Random exception SQL error code = -502 - Invalid cursor declaration using fb embedded from a service - bad cursor GUID [CORE4559]

[firebird-automations]

Submitted by: Francesco Pucino (fpucino)

Votes: 1

Using the enbedded engine inside a Windows service written in Delphi XE5 and using IBTable, IBQuery, IBDataBase and IBTransaction components, some time, randomly its raised this error:

Dynamic SQL Error SQL error code = -502 Invalid cursor declaration Statement already has a cursor XXXXXXX assigned.

The same identical code running from a standard Windows application and not from a service, works correctly (at least until now), so I suppose that the problem comes using the dbms from a service.

What I suppose is also a bug is that the cursor name XXXXXXX reported in the exception is followed by some random strange characters. Here some examples:

Statement already has a cursor {8E1EFE75-E593-4272-955F-722ACF638970}ŸŸŸŸŸŸ EB° assigned" Statement already has a cursor {907E1505-AE34-4722-800A-6984B4EDD550}E="XPOG¬° assigned" Statement already has a cursor {71857BF2-C993-4759-8B85-A91209F54C56}IJIIIH@›° assigned" Statement already has a cursor {5B310F1A-4455-4170-9181-57D489FD61A4}»ÐÛ¬€¨?° assigned"

I think that it should be a standard GUID without other appened chars.

[firebird-automations]

Commented by: Tim Kelly (m00bh000)

Not sure if it is the same bug but I am now randomly getting:

Dynamic SQL Error SQL error code = -502 Invalid cursor declaration Statement already has a cursor {B221BB30-9EEB-4EA0-88CE-4E01EA648F1F} assigned

When doing repeating a pre-preprepared select via delphi IBXpress selects that was prepared in a previous transaction.

Running: Server

2015-10-21 04:38:54 Firebird-3.0.0.32108-0_x64.zip 13 MB

Client 2015-10-21 04:38:49 Firebird-3.0.0.32108-0_Win32.zip 11 MB

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Please describe the sequence of database/connection/transactions operations which you are performing in your application.

[firebird-automations]

Commented by: Marco de Groot (marco)

This is a bug in the IBX library, not Firebird. The created GUID in the IBSQL.Cursor is not 0-terminated. At first everything works fine, because the remainder of the memory is filled with #⁠0, but after a while you get garbage and a very long random string might be used as a cursorname and this makes Firebird choke.

I have some workarounds in my code to fix this for the TIBSQL, TIBQuery and the TIBSchema. Basically it's

TYPE TMyIBSQL = CLASS(TIBSQL) END;

PROCEDURE PatchIBSQLCursor(aIBSQL: TIBSQL); VAR lNewCursorLen: Integer;

BEGIN // The XE5 implementation of the cursor is wrong. It uses a PByte from a TBytes with a GUID, but // the TBytes isn't 0-terminated. Once in a while there is some magic garbage after the GUID which // makes FB return: // SQL error code = -502 // Invalid cursor declaration // Statement already has a cursor {9885A855-9659-484F-A7B6-442D778BAF52}¨¨Ð44+É.Œ assigned // So, patch the cursor name to add a #⁠0 at the end. lNewCursorLen := Length(TMyIBSQL(aIBSQL).FCursor) + 1; // Not yet initialized or already set IF (lNewCursorLen = 1) OR (TMyIBSQL(aIBSQL).FCursor[lNewCursorLen-2] = 0) THEN Exit;

SetLength(TMyIBSQL(aIBSQL).FCursor, lNewCursorLen); TMyIBSQL(aIBSQL).FCursor[lNewCursorLen-1] := 0; END;

And this is called after creating the TIBSQL, for the QDelete, QInsert, QRefresh, QSelect and QModify of every TIBQuery.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Based on Marco's comments, marking this case as "won't fix" as the problem is with the IBX components, which is not a Firebird project resource.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \]

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]

[firebird-automations]

Commented by: Sheau Hong Ghan (hongghan)

Dear Marco de Groot,

We have a project developed using XE5 c++ that has the issue as reported. Can you please advise how we should implement your workaround in C++?

Thank you.