Page 1 of 1
UniDAC 6.3.13 and MSSQL: Error using Savepoints
Posted: Wed 27 Jul 2016 09:51
by GNiewerth
Hi,
MSSQL Server reports an unsupported operation when using SavePoints.
Code: Select all
try
{
TUniConnection* Database = new TUniConnection( this );
Database->ConnectString = "Provider Name=SQL Server;Data Source=Rev2K8;Initial Catalog=ocs-reporting;Port=0;User ID=sa;Password=***;Login Prompt=False";
Database->Connect();
Database->StartTransaction();
Database->Savepoint( "SavePoint" );
Database->ReleaseSavepoint( "SavePoint" ); // throws an Exception
Database->Commit();
}
catch( Exception& Excp )
{
MessageDlg( Excp.Message, mtError, TMsgDlgButtons() << mbOK, 0 );
// Message is: "Operation not supported by the database"
}
I´m trying to set up different savepoints within a transaction I can roll back to if something went wrong. Unfortunately even the simplest scenario does not work.
How can I achieve my goal using UniDAC in conjunction with MSSQL?
Re: UniDAC 6.3.13 and MSSQL: Error using Savepoints
Posted: Wed 27 Jul 2016 13:00
by ViktorV
This behavior of UniDAC is correct, SQL Server doesn't support explicit calling of release savepoint. In SQL Server you needn't to do any operation to release a savepoint. Savepoints are 'released' automatically at the final transaction commit or rollback, you needn't to manage them intermediately. Therefore, working with SQL Server, on executing the TUniConnection.ReleaseSavepoint method, you get the "Operation is not supported by the database" error.
You can call the TUniConnection.RollbackToSavepoint method to cancel all updates for the current transaction and restore its state up to the moment of the last defined savepoint.
Re: UniDAC 6.3.13 and MSSQL: Error using Savepoints
Posted: Wed 27 Jul 2016 14:44
by GNiewerth
Thank you for the answer, Victor.
Though UnIDAC behaves correct it´s still a nuisance for the developer. I expected the TUniConnection to handle the provider specific behaviour. It was really nice if TUniConnection allows the ReleaseSavePoint call and just do nothing for the MSSQL Provider.
I thought TUniConnection of an universal database connector (hence the name) and universal wrapper for different DBMS, so I can change providers on the fly. I am really disappointed by this UniDAC behaviour because it a) reports the error at runtime only and b) requires my code to distinguish bewteen different providers
.
Furthermore I see no point in reporting an error when there´s nothing to do.
Edit:
I ran into this error when migrating from Postgres to MSSQL. I wrote my own wrappers around Transactions and SavePoints, so they were started and committed/rolled back automatically. When I changed the DAC from PostgresDAC to UniDAC my code compiled, but gave me an error at run time.
Re: UniDAC 6.3.13 and MSSQL: Error using Savepoints
Posted: Thu 28 Jul 2016 10:30
by ViktorV
This correct behavior of UniDAC. UniDAC is a set of universal components designed for work with various DBMS's, and it doesn't support some specific features of particular DBMS's. When attempting to perform a specific feature of a particular DBMS, that is not supported in UniDAC, we generate a message informing the user about this.
To avoid generating the "Operation is not supported by the database" message, you shouldn't call the TUniConnection.ReleaseSavepoint method when using SQL Server provider. You can also buy UniDAC Professional Edition with source code and override this behavior.