Subject Re: [IBO] column list & variable list do not match (SOLVED)
Author Helen Borrie
At 10:17 PM 21/11/2004 +0000, you wrote:

>Thanks you both Helen & Jason for your suggestions, from these I've
>finally figured the problem.
>Following Jasons suggestion of using the client trace, I found that
>once the report was about to display the results the query was being
>changed to:
>So at least now I could SEE how the two sets were different, but why
>was my query being changed from two identical, explicitly created sets
>to one containing COUNT(*)?
>The property TIBQuery.RecordCountAccurate was the culprit.
>I've NEVER used that property, and checking now I see that this
>property is true by default.

I think that it shouldn't be true by default but that it was made so under
duress to be compatible with the VCL's dataset. As you probably know,
select count(*) on the query in hand is the only way to get an accurate
count of records in a set with IB/Fb.

>Any chance someone can explain why this happens though.

When RecordCountAccurate is false, IBO gets the record count by counting
the rows in the buffer. The rows in the buffer are not necessarily all of
the rows in the set.

In ReadCommitted isolation, I think a select count(*) is done regardless
when an application tries to read RecordCount. In Concurrency isolation,
the row-by-row count is bean-counted as rows are fetched into the buffer
and is cached.

Did setting RecordCountAccurate to false fix the problem?

>I mean, I have no code which asks for a RecordCount, so even though
>that property is true, why would it even be trying to calculate the

QuickReport is probably reading RecordCount for pagination or some other
purpose, for example if you have defined a header or footer field that
depends on knowing the size of the set before the report is finished, e.g.
"Page m of n".

>and thereby silently screwing around with the statement I pass it?

It's not "screwing around" with your statement - the statement object
itself remains intact.

What's happening is that, when the application asks for an accurate
recordcount, IBO is using the statement tries to construct another
(separate) SELECT COUNT(*) query by prepending " count(*), " to the output
fields, probably because you're running the report in ReadCommitted
isolation (which is never a good thing for reports under any circumstances!).

But I don't know of any way to get a count like this from a regular UNION
statement. Possibly Jason needs to look at this implementation and
pre-empt a call to RecordCountAll if the word "UNION" appears in the
statement. That way, when QR asks for a recordcount, the exception will
accurately pinpoint the problem with a message like "Accurate record count
is not possible for a UNION query".

At bottom, if your report really must do things that need a recordcount,
you'll have to make the statement into a view *and*, of course, run reports
in Concurrency isolation.

QuickReport is very old and was written for Paradox, anyway. It does what
it does. As the owner of a full licence to QuickReport, I spent a lot of
time communicating with its authors around Delphi 4 (we're talking 1997-98
here) and they had clearly already given up on it, even then. It was a
baby that grew out of its pram. Keep things simple and it will do it.