Sequence error when reading from SqlQuery
lazarus / 16-Databases

----------------------------------------------------------------------
Ign_17 · 2015-12-18 10:24

Hi everybody. Please, i´ll thank some information / help about one
question...

Imagine we have a table, "table1" containing the name and age of all
students in our classroom, and we want to copy information to another
table, "table2", just the records of all of them with age > 25

So, i implement the next algoritm:


SqlTransaction1.StartTransaction;

Sqlquery1.SQL.Text:='SELECT * FROM table1';
Sqlquery1.Open;

While not SqlQuery1.EOF then
begin
          x:=SqlQuery1.fieldbyname('name').AsInteger;
          y:=SqlQuery1.fieldbyname('age').AsInteger;

         if y >25 then
         begin
             Sqlquery2.SQL.Text:='INSERT INTO table2 (name, age)
VALUES (x,y)';
             SqlQuery2.ExecSQL;
             Sqltransaction1.CommitRetaining;
             SqlQuery2.Close;
        end;

       SqlQuery1.Next;
end;

SqlQuery1.Close;
Sqltransaction1.EndTransaction;



Ok. I have find some behavior i do not understand at all. At first, it
runs fine, but the first time it is found a record with age>25 and the
SqlTransaction.Commit executes, then the application can only read a
little amount of records from Tsqlquery1 and inmediately launch an
exception:

"Could not fetch new row from result set. SqlState: HY010 [ODBC Driver
Manager] Function sequence error"

If i modify the property Sqlquery1.PacketRecords and put a bigger
number than the default, it reads more records but finally get the
same exception.

Of course, if i put SqlQuery1.Packetrecords=-1 all SqlQuery1 registers
are buffered before executing Sqltransaction.Commit and application
runs fine, but i don´t like very much to buffer all records at once.

It seems when executing sqltransaction.Commit the state of SqlQuery1
is "lost". Does anybody knows why, and if there is another way to do
it instead of buffering all records?


Thanks very much in advance.

----------------------------------------------------------------------
balazsszekely · 2015-12-18 10:44

I suppose name is a string and age integer, so I modified the code
accordingly:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text:='SELECT * FROM table1 where age > :page'; 
SQLQuery1.ParamByName('page').AsInteger := 25;  SQLQuery1.Open;  if
SQLQuery1.RecordCount > 0 then  begin    SQLQuery2.SQL.Text:='INSERT
INTO table2 (name, age) VALUES (:pname, :page)';    while not
SQLQuery1.EOF do    begin      SQLQuery2.Params.BeginUpdate;     
SQLQuery2.ParamByName('pname').AsString  :=
SQLQuery1.FieldByName('name').AsString;     
SQLQuery2.ParamByName('page').AsInteger :=
SQLQuery1.FieldByName('age').AsInteger;;     
SQLQuery2.Params.EndUpdate;      SQLQuery2.ExecSQL;      
SQLQuery1.Next;    end;    SQLTransaction1.CommitRetaining;   
SQLTransaction1.EndTransaction;  end;
PS: Always use parameterized queries when possible and Tables should
always have a unique ID.

----------------------------------------------------------------------
Ign_17 · 2015-12-18 17:10

Thank you very much for your answer.

I´m afraid I put a bad example, with mistakes on it

Looking to your code, I see you first executes all transfer operations
(.ExecSQL, .Post, etc) and finally executes an only
SqlTransaction.Commit. But, is there any limit? I mean, what if i need
to work over thousands of records? An only final .Commit is enough, or
do i .commit periodically?

Thanks again.

----------------------------------------------------------------------
Abelisto · 2015-12-18 17:35

I am afraid to be obvious but why do not to give all business logic to
SQL? Like:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
table2 (name, age) SELECT x, y FROM table1 WHERE y > 25
?

And
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 SqlQuery2.Close;at least redundant.

----------------------------------------------------------------------
LacaK · 2015-12-18 17:41

Quote from: Ign_71 on December 18, 2015, 11:24:54 am
"Could not fetch new row from result set. SqlState: HY010 [ODBC Driver
Manager] Function sequence error"

If i modify the property Sqlquery1.PacketRecords and put a bigger
number than the default, it reads more records but finally get the
same exception.

Of course, if i put SqlQuery1.Packetrecords=-1 all SqlQuery1 registers
are buffered before executing Sqltransaction.Commit and application
runs fine, but i don´t like very much to buffer all records at once.

It seems when executing sqltransaction.Commit the state of SqlQuery1
is "lost". Does anybody knows why, and if there is another way to do
it instead of buffering all records?

Depends which database do you use?
Some does not support multiple active resultsets at same time.
So you can not execute other SQL statement, until you fetch all rows
from opened recordset.

----------------------------------------------------------------------
Ign_17 · 2015-12-20 16:25

Quote from: LacaK on December 18, 2015, 06:41:36 pm
Depends which database do you use?
Some does not support multiple active resultsets at same time.
So you can not execute other SQL statement, until you fetch all rows
from opened recordset.

In my example i am working with ODBC and Access.


-- Sorry, I modify this. On the rest of this comment  I´m wrong,
Getmem´s code works fine --


And about the code posted by Getmem (again, thanks you so much for
your comment), the problem in my case is the same: when the number of
records provided by SqlQuery1 is greater than value of
SqlQuery1.PacketRecords, sqlquery1.Next get the exception i was
talking about when trying to read the next record after the last one
in the buffer:
"SqlState: HY010 [ODBC Driver Manager] Function sequence error"

I suppose this could be what LacaK comments, the lack of MARS in
Access that avoids working with SqlQuery1 and SqlQuery2 at the same
time.  Anycase, with SqlQuery1.PacketRecords=-1 the question is
resolved, but is there any better way to do it without buffering all
sqlquery1 records?

Thaks again