ADO and Transactions

Forum related to ADO

Moderators: gto, cipto_kh, EgonHugeist

phoenix
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 03.09.2008, 10:08

ADO and Transactions

Postby phoenix » 03.09.2008, 10:14

Hello,

I have a problem with transactions and ADO.

I have two programs one that connects to a MSSQL 2005 server and the other that connected to an MSSQL 2000 server. I was using mssql (ntwdblib.dll) protocol to connect to the servers and that worked fine with the transactions but because it truncated strings to 255 chars, we opted to switch to ADO, which didn't have this problem.

Now when we run the same code that worked fine with mssql protocol I get the error:

An SQL Error has occurred

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1

BEGIN TRANSACTION

When the user starts editing data on our screen we add the above SQL.Text into a TZQuery and then execute the statement, which causes an error. What we want to do is the same as before, so we'd want multiple ExecSQL statements using the same connection:

TZQuery -> Begin Transaction SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Commit Transaction SQL Statement -> ExecSQL

All TZQuery objects use the same TZConnection.

But it seems that with ADO you can only do this within one TZQuery and one execute otherwise you get the above error. Has anyone else had this problem with transactions? I've tried using the StartTransaction / Commit / Rollback on the TZConnection connection itself, but this also didn't work.

From reading other posts I've tried changing the autocommit flag, but again this either produced the same error or new errors. I think its the scope of the transaction that needs to be changed, so the transaction can be over multiple ExecSQL statements, but I have no idea how to do this.

I was using Zeos components v6.5.1, but I upgraded to v6.6.3 - stable, to see if this helped, but unfortunately it didn't

Any help / ideas would be appreciated.

Thanks
Phoenix

phoenix
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 03.09.2008, 10:08

Postby phoenix » 04.09.2008, 14:57

Hi

I have now fixed the problem...

From looking at this post:

http://zeos.firmos.at/viewtopic.php?t=1988

The transaction needed to be on the connection object rather than the SQL statement in the TZQuery object. So by looking at the above post I realised that there was an ExecuteDirect function that can be called from the connection object. I added the following line of code in the correct places and it all worked without errors.

TZConnection.ExecuteDirect( 'BEGIN TRANSACTION' );
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZConnection.ExecuteDirect( 'COMMIT TRANSACTION' );

I just thought I'd post this in-case anyone else came up with the same issue in the future.

Phoenix


Return to “ADO”

Who is online

Users browsing this forum: No registered users and 1 guest