Stored procedure runs correctly only sometimes

Question

My problem is: I have a stored procedure that only sometimes runs correctly, sometimes it does not run at all and sometimes partially (some rows where inserted in the table bot not all). I use lazarus 2.0.6 on Win10 64bit and a TODBCConnection to connect to SQL Server 2012.

The stored procedure itself is correct, in SQL Server Management Studio, it runs without problems. The stored procedure writes records into 4 different tables; depending on the parameters approximately 1-400 records per table.

The code:

con:TODBCConnection;
tra:TSQLTransaction;
qry:TSQLQuery;

con:=TODBCConnection.Create(nil);
tra:=TSQLTransaction.Create(nil);
qry:=TSQLQuery.Create(nil);

tra.DataBase:=con;
qry.DataBase:=con;
qry.Transaction:=tra;

con.Params.Clear;
con.Params.Add('DRIVER=SQL Server');
con.Params.Add('SERVER=xxxxx');
con.Params.Add('DATABASE='+DATENBANK_NAME);
con.Params.Add('Trusted_Connection=Yes');
con.Params.Add('AUTOCOMMIT=1');
con.Open; 

// call the procedure
sql:='EXEC dm.createStorage2 @St_Geraet='Q', @St_Bezeichnung='Q-Freezer -80', @St_Temperatur=-80, @St_Raum='Keller', @St_Gebaeude='ABC', 
@St_AnzFaecher=2, @St_AnzTuermeProFach=5, @St_MoeglicheSchubladenkombinationenProTurm='4,3', @St_MoeglicheBoxenkombinationenProSchublade='3,4', 
@St_MoeglicheBoxKapazitaeten='16,25,100', @schubladenProTurm=4, @boxenProSchublade=3, @boxkapazitaet=16;';

qry.SQL.Text:=sql;
qry.ExecSQL;

// alternatively, this will also not work:
// con.ExecuteDirect(sql);

con.Close();
qry.Free;
tra.Free;
con.Free;

If I set a breakpoint at qry.ExecSQL; and step over it - it works ?!

It seems that it doesn't wait for qry.ExecSQL; to finish and frees the resources directly afterwards. What can I do to tell the code to wait until qry.ExecSQL; is finished?

Edit: the problem seems to be that qry.ExecSQL; runs asynchronous instead of synchronous as it should be. How can I tell this call to run synchronous?

Thanks in advance.

Answer 1

have it figured out: it´s a bug in sqldb.pas, in combination with odbc he can´t detect when a stored procedure is finished. so the programm immediatly steps to the next command, which in my case was the freeing of the database-instance -with the result that the sql-command was aborted. using now the "zeos" library (https://zeoslib.sourceforge.io) and it works like a charm.

uses
Classes, SysUtils, ZConnection, ZDataset;

...

con:=TZConnection.Create(nil);
qry:=TZQuery.Create(nil);

qry.Connection:=con;

con.Protocol:='ado';
con.Database:='DRIVER=SQL Server;Trusted_Connection=Yes;SERVER=xxxx;DATABASE='+DATENBANK_NAME;
con.Connected:=true;

the rest is identical to the old code, with one excaption: con.close(); becomes con.connected:=false;