Jump to content
Kyle Miller

Delphi 10.4, FireDac, StartTransaction causes ODBC error.

Recommended Posts

FireDAC randomly raises the following error when I call StartTransaction.

 

image.png.43c67d0cca4588fb20f181349592cd86.png

 

What causes this? A workaround?

================================
FireDAC info
================================
Tool = RAD Studio 10.4
FireDAC = 27.0.0 (Build 99682)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
      Found [SQL Server Native Client 11.0]
Driver Manager version = 03.81.14393.0000
================================
Session info
================================
Current catalog = 
Current schema = dbo
Driver name = sqlncli11.dll
Driver version = 11.00.7462
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 12.00.6164

 

Edited by Kyle Miller

Share this post


Link to post

That's happened because somewhere called TFDQuery.ExecSQL or TFDStoredProc.ExecProc but SQL stored proc return result set, which obviously not fetched but not discard. And now any attempt to StartTransaction (internally it's just turn off autocommit) will be failed with this "clear and detailed" error message.

 

There's two options - call Query.Disconnect(True) or call Open instead ExecSQL\ExecProc.

Edited by Michael Longneck
  • Thanks 1

Share this post


Link to post
18 hours ago, Michael Longneck said:

That's happened because somewhere called TFDQuery.ExecSQL or TFDStoredProc.ExecProc but SQL stored proc return result set, which obviously not fetched but not discard. And now any attempt to StartTransaction (internally it's just turn off autocommit) will be failed with this "clear and detailed" error message.

 

There's two options - call Query.Disconnect(True) or call Open instead ExecSQL\ExecProc.

That's a good thing to check, but it isn't the case here. The only code executed before the StartTransaction is the non-database releated.

 

I did find the situation causing the error, but I don't know why. I have one form open to a record. I open another form, a different form, and open it to the same record in same table. When StartTransaction fires, there's an error. When I close Form1, StartTransaction fires no error.

 

My first suspension was an event alerter on Form1 causing an issue, but Form2 has not performed any CRUD yet when the error is raised. Also, execution never hits the event alerter's event when Form2 performs its action.

 

As a workaround, I'll probably shove all the Delphi logic into a stored procedure & avoid StartTransaction.

Share this post


Link to post

Your problem might have something to do with nested transactions. If your db or your odbc driver don't support nested transactons, make shure there is no ongoing transaction on the same connectionm, before you call StartTransaction.

 

Something like this:

    if FDConnection1.InTransaction then
       FDConnection1.Commit;
 

    FDConnection1.StartTransaction;

Share this post


Link to post
On 9/3/2021 at 12:14 AM, Michael Longneck said:

That's happened because somewhere called TFDQuery.ExecSQL or TFDStoredProc.ExecProc but SQL stored proc return result set, which obviously not fetched but not discard. And now any attempt to StartTransaction (internally it's just turn off autocommit) will be failed with this "clear and detailed" error message.

 

There's two options - call Query.Disconnect(True) or call Open instead ExecSQL\ExecProc.

Michael, You are exactly right. I went back through all the stored procedures and found one which returns a result set. It's not used, but it is present. Thank you for the tip! 👍

Share this post


Link to post
6 hours ago, Hans J. Ellingsgaard said:

Your problem might have something to do with nested transactions. If your db or your odbc driver don't support nested transactons, make shure there is no ongoing transaction on the same connectionm, before you call StartTransaction.

 

Something like this:

    if FDConnection1.InTransaction then
       FDConnection1.Commit;
 

    FDConnection1.StartTransaction;

I did suspect that, i.e. a transaction left open on Form1 causing a problem on Form2. Turned out to not be the case. Like Michael said, there was a stored procedure returning a result set but was executed with ExecProc.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×