Ok, here is part two answered.

See below again.

Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: <steve@...>
To: <IBObjects@yahoogroups.com>
Sent: Monday, November 19, 2001 11:56 AM
Subject: [IBO] Problems with generated fields


> Also. A fellow programmer asked a question on this forum that was
> never answered. This relates to the query being re-written
> incorrectly
> and sent back to the database resulting in an error whenever we asked
> for a RecordCount.
>
> The question is repeated below. Again, note the generated field
> 'Selected':
>
> The IBO Monitor/Trace tool reports the following SQL on a TOBIQuery.
>
> -----------
> select pl.Packline, Var.Variety Variety, Gm.Ref GrMeth,
> Maturityindex.Ref Maturity,
> DateReceived, Pl.PacklineID, 'T' selected,
> (select count(distinct rdt.pagenumber) from rejectdetail rdt where
> rdt.packlineid = pl.packlineid) pagn
> from packline pl
> join GrowingMethod Gm on Gm.GrowingmethodID = Pl.GrowingmethodID
> join Variety Var on Var.VarietyID = Pl.VarietyID
> join MaturityIndex Mat on Mat.MaturityIndexID = Pl.MaturityIndexID
> where (select count(distinct rdtl.pagenumber) from rejectdetail rdtl
> where rdtl.packlineid = pl.packlineid) <> 0
> and Pl.PackhouseID = ? /* PackhouseID */
> and Pl.GrowerID = ? /* GrowerID */
> ORDER BY packline ASC
> -----------
>
> Asking for RecordCount causes an error. For example, place button on
> form with the following code:
>
> ShowMessage('number of records: ' + IntToStr(QryLines.recordCount) );
>
> This will produce an error reporting the following in the IBO
> Monitor/trace tool.
>
> -----------
> PREPARE STATEMENT
> TR_HANDLE = 28398556
> STMT_HANDLE = 28397488
>
> SELECT COUNT( DISTINCT rdt.pagenumber) from rejectdetail rdt where
> rdt.packlineid = pl.packlineid) pagn )
> from packline pl
> join GrowingMethod Gm on Gm.GrowingmethodID = Pl.GrowingmethodID
> join Variety Var on Var.VarietyID = Pl.VarietyID
> join MaturityIndex Mat on Mat.MaturityIndexID = Pl.MaturityIndexID
> where (select count(distinct rdtl.pagenumber) from rejectdetail rdtl
> where rdtl.packlineid = pl.packlineid) <> 0
> and Pl.PackhouseID = ? /* PackhouseID */
> and Pl.GrowerID = ? /* GrowerID */
> FIELDS = [ Version 1 SQLd 1 SQLn 1
> < SQLType: 496 SQLLen: 4 > = -1 ]
>
> ERRCODE = 335544569
> -----------
>
> It seems to attempt to re-write the query incorrectly.
>
> It seems that IBO removes the first open bracket of the field level
> sub-select, then adds a closing bracket to the end of the line, after
> the alias "pagn" and attempts to prepare the remainder of the now
> broken query.
>
> It doesn't like the closing bracket on the first line of the query.
> Note that this closing bracket was added by IBO and was not part of
> the original query.
>
> This can be re-created at any time on this query and worked without
> fail using the BDE. The query is cached and contains update queries
> that are never processed as the cache is never updated, but used for
> a selection tool only.
>
> Can you please confirm either a problem at our end in what we are
> doing, or if this is a problem at your end.

In cases where the query is too complex for IBO to figure out for you there
are two options you have available.

#1: Call the FetchAll method prior to requestiing the RecordCount property.
Then, it will have all the records in the buffer and it will simply return
that as the RecordCount.

#2: Provide your own OnGetRecordCount handler that will parse together the
correct SQL statement and augment the answer there with whatever alterations
are appropriate based on filtered records, etc.

> Many thanks
>
> Steve Peacocke
> steve@...
>
>
>
___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
> without the need for BDE, ODBC or any other layer.
>
___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
> keyword-searchable FAQ, community code contributions and more !
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>