>I have a TIBOQuery object in a Delphi program whose SQL is a fairly
>complex three-way union, using the "union all" option to capture all
>records. By default, TIBOQuery objects appear with their
>RecordCountAccurate property set to true. When I test this query in
>Database Workbench, it opens and returns the records it shouldl; when I
>put it into the TIBOQuery and Activate it in the Delphi IDE, no problem.
>At run time (based on watching it in Debug) the query opens and gets
>results just fine -- but calling RecordCount on it throws an "unexpected
>end of command" ISC Error. If I set RecordCountAccurate to False, no
>problem. (The RecordCount test was for RecordCount= 0, which still works
>as expected when there's an empty resultset.)
>
>So -- is this a bug, or a feature? Is there some incompatibility between
>RecordCountAccurate, the RecordCount method, and complex union SQLs?
>Just seeking understanding ...

Have you tried to see what SQL IBO is trying to execute, Lane? I know it is possible to use TIB_Monitor (or similar, I don't remember the name), though I rarely use TIBOQuery and don't know whether such monitoring only applies to TIB_ or if TIBO is also covered. Maybe MON$STATEMENTS can give similar information (though I don't know whether things that fail to prepare gets here or not).

I assume the SQL issued is incorrect, and I assume Jason to want to see your three-way union as well as the SQL that IBO creates when counting and that gives the error. He probably also want to know which IBO version you are using.

I also think it should be possible to circumvent your problem, at least if you're on Firebird 2.5 (and probably also 2.1), just embrace your statement with a CTE, i.e. change

SELECT <fields>
FROM <query>
UNION
SELECT <fields>
FROM <query>
UNION
SELECT <fields>
FROM <query>

to

WITH MyCTE(<fields>) AS
(SELECT <fields>
FROM <query>
UNION
SELECT <fields>
FROM <query>
UNION
SELECT <fields>
FROM <query>)
SELECT * FROM MyCTE

Don't think I've encountered your problem before, but I have added CTEs to avoid problems (I think the typical situation has been when I've wanted to use only one parameter in a query, but use this parameter to match both a CHAR and a VARCHAR field).

HTH,
Set