Sharing Snapshot Transactions

[Rand Random]

I stumbled accross those open issues:

https://github.com/FirebirdSQL/NETProvider/issues/871
https://github.com/FirebirdSQL/NETProvider/issues/835

and would like to know if it would be possible for me to manually set the snapshot number with

SET TRANSACTION SNAPSHOT AT NUMBER 12345;

My attempts all fail with the same error message

invalid transaction handle (expecting explicit transaction start)

Not sure if I am doing something wrong, or if it is just impossible without proper support.

My basic attempt looks like this:

        var conString = GetConnectionString(@"C:\foo.fdb").ToString();
        using FbConnection dbConnection = new(conString);
        dbConnection.Open();

        try
        {
            using FbCommand fbCommand = dbConnection.CreateCommand();
            fbCommand.CommandText = "SET TRANSACTION SNAPSHOT AT NUMBER 12345;";
            _ = fbCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Test 1 failed");
            Console.WriteLine(ex.Message);
        }

Also tried to manually open the transactions like this:

        var conString = GetConnectionString(@"C:\foo.fdb").ToString();
        using FbConnection dbConnection = new(conString);
        dbConnection.Open();

        try
        {
            using FbTransaction fbTransaction = dbConnection.BeginTransaction();

            using FbCommand fbCommand = dbConnection.CreateCommand();
            fbCommand.Transaction = fbTransaction;
            fbCommand.CommandText = "SET TRANSACTION SNAPSHOT AT NUMBER 12345;";
            _ = fbCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Test 2 failed");
            Console.WriteLine(ex.Message);
        }

but same exception.

[Jiří Činčura]

> https://github.com/FirebirdSQL/NETProvider/issues/871
> https://github.com/FirebirdSQL/NETProvider/issues/835

Looks like one is duplicate. I closed it.

> and would like to know if it would be possible for me to manually set
> the snapshot number with

AFAIR all the `SET TRANSACTION` statements are understood only by `isql`. Sending these over the wire does not work.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Mark Rotteveel]

On 09-09-2022 11:33, Jiří Činčura wrote:
>> https://github.com/FirebirdSQL/NETProvider/issues/871
>> https://github.com/FirebirdSQL/NETProvider/issues/835
>
> Looks like one is duplicate. I closed it.
>
>> and would like to know if it would be possible for me to manually set
>> the snapshot number with
>
> AFAIR all the `SET TRANSACTION` statements are understood only by `isql`. Sending these over the wire does not work.

That is not correct, it is a valid DSQL statement. The issue is that
statement `SET TRANSACTION` must be executed with an invalid transaction
handle (IIRC, -1), and the new transaction handle is returned in the
object handle field of the generic response.

If a `SET TRANSACTION` is executed with a normal transaction handle, it
produces error "invalid transaction handle (expecting explicit
transaction start)". So, given statements in the provider are always
executed with a transaction, this error occurs.

Mark
--
Mark Rotteveel

[Rand Random]

So, am I assuming correct that the "invalid transaction handle (-1)" would be impossible for me to configure at the current state of the .net provider?

Or is there a way?

[Mark Rotteveel]

On 09-09-2022 13:39, Rand Random wrote:
> So, am I assuming correct that the "invalid transaction handle (-1)"
> would be impossible for me to configure at the current state of the .net
> provider?
> Or is there a way?

No, as far as I'm aware, it will always start a transaction when none is
active when you execute a statement. And even if it were possible, this
would also require special handling to obtain and use the new
transaction handle created by this statement.

Mark
--
Mark Rotteveel

[Jiří Činčura]

> No, as far as I'm aware, it will always start a transaction when none is
> active when you execute a statement. And even if it were possible, this

Yes.