Order in bind variable for PreparedStatement in Java

[Stephane Philippart]

Hi everybody,

Sorry for my poor english and 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

[Jeanne Boyarsky]

Stef,
Sounds like a bug in the driver. Are there any other Informix drivers you can use?