2006/10/5, Helen Borrie <helebor@...>:

> At 08:00 PM 5/10/2006, you wrote:
> >Here is what I encountered with the following SQL, anyways:
> >
> >select
> > u.id, u.name, u.u_us,
> > case when( exists(select a.* from
> > add_agents a where a.id_main=:id_add and a.id_user=:id_user) ) then
> > "I" else
> > case when( exists(select a.* from add_agents a where
> > a.id_main=:id_add) ) then
> > "T" else
> > "F"
> > end
> > end as has_agents from
> >sys_users u where u.id=:id_salesman

[snip]

> >This might not be the most noble way to do it, but that's not the matter
> >here. The problem I encountered is that the parameter-values for
> >id_add and id_salesman are mixed up. The query does not work
> >as expected when I do the following:

[snip]

> >I tried the same query with IBPP, a lower-level Interbase/Firebird API and
> >it worked as expected without mixing any values, so I highly suspect
> >IBObjects as the culprit.
>
> I'm not familiar with the IBPP implementation so I can't surmise
> whether what you claim is true or not. However, given that IBPP is
> no more than a wrapper for the IB/Fb API, I fancy that your "worked
> as expected" was a matter of coincidence.

Yes, IBPP is quite a thin layer for the IB/FB API, so I'm using "?" there and
set the parameters by their "index".
Here's a snippet for the IBPP code:

Statement st = StatementFactory(db,tr);
st->Prepare("select u.id, u.name, u.u_us, "
"case when( exists(select a.* from add_agents a where
a.id_main=? and a.id_user=? /* id_user */ ) ) then \"I\" else "
"case when( exists(select a.* from add_agents a where
a.id_main=? ) ) then \"T\" else \"F\" end end as has_agents "
"from sys_users u where u.id=? /* id_salesman */");
st->Set(1,6);
st->Set(2,4);
st->Set(3,6);
st->Set(4,60);
st->Execute();

> What I can say is that your Delphi usage of parameters is making the
> common (but wrong) assumption that parameters are variables.

Delphi -- or IBO, for that matter -- rather encourages the use of "named"
parameters, IMHO. It's often more flexible and easier to use, after all.

> They are not. They are placeholders for collections of data
> attributes. "Assigning" to a parameter involves applying a
> compatible constant value to *that* parameter. You need one
> parameter for each replaceable piece in you statement - you can't
> "recycle" a parameter and re-use it elsewhere in the statement.

Yes, I'm aware of that. I just wanted to state that there was an error in the
IBO "layer" that converts those "variable-style" parameters to actual
"interbase-style", unnamed parameters.
Thanks for the clarification, though.

> Change your statement to:
>
> select
> u.id, u.name, u.u_us,
> case when( exists(select 1 from
> add_agents a where a.id_main=:id_add and a.id_user=:id_user) ) then
> "I" else
> case when( exists(select 1 from add_agents a where
> a.id_main=:id_add1) ) then
> "T" else
> "F"
> end
> end as has_agents from
> sys_users u where u.id=:id_salesman
>
> Then, for your assignments:
>
> C.ParamByName('id_add' ).AsInteger := AdressId;
> C.ParamByName('id_add1' ).AsInteger := AdressId;
> C.ParamByName('id_user' ).AsInteger := UserId;
> C.ParamByName('id_salesman').AsInteger := SalesmanId;

I tried that earlier, too, but I still didn't succeed.
When I use the "classic" way and use unnamed parameters -- that is,
replace every :foo with a simple ? -- and work with
C.Params[x].AsInteger := FooId,
the bug does not occur.

Thanks for your answer, Helen.

By the way: IBO version is 4.2.Ib and Firebird version is 1.5.0.4290 (Linux).

--
eat(this); // delicious suicide