Problem with Firebird and CTE query
lazarus / 16-Databases

----------------------------------------------------------------------
BenLaz · 2014-05-29 06:40

Hello,

I have a problem with a CTE Query (recursive). Here is the query :

WITH RECURSIVE BTREE (ID) AS
      (SELECT ID
       FROM BINDERS WHERE PARENTID = 0
       UNION ALL SELECT L.ID
       FROM BINDERS L 
       INNER JOIN BTREE T ON T.ID = L.PARENTID)
SELECT BINDERS.* FROM BTREE INNER JOIN BINDERS ON BINDERS.ID =
BTREE.ID

When executing the request with a TSQLQuery there is no problem, but
when retrieving the results using

while not oQuery.EOF do
begin
  // Some code
  oQuery.Next;
end;

there is an error : Fetch "too many concurrent executions of the same
request".

The error occurs after several loops.

Does anyone have an idea ?

----------------------------------------------------------------------
BigChimp · 2014-05-29 10:55

My idea is that that shouldn't happen
Is this a Firebird error code though (i.e. the error has a GDS code
etc)?

What FPC/Lazarus version are you using? What OS?

If you can, could you raise a bug in the Bugtracker (link top left)
with example FPC program (e.g. adapted from
http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal)
and the required DDL to set up the tables?
Alternatively, you could attach it to a post in this thread as a zip
file.

Thanks.

----------------------------------------------------------------------
BenLaz · 2014-05-29 12:39

Hello,

Thank you for your answer.

I use Lazarus last release (1.2.2/ FPC 2.6.4) on Windows 7 32 bits.

I believe (but i'm not sure) that it's a FireBird error. The
ExceptionClass is EIBDatabaseError and the full error message is
"Fetch: -Too many concurrent executions of the same request"

I link to this post a sample of code generating the error using the
example programm you provided to me

Thank you for your help

----------------------------------------------------------------------
BigChimp · 2014-05-29 13:12

Yes, it does seem to be a firebird error:
Code: [Select]    try
      while not Q.EOF do
      begin
        Q.Next;
      end;
    except
      on F: EIBDatabaseError do
      begin
        writeln('Firebird error '+inttostr(F.GDSErrorCode) +
LineEnding +
          'Message : ' + F.Message);
      end;
      on E: Exception do
      begin
        writeln('Exception: '+E.Message);
      end;
    end;
QuoteFirebird error 335544663
Message :  : Fetch :
 -Too many concurrent executions of the same request
(ran it on x86 Firebird 2.5.1 (I think, 2.5.x for sure), Windows)
... so it is most likely a Firebird problem, not a FPC/Lazarus
problem.

Try running the same query in another tool such as FlameRobin or isql
and see if you get the same error...
Edit: just tried it in FlameRobin, run the query, right click on the
grid, click Fetch all records=>same error message...

----------------------------------------------------------------------
BenLaz · 2014-05-29 19:31

Thank you very much for your help.

I'll try to post in firebird's forums.

----------------------------------------------------------------------
Kazmirus · 2014-06-03 18:54

Are you shure that father.field and son.field are correct? Its look
like you try to read the same field once as father once as son.