Order in bind variable for PreparedStatement in Java
Hi everybody,
Sorry for my poor english on the size of the post.
I have a critical problem with a prepared query and bind variable in Java.
I try to execute the following query which do return one row :
select aclient.cli, bknom036.mnt1 tcli, aclient.nom, aclient.pre,
client.dna, aclient.age,case
when exists (select pro3 from bkcli bkcli3 where aclient.tce <= ?
then pro3
when exists (select pro2 from bkcli bkcli2 where aclient.tce <= ?
then pro2
when exists (select pro1 from bkcli bkcli1 where aclient.tce <= ?
then pro1
end pro, bknom001.lib1 libelleAgence, bknom035.lib2 cuti2, bknom035.lib1
cuti1, bknom036.lib1 intituleClient
from bkcli aclient
inner join bknom bknom035 on (bknom035.age = ? and bknom035.ctab = '035'
and bknom035.cacc = aclient.ges)
inner join bknom bknom001 on (bknom001.age = ? and bknom001.ctab = '001'
and bknom001.cacc = aclient.age)
left join bknom bknom036 on (bknom036.age = ? and bknom036.ctab = '036'
and bknom036.cacc = aclient.lib)
where aclient.cli=?
order by aclient.nom
The tce column is a decimal, age column a varchar and cli a varchar
with the following values :
prepared.setObject(1, new Double(1.112));
prepared.setObject(2, new Double(1.112));
prepared.setObject(3, new Double(1.112));
prepared.setString(4, "99000");
prepared.setString(5, "99000");
prepared.setString(6, "99000");
prepared.setString(7, "001000032");
No rows are returned.
If I don't respect the order of the bind variable like this :
prepared.setString(1, "99000");
prepared.setString(2, "99000");
prepared.setString(3, "99000");
prepared.setObject(4, new Double(1.112));
prepared.setObject(5, new Double(1.112));
prepared.setObject(6, new Double(1.112));
prepared.setString(7, "001000032");
The correct row is returned.
I am confused because it seems that the jdbc driver bind the variable
first in the inner join and after in my case ... when inspite their
order in the query.
The same query work perfectly under Oracle whit the good order for the
bind variables.
My informix database is 9.40.UC2
My Jdbc informix driver is 2.21.JC5
If anybody have an explaination.
thanks
Stef