Recent

Author Topic: sqoKeepOpenOnCommit and PacketRecords=-1  (Read 2727 times)

Ign_17

  • New Member
  • *
  • Posts: 43
sqoKeepOpenOnCommit and PacketRecords=-1
« on: August 23, 2017, 09:04:00 am »
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

« Last Edit: August 23, 2017, 09:50:38 am by Ign_71 »

Ign_17

  • New Member
  • *
  • Posts: 43
Re: sqoKeepOpenOnCommit and PacketRecords=-1
« Reply #1 on: August 26, 2017, 10:55:35 am »
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

  • Hero Member
  • *****
  • Posts: 691
Re: sqoKeepOpenOnCommit and PacketRecords=-1
« Reply #2 on: September 06, 2017, 12:40:44 pm »
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.

 

TinyPortal © 2005-2018