Order in bind variable for PreparedStatement in Java - JDBC JAVA

This is a discussion on Order in bind variable for PreparedStatement in Java - JDBC 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 : ...

+ Reply to Thread
Results 1 to 2 of 2

Order in bind variable for PreparedStatement in Java

  1. Default 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

  2. Default Re: Order in bind variable for PreparedStatement in Java

    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)

+ Reply to Thread

Similar Threads

  1. java.sql.SQLException: ORA-01006: bind variable does not exist
    By Application Development in forum JDBC JAVA
    Replies: 2
    Last Post: 09-19-2006, 09:27 AM
  2. bind variable
    By Application Development in forum ADO DAO RDO RDS
    Replies: 0
    Last Post: 06-22-2006, 01:10 AM
  3. PreparedStatement + "order by ?"
    By Application Development in forum JDBC JAVA
    Replies: 2
    Last Post: 02-17-2006, 01:18 PM
  4. when and what to close java.sql.PreparedStatement
    By Application Development in forum JDBC JAVA
    Replies: 6
    Last Post: 11-05-2004, 05:28 AM
  5. Bind variable order in OracleCommand
    By Application Development in forum DOTNET
    Replies: 3
    Last Post: 10-23-2003, 12:50 PM