8 Replies Latest reply: Jun 7, 2013 4:48 PM by dsurber RSS

    Bind Variables Out of Order

    eric.hill

      I'm not sure where to post this, so please point me in the correct direction if necessary. It seems that binding variables in the sequence they appear in the SQL statement works fine, but binding them out of order causes the statement to be mis-executed. Here is the output on my machine, Win 7 Pro, JDK 1.7.0_21 against Oracle 11.2.0.3 on Linux 64.

      Creating test table
      Inserting temporary data
      Showing all data
      10 50 Red
      20 40 Green
      30 30 Blue
      10 40 Orange
      20 50 Yellow
      70 10 Pink
      Selecting A=10 and B=50 in order
      10 50 Red
      Selecting A=10 and B=50 in reverse order
      Dropping test table

      Here's the code that produces these results. The difference between the first and second select statements is simply the order the variables are bound. Am I doing this wrong?

      import oracle.jdbc.OracleDriver;

      import java.sql.CallableStatement;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;

      public class RunTests {
      private void log(String s) {
      System.out.println(s);
      }

      private void log(ResultSet row) throws Exception {
      log(row.getString("a") + "\t" + row.getString("b") + "\t" + row.getString("c"));
      }

      private void start(String[] args) throws Exception {
      DriverManager.registerDriver(new OracleDriver());
      Connection c = DriverManager.getConnection("jdbc:oracle:thin:@someserver:1521:sid", "user", "pass");

      log("Creating test table");
      CallableStatement cs = c.prepareCall("create table eric_example (a number, b number, c varchar2(30))");
      cs.execute();
      cs.close();

      log("Inserting temporary data");
      cs = c.prepareCall("insert into eric_example values (:a, :b, :c)");
      cs.setInt(":a", 10);
      cs.setInt(":b", 50);
      cs.setString(":c", "Red");
      cs.executeUpdate();

      cs.setInt(":a", 20);
      cs.setInt(":b", 40);
      cs.setString(":c", "Green");
      cs.executeUpdate();

      cs.setInt(":a", 30);
      cs.setInt(":b", 30);
      cs.setString(":c", "Blue");
      cs.executeUpdate();

      cs.setInt(":a", 10);
      cs.setInt(":b", 40);
      cs.setString(":c", "Orange");
      cs.executeUpdate();

      cs.setInt(":a", 20);
      cs.setInt(":b", 50);
      cs.setString(":c", "Yellow");
      cs.executeUpdate();

      cs.setInt(":a", 70);
      cs.setInt(":b", 10);
      cs.setString(":c", "Pink");
      cs.executeUpdate();
      cs.close();

      log("Showing all data");
      cs = c.prepareCall("select * from eric_example");
      ResultSet rs = cs.executeQuery();
      while (rs.next()) {
      log(rs);
      }
      rs.close();
      cs.close();

      log("Selecting A=10 and B=50 in order");
      cs = c.prepareCall("select * from eric_example where a = :a and b = :b");
      cs.setInt("a", 10);
      cs.setInt("b", 50);
      rs = cs.executeQuery();
      while (rs.next()) {
      log(rs);
      }
      rs.close();
      cs.close();

      log("Selecting A=10 and B=50 in reverse order");
      cs = c.prepareCall("select * from eric_example where a = :a and b = :b");
      cs.setInt("b", 50);
      cs.setInt("a", 10);
      rs = cs.executeQuery();
      while (rs.next()) {
      log(rs);
      }
      rs.close();
      cs.close();

      log("Dropping test table");
      cs = c.prepareCall("drop table eric_example");
      cs.execute();
      cs.close();

      c.close();
      }

      public static void main(String[] args) {
      RunTests runTests = new RunTests();
      try {
      runTests.start(args);
      } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
      }
      }

      }

        • 1. Re: Bind Variables Out of Order
          rp0428
          >
          I'm not sure where to post this, so please point me in the correct direction if necessary. It seems that binding variables in the sequence they appear in the SQL statement works fine, but binding them out of order causes the statement to be mis-executed. Here is the output on my machine, Win 7 Pro, JDK 1.7.0_21 against Oracle 11.2.0.3 on Linux 64.
          >
          Well what is it you expect to bind with this that includes the colon in the name?
          cs.setInt(":a", 10);
          And code like this
          cs.setInt("a", 10);
          is NOT supported. You cannot bind by name using the setXXX methods.

          Modify your code to use supported syntax and you shouldn't have a problem.
          cs = c.prepareCall("select * from eric_example where a = :1 and b = :2");
          cs.setInt(1, 10);
          cs.setInt(2, 50);
          See 'Binding Named Parameters' in the JDBC Dev Guide
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/apxref.htm#BABEJDBH
          >
          Binding Named Parameters

          Binding by name is not supported when using the setXXX methods. Under certain circumstances, previous versions of Oracle JDBC drivers have allowed binding statement variables by name when using the setXXX methods. In the following statement, the named variable EmpId would be bound to the integer 314159.

          PreparedStatement p = conn.prepareStatement
          ("SELECT name FROM emp WHERE id = :EmpId");
          p.setInt(1, 314159);

          This capability to bind by name using the setXXX methods is not part of the JDBC specification, and Oracle does not support it. The JDBC drivers can throw a SQLException or produce unexpected results. Starting from Oracle Database 10g JDBC drivers, bind by name is supported using the setXXXAtName methods.
          >
          You may be using one of those 'previous versions' of the driver that appeared to support binding by name but such bindings are NOT supported so you should not try to use them.

          There are no workarounds. Replace the bind variable names with a question mark.
          • 2. Re: Bind Variables Out of Order
            Joe Weinstein-Oracle
            Hi Eric, it's a bug in the driver I expect, but you can avoid it by sticking to
            the java standard for statement parameter demarcation and setting:

            cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
            cs.setInt(1, 10);
            cs.setInt(2, 50);
            rs = cs.executeQuery();
            while (rs.next()) {
            log(rs);
            }
            rs.close();
            cs.close();

            log("Selecting A=10 and B=50 in reverse order");
            cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
            cs.setInt(2, 50);
            cs.setInt(1, 10);


            Creating test table
            Inserting temporary data
            Showing all data
            10 50 Red
            20 40 Green
            30 30 Blue
            10 40 Orange
            20 50 Yellow
            70 10 Pink
            Selecting A=10 and B=50 in order
            10 50 Red
            Selecting A=10 and B=50 in reverse order
            10 50 Red
            Dropping test table
            • 3. Re: Bind Variables Out of Order
              Joe Weinstein-Oracle
              and the reason I say a driver bug, is because this code:

              cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
              cs.setInt("a", 10);
              cs.setInt("b", 50);
              rs = cs.executeQuery();
              while (rs.next()) {
              log(rs);
              }
              rs.close();
              cs.close();

              log("Selecting A=10 and B=50 in reverse order");
              cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
              cs.setInt("b", 50);
              cs.setInt("a", 10);

              is supposed to work, but gives the bad result:

              Showing all data
              10 50 Red
              20 40 Green
              30 30 Blue
              10 40 Orange
              20 50 Yellow
              70 10 Pink
              Selecting A=10 and B=50 in order
              10 50 Red
              Selecting A=10 and B=50 in reverse order
              Dropping test table
              • 4. Re: Bind Variables Out of Order
                rp0428
                >
                and the reason I say a driver bug, is because this code:

                cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                cs.setInt("a", 10);
                cs.setInt("b", 50);
                rs = cs.executeQuery();
                while (rs.next()) {
                log(rs);
                }
                rs.close();
                cs.close();

                log("Selecting A=10 and B=50 in reverse order");
                cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                cs.setInt("b", 50);
                cs.setInt("a", 10);

                is supposed to work, but gives the bad result:
                >
                Please explain how that could possibly work. First, neither of those queries is using a named bind variable:
                >
                cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                >
                So how can you use a 'name' to set it?
                cs.setInt("b", 50);
                cs.setInt("a", 10);
                Second binding by name that way is not supported - per the Oracle JDBC Dev Guide - as quoted above
                >
                Binding Named Parameters

                Binding by name is not supported when using the setXXX methods. Under certain circumstances, previous versions of Oracle JDBC drivers have allowed binding statement variables by name when using the setXXX methods.
                • 5. Re: Bind Variables Out of Order
                  Joe Weinstein-Oracle
                  right. My mistake. I was thinking about ResultSet.getXXX("a") etc. Nevermind...
                  • 6. Re: Bind Variables Out of Order
                    Joe Weinstein-Oracle
                    Interesting enough though, that code does run, and you'd think the driver would
                    throw an exception from setInt("A", 10)... This below also 'works real good!' ;)


                    log("Selecting A=10 and B=50, setting params in order");
                    cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                    cs.setInt("foo", 10);
                    cs.setInt("bar", 50);
                    rs = cs.executeQuery();
                    while (rs.next()) {
                    log(rs);
                    }

                    log("Selecting A=10 and B=50, setting params in reverse order");
                    cs.setInt("bar", 50);
                    cs.setInt("foo", 10);
                    rs = cs.executeQuery();
                    while (rs.next()) {
                    log(rs);
                    }

                    C:\weblogic\dev\sandbox\joe>java RunTests2
                    The driver is 11.2.0.3.0
                    The DBMS is Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                    Creating test table
                    Inserting temporary data
                    Showing all data
                    10 50 Red
                    20 40 Green
                    30 30 Blue
                    10 40 Orange
                    20 50 Yellow
                    70 10 Pink
                    Selecting A=10 and B=50, setting params in order
                    10 50 Red
                    Selecting A=10 and B=50, setting params in reverse order
                    10 50 Red
                    Dropping test table
                    • 7. Re: Bind Variables Out of Order
                      eric.hill
                      I think the documentation doesn't line up with reality, because I've confirmed this behavior using the latest 11.2 JDBC thin driver. The whole "prior releases" thing is superfluous.
                      • 8. Re: Bind Variables Out of Order
                        dsurber
                        Joe Weinstein wrote:
                        and the reason I say a driver bug, is because this code:

                        cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                        cs.setInt("a", 10);
                        cs.setInt("b", 50);
                        rs = cs.executeQuery();
                        while (rs.next()) {
                        log(rs);
                        }
                        rs.close();
                        cs.close();

                        log("Selecting A=10 and B=50 in reverse order");
                        cs = c.prepareCall("select * from eric_example where a = ? and b = ?");
                        cs.setInt("b", 50);
                        cs.setInt("a", 10);

                        is supposed to work, but gives the bad result:
                        It is not supposed to work because the SQL is not a call, it is a select. setXXX(String, XXX) only works with stored procedure calls. The driver doesn't parse the SQL, that's the database's job, so the driver doesn't reject this and apparently neither does the db.

                        setXXX(String,XXX) is a bind by name. The name is the formal parameter name of the stored procedure. It is not the parameter marker name, :foo, in the SQL string. Oracle strongly discourages :foo parameter markers. Use JDBC standard parameter markers '?' instead. Used with '?' in a stored procedure call, setXXX(String,XXX) works just fine. Unfortunately the driver cannot reliably detect all the failure cases.