Re: Order in bind variable for PreparedStatement in Java
- From: Lee Fesperman <firstsql@xxxxxxxxxxxxx>
- Date: Wed, 11 May 2005 21:31:42 GMT
Wilda wrote:
>
> 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.
I'd say that the Informix JDBC driver is seriously broken. I see a coupla scenarios:
+ The driver is rearranging the query and not compensating for it.
+ The Informix server is rearranging the query, and the driver is not catering to it.
+ The driver is just buggy.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
.
- References:
- Prev by Date: Re: large queries: optimal settings (sqlserver, jtds)
- Next by Date: Re: large queries: optimal settings (sqlserver, jtds)
- Previous by thread: Order in bind variable for PreparedStatement in Java
- Next by thread: Re: large queries: optimal settings (sqlserver, jtds)
- Index(es):
Relevant Pages
|
