TMSSQLConnection Fetch Loop while DB Process dead
lazarus / 16-Databases

----------------------------------------------------------------------
Tz · 2017-10-06 10:38

Hi All, I am new here

currently I m working on mssql database for logging using Linux
freetds, sqldb.
Some how I got error when querying large data set
when network error or connection is kill, the process become almost
100% and app doesnt quit   

so trace to this code I found that dbnextrow return 0 which equal FAIL
but in this code no handling for exit

function TMSSQLConnection.Fetch(cursor: TSQLCursor): boolean;
begin
  // Compute rows resulting from the COMPUTE clause are not processed
  repeat
    Fstatus := dbnextrow(FDBProc);
    Result  := Fstatus=REG_ROW;
  until Result or (Fstatus = NO_MORE_ROWS);   /// this make loop
forever

  if Fstatus = NO_MORE_ROWS then
    while dbresults(FDBProc) <> NO_MORE_RESULTS do // process
remaining results if there are any
      repeat until dbnextrow(FDBProc) = NO_MORE_ROWS;
end;

because I new here dunno how to make it fix or anybody could check it
too?

my current fixed is this

function TMSSQLConnection.Fetch(cursor: TSQLCursor): Boolean;
   begin
      // Compute rows resulting from the COMPUTE clause are not
processed
      repeat     
         Fstatus := dbnextrow(FDBProc);
         Result  := Fstatus = REG_ROW;
      until Result or (Fstatus = NO_MORE_ROWS)
            or (FStatus = FAIL);

      if Fstatus = NO_MORE_ROWS then
         while dbresults(FDBProc) <> NO_MORE_RESULTS do // process
remaining results if there are any
            repeat
               FStatus := dbnextrow(FDBProc);
            until (FStatus = NO_MORE_ROWS) or (FStatus = FAIL); 

      if FStatus = FAIL then
         CheckError( dbresults(FDBProc) );
   end;

probably not best fix

----------------------------------------------------------------------
LacaK · 2017-10-08 08:53

Yes, it seems, that FAIL is not handled in case when DB Process
becomes dead ... I will improve it after testing ... I will let you
know
I added a bug report: https://bugs.freepascal.org/view.php?id=32524

----------------------------------------------------------------------
Tz · 2017-10-09 08:49

almost forgot  also in TMSSQLConnection.Execute

         if not c.FSelectable then  //Sybase stored proc.
         begin
            repeat until dbnextrow(FDBProc) = NO_MORE_ROWS;
            res := CheckError( dbresults(FDBProc) );
            // stored procedure information (return status and output
parameters)
            // are available only after normal results are processed
            //if res = NO_MORE_RESULTS then GetParameters(cursor,
AParams);
         end;


Thanks in advance

----------------------------------------------------------------------
LacaK · 2017-10-27 12:02

I am trying to simulate problem.
Strange thing is that I can not reproduce described behavior.
I read rows from large table in loop and while reading I unplug
network cable to simulate network failure.
Loop stop reading, but no error is returned. Instead application
freezes in call to dbnextrow() in dblib.dll (libsybdb.so). So it
freezes not in loop in TMSSQLConnection.Fetch but somewhere in freetds
library ...
Same on Windows and Linux.

----------------------------------------------------------------------
LacaK · 2017-10-31 09:03

Fixed in TRUNK