DBX components & SQL Server transactions

sboydlns · Post by **sboydlns** » Fri 05 Aug 2011 13:56

I am having a problem with Delphi 2007 & DBExpress. I have installed the DevArt SQL Server driver and am using that to make my database connection.

It appears as though explicit transactions are not working. In my program I make a call to TSQLConnection.BeginTransaction. If I place a breakpoint at this line in the program and use SQL Server Manager to check to active transactions for the database I find zero. I then step through the BeginTransaction call. I would expect SQL Server Manager to now show an active transaction for the database. But it does not. It still show zero active transactions. In fact, no transactions appear on the database until I execute the first DML statement via TSQLQuery. At this time an implicit transaction appears. Not an explicit transaction as I would expect.

Am I doing something wrong? Are there problems with the DBX components in Delphi 2007? Or do I just not understand the way SQL Server is supposed to work?

I really need explicit transactions if my application is to work the way is should.

AndreyZ · Post by **AndreyZ** » Fri 05 Aug 2011 14:44

Hello,

The point is that in SQL Server a real transaction begins only when the first SQL statement for data modification is executed.

sboydlns · Post by **sboydlns** » Fri 05 Aug 2011 15:06

AndreyZ wrote:Hello,

The point is that in SQL Server a real transaction begins only when the first SQL statement for data modification is executed.

Ok. Then why is the transaction that gets started an implicit rather than an explicit transaction. This seems significant because the program in question is doing a mass load of records to a table. Occasionally the INSERT statement will throw an exception due to bogus data in the original file. At this point the implicit transaction appears to be getting rolled back and closed. That is not what I want to happen. I want to trap the error, log it and continue.

sboydlns · Post by **sboydlns** » Fri 05 Aug 2011 18:35

I finally found out what the problem was. I was getting data conversion errors due to some bogus data in the source file. I knew about this and the program made allowances for it by trapping the exception and carrying on. Anyway, data conversion errors fall into a class of SQL Server errors known as "batch aborting" errors. Whenever a batch aborting error occurs, the current transaction is rolled back, without so much as a "by your leave". I'd like to meet the guy who thought that this was a good idea.

And people wonder why I hate Microsoft!