Subject Re: [ib-support] Parameter in the Where clause
Author Helen Borrie
At 08:34 PM 01-08-01 +0800, you wrote:
>Hi Lester,
>In fact, what I want may be a bit too greedy, I am thinking of something
>like this:
>select * from TABLE1 :WHEREPARAM
>where :WHEREPARAM might even be empty.
>Best wishes,

In IB Objects you can do this with ease but AFAIK there is no way to do it with IBX or the BDE.

In IBO you can have you IB_Query with SQL of
SELECT whatever FROM aTable

and then, in the OnPrepareSQL event, you can add whatever pieces you want to be in the WHERE clause, e.g.

with MyQuery do
SQLWhereItems.Clear; // not usually necessary as the component does it
SQLWhereItems.Add( '(' );
SQLWhereItems.Add( 'MYCOL1 = 100' );
SQLWhereItems.Add( 'OR' );
SQLWhereItems.Add( 'MYCOL1 = 200' );
SQLWhereItems.Add( ')' );

and when parsed into the finalized SQL statement will end up as:

WHERE .... < original criteria plus other system stuff >
AND ( MYCOL1 = 100 OR MYCOL1 = 200 )

etc. etc.

Otherwise, without these benefits, you don't have any choice but to clear and reparse the statement yourself whilst the dataset object is unprepared and manufacture it by concatenating various strings, before preparing it again.


All for Open and Open for All
InterBase Developer Initiative ยท