This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 18, 2012 2:43 PM by dsurber RSS

Difference between setString and setStringAtName

HJHorst Explorer
Currently Being Moderated
Hi,

What is the difference between standard java.sql [url http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html#setString(java.lang.String, java.lang.String)]setString and oracle.jdbc [url http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OraclePreparedStatement.html#setStringAtName_java_lang_String__java_lang_String_]setStringAtName methods?

Both claim to set the designated parameter to a Java String value. They seem to behave diffently.

Groeten,
HJH
  • 1. Re: Difference between setString and setStringAtName
    Kayaman Guru
    Currently Being Moderated
    HJHorst wrote:
    They seem to behave diffently.
    How? Also the difference is that one is a standard and the other isn't.
  • 2. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    Well, setStringAtName throws an error "Attempt to set a parameter name that does not occur in the SQL" when a parameter is used that does not occur in the SQL, setString(String, String) does not throw an error.

    setString(String param, String sql) seems to work like setString(int i, String sql) where i is incemented with every call of setString.

    Groeten,
    HJH
  • 3. Re: Difference between setString and setStringAtName
    Joe Weinstein Expert
    Currently Being Moderated
    If you are saying that (for instance) if you have a callable statement with SQL " { call myproc( ?, ?, ?) }",
    where the procedure parameter names are param2, param2, param3, and you call setString("foo", "aaaaa"),
    setString("bar", "bbbbb"), setString("qwe", "ccccccc"), it works and delivers "aaaaaa", "bbbbbb", "cccccc"
    to the procedure? If so, this is an entertaining excursion of the driver away from the spec.
  • 4. Re: Difference between setString and setStringAtName
    rp0428 Guru
    Currently Being Moderated
    >
    Well, setStringAtName throws an error "Attempt to set a parameter name that does not occur in the SQL" when a parameter is used that does not occur in the SQL, setString(String, String) does not throw an error.

    setString(String param, String sql) seems to work like setString(int i, String sql) where i is incemented with every call of setString.
    >
    Well - you need to explain that one by showing an example since with a string there is nothing to increment. I suppose a 'purist' would say that the binary representation of the low-order character could be incremented but I'm not considering that.
  • 5. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    Joe Weinstein wrote:
    If you are saying that (for instance) if you have a callable statement with SQL " { call myproc( ?, ?, ?) }",
    where the procedure parameter names are param2, param2, param3, and you call setString("foo", "aaaaa"),
    setString("bar", "bbbbb"), setString("qwe", "ccccccc"), it works and delivers "aaaaaa", "bbbbbb", "cccccc"
    to the procedure? If so, this is an entertaining excursion of the driver away from the spec.
    Yes, this is what I find, when SQL is like myproc(:one,:two,:three) is does not matter what parameter names are used in setString(param,"qqq") only the order of the setStrings is important...

    Groeten,
    HJH
  • 6. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    Here is what I did...

    Create table:
    create table hjh(kol1 varchar2(20),kol2 varchar2(20),kol3 varchar2(80))
    Part of my code:
        sql = "insert into hjh values (:qwe2,:rr1,:ii3)";
        cs = this.getDBTransaction().createCallableStatement(sql, 0);
        ocs = (OracleCallableStatement)cs;
        try {
          ocs.setString("1a", "wat");
          ocs.setString("1b", "wordt");
          ocs.setString("1c", sql);
          ocs.execute();
          this.getDBTransaction().commit();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    This leads to:
    KOL1                 KOL2                 KOL3                                                                             
    -------------------- -------------------- -------------------------------------------------------------------------------- 
    wat                  wordt                insert into hjh values (:qwe2,:rr1,:ii3)                                         
    Groeten,
    HJH
  • 7. Re: Difference between setString and setStringAtName
    Joe Weinstein Expert
    Currently Being Moderated
    Entertaining! Double-check the Oracle driver docs to see if this is discussed at all,
    and you might file a bug report if you can argue it. Try using the correct parameter
    names, but in a mixed order, and see if it works as you'd expect it to, or not.
  • 8. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    That's what I tried to achieve in the first place.

    But it didn't work as expected...

        sql = "insert into hjh values (:2,:1,:3)";
        cs = this.getDBTransaction().createCallableStatement(sql, 0);
        ocs = (OracleCallableStatement)cs;
        try {
          ocs.setString("2", "first \"2\"");
          ocs.setString("1", "then \"1\"");
          ocs.setString("3", sql);
          ocs.execute();
          this.getDBTransaction().commit();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    
        sql = "insert into hjh values (:2,:1,:3)";
        cs = this.getDBTransaction().createCallableStatement(sql, 0);
        ocs = (OracleCallableStatement)cs;
        try {
          ocs.setString("1", "first \"1\"");
          ocs.setString("2", "then \"2\"");
          ocs.setString("3", sql);
          ocs.execute();
          this.getDBTransaction().commit();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    KOL1                 KOL2                 KOL3                                                                             
    -------------------- -------------------- -------------------------------------------------------------------------------- 
    first "2"            then "1"             insert into hjh values (:2,:1,:3)                                                
    first "1"            then "2"             insert into hjh values (:2,:1,:3)                                                
  • 9. Re: Difference between setString and setStringAtName
    rp0428 Guru
    Currently Being Moderated
    Can you provide both the name of the ojdbc jar file and the exact version? As well as the jdk versions being used for compilation and execution.

    Also confirm that there are no other JDBC jar files available in the path that might be being used (i.e. print out the version using Java during execution.)
  • 10. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    ojdbc6dms.jar as supplied by JDeveloper 11.1.1.6.0

    Manifest-Version: 1.0
    Ant-Version: Apache Ant 1.6.5
    Created-By: 1.5.0_30-b03 (Sun Microsystems Inc.)
    Implementation-Vendor: Oracle Corporation
    Implementation-Title: JDBC DMS
    Implementation-Version: 11.2.0.3.0
    Repository-Id: JAVAVM_11.2.0.3.0AS11.1.1.6.0_LINUX.X64_111104
    Specification-Vendor: Sun Microsystems Inc.
    Specification-Title: JDBC
    Specification-Version: 4.0
    Main-Class: oracle.jdbc.OracleDriver


    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Driver driver = DriverManager.getDriver("jdbc:oracle:thin:");
    System.out.println("driver: "+driver.getClass()+", major: "+driver.getMajorVersion()+", minor: "+driver.getMinorVersion());

    results in:
    driver: class oracle.jdbc.OracleDriver, major: 11, minor: 2
  • 11. Re: Difference between setString and setStringAtName
    rp0428 Guru
    Currently Being Moderated
    Ok - I think the JDBC Developer Guide may shed some light on this.
    http://docs.oracle.com/cd/B28359_01/java.111/b31224.pdf
    >
    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.

    The bound values are not copied by the drivers until you call the execute method.
    So, changing the bound value before calling the execute method could change the
    bound value.
  • 12. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    I just read http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28933

    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.

    Interesting, named parameters as specified by JDBC is something completely different it seems.

    Groeten,
    HJH
  • 13. Re: Difference between setString and setStringAtName
    HJHorst Explorer
    Currently Being Moderated
    Ah, this is what it means:
    create or replace procedure hjhp (kol1 varchar,kol2 varchar2, kol3 varchar2) is
    begin
    insert into hjh values (kol1,kol2,kol3);
    end;
          sql = "call hjhp(?,?,?)";
          ocs = (OracleCallableStatement)conn.prepareCall(sql);
          try {
            ocs.setString("kol2", "first \"2\"");
            ocs.setString("kol1", "then \"1\"");
            ocs.setString("kol3", sql);
            ocs.execute();
            conn.commit();
          } catch (SQLException e) {
            e.printStackTrace();
          }
    KOL1                 KOL2                 KOL3                                                                             
    -------------------- -------------------- -------------------------------------------------------------------------------- 
    then "1"             first "2"            call hjhp(?,?,?)                                                                 
    OK, but setString(String param, String sql) should really throw an exception "Attempt to set a parameter name that does not occur in the SQL" in my "insert into hjh values (:qwe2,:rr1,:ii3)" case.
    And the supplied javadoc of setStringAtName...not so good...

    Groeten,
    HJH
  • 14. Re: Difference between setString and setStringAtName
    dsurber Explorer
    Currently Being Moderated
    setFooAtName is an Oracle proprietary extension that supports Oracle proprietary SQL. setFoo(String, Foo) is a JDBC standard method that does not require proprietary SQL.

    Standard JDBC uses '?' as a parameter marker. setFoo(int, Foo) refers to the parameters by position. The int arg is the order of the '?' in the SQL counting from the left starting at 1.

    Oracle SQL uses named parameters where the names are prefixed with a ':'. setFooAtName is refers to the parameters in the SQL by name. This is an Oracle extension to support Oracle proprietary SQL.

    So standard JDBC looks like this:

    PreparedStatement p = conn.prepareStatement("select ?, ? from dual");
    p.setString(1, "first");
    p.setString(2, "second");

    Using Oracle proprietary SQL

    OraclePreparedStatement p = (OraclePreparedStatement)conn.prepareStatement("select :one, :two from dual");
    p.setStringAtName("one", "first");
    p.setStringAtName("two", "second");

    setFoo(String, Foo) is a standard method on CallableStatement. It is used for setting the parameters of a stored procedure call using standard JDBC.

    CallableStatement c = conn.prepareCall("{call proc(?, ?)}");
    c.setString("arg1", "first");
    c.setString("arg2", "second");

    The above code assumes that the two arguments to proc are named "arg1" and "arg2". As a general rule setFoo(String, Foo) should only be used for procedure calls.

    The above is the real, correct answer. Below is the under the covers stuff. Everything above is supported. Nothing below is supported; it's just what happens.

    The only type of parameters that the Oracle network protocol supports are positional. That is, all Oracle really supports is standard JDBC style positional parameters. However Oracle uses named parameter markers ':foo' not '?'. So when the JDBC driver sees '?' it converts it to a named parameter, ':1', ':2', etc. Then when the user calls setFoo(int, Foo) the driver binds the value to the numeric position.

    If the programmer uses Oracle style named parameters ':foo' in the SQL the driver notices this. It will throw an error if you mix '?' and ':foo' in the same SQL. It also keeps track of the order in which the names appear in the SQL. So when the programmer uses setFooAtName(String, Foo) the driver binds the value to every position where the name appears. Note that the value is bound multiple times and is sent across the network multiple times.

    OraclePreparedStatement p = (OraclePreparedStatement)conn.prepareStatement("select :a, :b, :a from dual");
    p.setStringAtName("a", "first and last");
    p.setStringAtName("b", "middle");

    There are three parameter positions in the SQL. The driver binds "first and last" to the first position, "middle" to the second position, and "first and last" again to the third position. "first and last" is sent across the network twice.

    If you are a C programmer familiar with OCI you may be thinking I'm nuts because OCI hides this very well but it's a fact about how the network transport works.

    So how about setFoo(String, Foo)? There only thing on the network is positions so the driver has to cheat. If you call setFoo(String, Foo) on a CallableStatement the driver rewrites the SQL to use positional binding. "{call proc(?, ?)}" is rewritten as "CALL proc(arg1<=:1, arg2<=:2)" where 'arg1' and 'arg2' are the values passed to setFoo. (Or something like that. I'm not going to look up the exact SQL.) This is a named parameter binding in Oracle PL/SQL syntax. Note that the args still go on the network using positional representation; that's all the network supports.

    Now the driver doesn't really understand SQL. It looks for some key words and special characters. If it doesn't recognize what it finds it just does the best it can and sends it on to the server so the server can sort it out. We do this on purpose because the driver needs to support everything it possibly can, even stuff it doesn't understand. This can lead to some interesting behavior. In particular, setFoo(int, Foo) works for Oracle-style named parameters in some versions of the driver, but it is still positional. This leads to surprising behavior.

    PreparedStatement p = conn.prepareStatement("select :a, :b, :a from dual");
    p.setString(1, "first");
    p.setString(2, "second");
    p.setString(3, "third");

    sends three distinct values even though there are only two parameter names. I don't think this works with all versions of the driver as it probably should be disallowed. The problem is how much the driver does or does not understand about the SQL.

    There are other surprising cases but they all result from the driver not really understanding the SQL and using positional binding to send values to the server. This behavior is not supported and varies from release to release. Only the stuff mentioned at the top is supported. If you do any of the odd stuff don't be surprised when it stops working.
1 2 Previous Next

Legend

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