sqoKeepOpenOnCommit and PacketRecords=-1
lazarus / 16-Databases

----------------------------------------------------------------------
Ign_17 · 2017-08-23 07:04

Hi everybody,

Just a little question, when in a TsqlQuery object we set
TsqlQuery.Options with the value sqoKeepOpenOnCommit, automatically
PacketRecords gets the value -1, doesn´t matter the XXXXConnector we
use.

In the same way, when using CommitRetaining, the datasets  used in a
Transaction keep open, but we can´t read more than the number of
records specified by PacketRecords (we´ll get an error when trying to
read the record number PacketRecords + 1), unless we assign the value
-1 to PacketRecords.

Does anybody know why this behavior? I don´t like it at all, if we use
tables with thousands of records the value -1 assigned to
PacketRecords implies all the records read into memory at once instead
of reading by blocks of records and commit them.

Thanks very much in advance

----------------------------------------------------------------------
Ign_17 · 2017-08-26 08:55

well, i think i have find what´s happening.

This dummy example to execute a commit for each 50 records:


var x:Integer;

sqlquery1.PacketRecords:=100;

sqlquery1.SQL.Text:='Select * from example1';
sqlquery1.Open;

if sqlquery1.IsEmpty = False then
begin
     x:=0;
     while not sqlquery1.EOF do
     begin
          sqlqlery1.Edit;
          sqlquery1.Fieldbyname('Field5').AsInteger:=0;
     sqlquery1.Post;
          sqlquery1.ApplyUpdates;

     if x=50 then
          begin
               SQLTransaction1.CommitRetaining;
               x:=0;
          end;

          x:=x+1;
          sqlquery1.Next;
     end;
end;

sqlquery1.Close;


works fine in Firebird, but not at all in Access.

The problem in Access is the instruction Trans.CommitRetaining. After
it, the Dataset remains active, but not at all... To explain it
anyway, the dataset keeps not "alive", it´s just a "photo". For this
reason when trying to read the record number (PacketRecord + 1)
generates a SQL sequence error. The only solution I know to resolve it
is assigning the value -1 to PacketRecords, this way all records are
buffered into memory.

I suppose, not sure at all, the problem is the lack of RETAIN
parameter in the COMMIT instruction of access SQL.

Regards,

----------------------------------------------------------------------
LacaK · 2017-09-06 10:40

Imo your experience is related to how ODBC works by default (I assume
you use TODBCConnection)
ODBC by default allows only one active statement per connection (so if
you have still open cursor while you fetch rows you cannot execute
another statement and/or open another cursor)

Read for example https://docs.microsoft.com/en-us/sql/relational-
databases/native-client/features/using-multiple-active-result-sets-
mars
Is related to MS SQL Server (calls it MARS) but explains a bit what is
going to happen.