mORMot Open Source

CycleSoft

Member

Registered: 2013-01-18

Posts: 34

Transactions on an external DB

In my interface-based server I define an external DB connection to a Microsoft SQL server 2008 with:

  ExternalDBProperties := TOleDBMSSQLConnectionProperties.Create(ServerName, DataBaseName, UserID, UserPassword);

then in my services I can successfully execute SQL statemts against the external DB:

  Props := TSQLDBConnectionProperties( TSQLRestServerDB( SC.Factory.RestServer).Model.Props[TSQLAuthUser].ExternalDB.ConnectionProperties);
  Props.ExecuteNoResult(SQLStatement, []);

But how can I handle transactions? I use

  Props.MainConnection.StartTransaction / Commit / RollBack

but Props.MainConnection.TransactionCount is always zero after a  StartTransaction so subsequents Commits/Rollbacks fails.

How transactions on external DB should be handled ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Transactions on an external DB

The easiest, safest, and fastest is to use a TSQLRestBatch process, with automatic transaction.

CycleSoft

Member

Registered: 2013-01-18

Posts: 34

Re: Transactions on an external DB

AB, I'm trying to apply your (always valuable) suggestion.

It seems to me that TSQLRestBatch is a perfect choice in a ORM based implementation.

Probably I have not put under your attention that I'm using 'raw' SQL statements, because I have to deal with a given DB and structure, so no ORM here.

This news shreds some light and maybe a new hint?

It seems to me that everything related to transactions handling is already in place, needs only something to start working

Last edited by CycleSoft (2015-06-10 15:35:38)

CycleSoft

Member

Registered: 2013-01-18

Posts: 34

Re: Transactions on an external DB

As I hoped, it was easy, once figured out what was going on

Just added :

if not Props.MainConnection.Connected then
   Props.MainConnection.Connect;

right before the StartTransaction:

Props.MainConnection.StartTransaction;

The connection pool was creating a new connection without opening it, and the check for support for transactions is done after the connection is opened, so pening the connection before using it with a SQL statement do the trick.

Thanks for your time AB!

Last edited by CycleSoft (2015-06-10 16:27:29)