#228 Error Code: -502

[Hafedh TRIMECHE]

The new revision 4214 generated an exception:
SQL error: Cursor is not open. Error Code: -502. The cursor identified in an open statement is already open.

The previous revisions (4191) don't generate such exception.

[EgonHugeist]

Hi, what does it mean?

Which driver are you using? Which DB+Version? Which Compiler?
What are you doing? Example???

No available/known test fails. So we need mor informations! So we can't work....

cheers

[marsupilami79]

Hello Michael,

-502 is an SQL CODE from Firebird. The name of the screen shot suggests it is Delphi XE 10.2. The Tokyo release, I think. ;) According to the error message, I think Zeos tries to reopen an already existing cursor.

@Hafedh: Could you supply an example of how to raise the error? It is more easy for us to find the problem then.

Best regards,

Jan

[marsupilami79]

Sorry, my previous statement about it being Firebird was wrong. Hafedh, which database do you use? Which driver in Zeos od you use?

[Hafedh TRIMECHE]

Sorry for missing details about Database & Compiler.
Database : FireBird 4.0 with

  LibLocation            := 'fbclient.dll';
  Protocol               := 'firebirdd-3.0';
  UseMetadata            := False;
  User                   := 'SYSDBA';
  Password               := 'masterkey';
  TransactIsolationLevel := tiReadCommitted;
  AutoCommit             := True;

Compiler: Delphi 10.2
Exception raised when attemping to read records's number using Query.RecordCount in FetchTables procedure.

No problem with older SVN versions (<= 4191)

procedure TSqlDB.FetchTables;
const
  TABLES_SQL=
  'SELECT RDB$RELATION_NAME' + CRLF +
    'FROM RDB$RELATIONS' + CRLF +
   'WHERE RDB$SYSTEM_FLAG=0;';
var
  Query : TSqlStatement;
  Idx   : Integer;
begin
  Query          := TSqlStatement.Create(Self,nil);
  Query.SQL.Text := TABLES_SQL;
  Query.OpenQuery;
  SetLength(FTables,Query.RecordCount);
  Idx := 0;
  while not Query.Eof do
  begin
    FTables[Idx] := Trim(Query.Fields[0].AsString);
    Query.Next;
    Inc(Idx);
  end;
  FreeAndNil(Query);
end;

procedure TSqlDB.Afterconstruction;
var
  RecomputeIndexes : Boolean;
  sqlStatement     : string;
  Query            : TSqlStatement;
begin
  inherited;
  Query := TSqlStatement.Create(Self,nil);

  ExecuteScript(CHARACTER_SET_SCRIPT);

  FetchTables;

  Query.SQL.Text   := 'SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE (RDB$INDEX_INACTIVE=1)';
  RecomputeIndexes := Query.OpenQuery;
  if not TableExists(RecordsCountTable) then
  begin
    RecomputeIndexes := True;
    ExecuteScript(RecordsCountDDL);
  end;
  Query.SQL.Text   := 'SELECT * FROM '+RecordsCountTable+' WHERE (WELLCLOSED = 0) ROWS 1';
  RecomputeIndexes := RecomputeIndexes or Query.OpenQuery;
  if RecomputeIndexes {and (not IsDevMachine)} then
  begin
    Query.SQL.Text := 'SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE (RDB$SYSTEM_FLAG=0)';
    if Query.OpenQuery then
    begin
      sqlStatement := '';
      while not Query.Eof do
      begin
        sqlStatement := sqlStatement + 'ALTER INDEX '+Trim(Query.Fields[0].AsString)+' ACTIVE;'+CRLF;
        Query.Next;
      end;
      ExecuteScript(sqlStatement);//s
    end;
    ExecuteScript('UPDATE '+RecordsCountTable+' SET RECORDSCOUNT=-1;');
  end;
  FreeAndNil(Query);
end;

[EgonHugeist]

Hafedh TRIMECHE,

looks better now. Finally which branch of SVN are you using for? trunk or testing-7.2 or testing-7.3
the revision doesn't tell me this...

Note i can't reproduce the issue on 7.2
Quick test with a XE, FB2.5:

procedure ZTestCompInterbaseBugReport.Test_Ticket228;
var
  Query: TZQuery;
  I: Integer;
