Subject RE: [IBO] Multiple instances of the same parameter
Author Svein Erling Tysvær
>> 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;
> With my little mind, I thought that parameters are processed at IBO level but not passed to FIREBIRD's
> API (or something like that).
> I use FIREBIRD, IBO and DELPHI since a long time but I never took the time to understand what IBO really
> does or how it works...

Hi Laurent!

I don't doubt Helen is right, but still my big head with a malfunctioning brain thinks similar to you. If things are identical, I expect IBO to allow me to use a parameter twice. Though that requires all references to the parameter to have identical type, so I'd change your original query to:

select * from T
where (CAST(:P1 AS VARCHAR(32)) > '' and T.F1 = :P1)
or (CAST(:P2 AS VARCHAR(32)) > '' and T.F1 = :P2)

At least, when I do this change in IB_SQL, the number of parameters change from 4 to 2. Note, though, that the parameter gets cs = NONE, so I guess I'd probably use (CAST(:P1 AS VARCHAR(32) CHARACTER SET UTF8) if T.F1 uses that character set.