How to use SQL++ in a transaction for multiple upserts

[gokceng_intertech]

Hi,
We’d like to upsert multiple records with TTL in a single transaction using .Net SDK.

We’ve seen Couchbase Transactions don’t support TTL definition so we have given a try to SQL++. The snippet below shows what we have tried:

var cluster = await clusterProvider.GetClusterAsync();
var query = @$"UPSERT INTO mybucket.myscope.mycollection (KEY, VALUE, OPTIONS) VALUES ('Key1', 'Value1', {{'expiration':1697556337}}), ('Key2', 'Value, {{'expiration':1697556337}});";
await cluster.QueryAsync<dynamic>(query);

We’d like to confirm if this the right way of doing this, and whether this statement would be executed in a single transaction or not.

We’ve also tried running this query in a transaction but we got an error:

var cluster = await clusterProvider.GetClusterAsync();
var transaction = Transactions.Create(cluster, TransactionConfigBuilder.Create().Build());
await transaction.RunAsync(async (ctx) => {
    var query = @$"UPSERT INTO mybucket.myscope.mycollection (KEY, VALUE, OPTIONS) VALUES ('Key1', 'Value1', {{'expiration':1697556337}}), ('Key2', 'Value, {{'expiration':1697556337}});";
    var result = await ctx.QueryAsync<dynamic>(query);
});

ERROR:
CouchbaseException: Error processing duration - cause: Unable to parse duration: 14959,0927ms [1040]

Kind regards,
Gokcen

[graham.pople]

Hi @gokceng_intertech
Apologies, but TTL is not supported inside transactions at present, and neither such approach will work.

[gokceng_intertech]

What would you suggest to achieve upserting 2 documents with TTL in a single transaction?

[graham.pople]

Hi @gokceng_intertech sorry but as TTL is not supported inside transactions at present, it just isn’t possible.

The only real workaround at present is to apply the TTL after the transaction.