Delphi 10.4, FireDac, StartTransaction causes ODBC error.

[Kyle Miller 1]

FireDAC randomly raises the following error when I call StartTransaction.

 

 

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 September 3, 2021 by Kyle Miller

[Michael Longneck 2]

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 September 3, 2021 by Michael Longneck

[Kyle Miller 1]

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.

[Hans J. Ellingsgaard 21]

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;

[Kyle Miller 1]

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! 👍

[Kyle Miller 1]

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.