begin
  if SkipForReason(srClosedBug) then Exit;

  Query := CreateQuery;
  try
    Query.SQL.Text := 'SELECT RDB$RELATION_NAME' +
      ' FROM RDB$RELATIONS' +
      ' WHERE RDB$SYSTEM_FLAG=0;';
    Query.Open;
    I := Query.RecordCount;
    while not Query.Eof do begin
      Check(Query.Fields[0].AsString <> '');
      Query.Next;
    end;
    Query.Close;
    Check(True, 'TestPassed');
  finally
    Query.Free;
  end;
end;

[Hafedh TRIMECHE]

Hi,
The SVN version used: trunk 7.2

The exception generated because of retrieving RecordCount in the transaction frame (before commit).

Placing "Query.Connection.Commit;" after "rc := Query.RecordCount;" instruction solved the problem.

Please note that this code worked fine before updating to new SVN version.

  Query.SQL.Text := 'SELECT * FROM ...) ROWS 1';
  Query.Params.ParamByName('P0').AsString := '..';
  Query.Params.ParamByName('P1').AsString := '...';
  Query.Connection.StartTransaction;
  Query.Open;
  Query.Connection.Commit;
  rc := Query.RecordCount;
  Query.First;
  while not Query.Eof do
  begin
    s := Trim(Query.Fields[0].AsString);
    if s='..' then doSomething;
    Query.Next;
  end;

[marsupilami79]

Hello Hafedh,

I do understand your confusion about this changed behaviour. And I agree that such behaviour changes should not happen in a Zeos tree that is already declared beta for some time now.

Here is what happens under the hood of Zeos: If you open the TZQuery, your SQL gets sent to the database. The database returns a handle that allows Zeos to fetch rows of the result as it needs them. In most cases the rows can be fetched in a forward only, read only manner which means we cannot scroll through them freely and we cannot modify them. Zo whenever we fetch a row, it gets added to a cache which in turn delivers the data to the query. But that cache gets not filled immediately, instead it gets filled whenever you ask for the data of a row. The idea is to be careful about ressouorces.
So after TZQuery.Open we have a handle to the result set and maybe already fetched some rows, but not all of them. If you call TZQuery.Connection.Commit the current transaction is committed. Unfortunately this also invalidates the handle to the result set.
Now, when you query the record count by using TZQuery.RecordCount, the query tries to fetch all remaining rows from the resultset and the result set finds the handle to be invalid, which in turn leads to the exception that you have seen.
This is why moving the usage of TZQuery.RecordCount in front of TZQuery.Connection.Commit solves your problems: The cache is filled then and all records have been read from the result set.

As far as I can see, this is the way Zeos is designed. If you want to be sure, that all records have been read from the result set, you have to call TZQuery.FetchAll. Alternatively you can move your call to TZQuery.Connection.Commit after you closed your connection.

Michael, yesterday you asked me about my proposal about this: Honestly I think, we can keep things the way they are. Zeos never promised for this to work. Otherwise there would be no FetchAll procedure. We could make FetchAll the default behaviour of Open and provide a parameter for disabling this behaviour if we want to be on a safewr side though.

[Fr0sT.Brutal]

Agree with Jan. Committing a transaction means finish all actions done inside it (unless a commit is retaining). It's like you've started Word (connection), opened a document (start transaction), read something (select) and then closed the document (commit transaction). Obviously you can not read anything from a closed document.
Probably some DB components allow datasets remain open after commit but it's completely their own high-level handling.
I think this is not a bug but fundamental database feature. For example, IBX on code sample given above allows reading RecordCount but fails on IBQuery1.First telling that dataset is not open.

We could make FetchAll the default behaviour of Open and provide a parameter for disabling this behaviour if we want to be on a safewr side though.

I'd vote against this. Performance impact would be too big.

[EgonHugeist]

Just a note, no personal statement: Current error code is SQL Error:

invalid transaction handle (expecting explicit transaction start). Error Code: -901. Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements

[EgonHugeist]

@Hafedh TRIMECHE

resolved in 4340 /testing-7.2.
Solution is just for 7.2 by now. Since the behavior change on a release candidate was to big for me. I execute the stmt again on the Server. The rows can be fetched for this RecordSet only. Do not touch other open cursors -> no reload happens.
But keep in mind .. such code is less helpful. It doesn't make sence to call a commit after opening recordsets.

please test and close if ok.

@Jan
since 7.2 and drivers which support multiple active recordset, we are fetching the rows just on demand. A "FetchAll" on native Dbc should happen only for tabular protocols. So i vote against this too.

[marsupilami79]

Fixed