At 12:14 AM 24/07/2012, l_gilbert_fr wrote:

>Hi all,
>
>I have a little problem with a simple query where I use the same parameter two times.
>
>My query looks like :
>
>select * from T
> where (:P1 > '' and T.F1 = :P1)
> or (:P2 > '' and T.F1 = :P2)
>
>T.F1 is defined as a VARCHAR(32)
>
>This not my real query but just a stupid example to explain the problem :-):-)
>
>If I prepare the query from the IBO query component, I show four parameters (2*P1 and 2*P2 in this case). The first P1 is a "text CHAR(0) /* cs=UTF8 */" and the second P1 is a "(vartext)VARCHAR(32) /* cs=NONE */
>
>I get no error but it doesn't work if I set P1 to an existing value.
>
>Any idea ?

Parameters are not variables. "Named parameters" in Delphi are place-holders for members of a positional structure that is described in the API as a list of "?". So your query is passed as
select * from T
where (? > '' and T.F1 = ?)
or (? > '' and T.F1 = ?)
along with a list of XSQLVAR structures (?,?,?,?) into which the engine will return a "template" describing type and size for each late-bound value, in matching order.

If you pass four parameters in a statement then you need to describe four parameters and assign values to each one distinctly, thus:

select * from T
where (:P1a > '' and T.F1 = :P1b)
or (:P2a > '' and T.F1 = :P2b)

For the late-binding, you assign as follows:

ParamByName('P1a').AsWhatever := Var1;
ParamByName('P1b').AsWhatever := Var1;
ParamByName('P2a').AsWhatever := Var2;
ParamByName('P2b').AsWhatever := Var2;

If you are using a very old version of Firebird or a very old version of IBO, then you might run into another parameter-related problem. Prior to Fb 2 and in all old InterBase versions, the API had a bug that inverted the ordering of the parameters during the Prepare. IBO addressed this bug by deliberately inverting the order of the parameters before passing the statement to Prepare. In firebird.conf you could set a configuration parameter OldParameterOrdering = 1 to make your IBO applications work properly. The bug was fixed in Fb 2 and OldParameterOrdering became deprecated. AFAIR, it is not supported at all from Fb 2.1 forward.

Helen