[fdo-internals] About transaction in FDO SQL server spatial provider

[Sam Wang]

Hi experts,

I see that FDO SQL server spatial provider uses an ODBC driver and I found the following about transaction in ODBC on MSDN website.

"When an application turns autocommit off, the next statement sent to the database starts a transaction. The transaction then remains in effect until the application calls SQLEndTran with either the SQL_COMMIT or SQL_ROLLBACK options. The command sent to the database after SQLEndTran starts the next transaction."

That means the transaction is not started explicitly and it turns out that in manual commit mode(which is switched to when connection opens), any command can start a transaction.

So considering the following scenario for SQL server spatial provider:

FdoPtr<FdoIInsert> insertCommand1 = (FdoIInsert *) connection->CreateCommand(FdoCommandType_Insert);
insertCommand1->SetFeatureClassName(...)
...
insertCommand1->Execute();

FdoPtr<FdoITransaction> transaction = connection->BeginTransaction();
FdoPtr<FdoIInsert> insertCommand2 = (FdoIInsert *) connection->CreateCommand(FdoCommandType_Insert);
insertCommand2->SetFeatureClassName(...)
...
insertCommand2->Execute();
transaction->Rollback()

Actually if we look into the code, the BeginTransaction method simply calls rdbi_tran_begin which does nothing(not start an explicit transaction like using statement "begin transaction") but only creates an transaction entry for the connection.
Therefore, the first insert command creates a transaction implicitly and not until the transaction->Rollback() is executed that the transaction ends.

The problem is the first and the second insert commands are all rolled back which is not intended.
Is there somewhere I am missing or understanding incorrectly?

Sorry for the stupid question.

Thanks,
Sam
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20110616/bf5d6786/attachment.html