This discussion is archived
8 Replies Latest reply: Jun 7, 2013 2:48 PM by dsurber RSS

Bind Variables Out of Order

eric.hill Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    right. My mistake. I was thinking about ResultSet.getXXX("a") etc. Nevermind...
  • 6. Re: Bind Variables Out of Order
    Joe Weinstein Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points