Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much.
You might have wondered what happened here:
I HATE THE ORACLE JDBC DRIVER SO FUCKING MUCH…
— Michael Simons (@rotnroll666) July 19, 2012
Well easy, i had fun with the Oracle JDBC Driver and named parameters. I modified one call, added a parameter and set this parameter not in order… No problem, i thought, as i was using a callable statement with named parameter. As i it turns out, named parameters (“:blah”) are not so named, after all, but the order of setting them does matter, see the following tests:
package tests.jdbc; import java.sql.*; import org.junit.*; public class OracleCallableStatementTest { protected static Connection connection; @BeforeClass public static void setup() throws Exception { try{ Class.forName("oracle.jdbc.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>"); PreparedStatement ps = connection.prepareStatement("Create table callable_statement_test(column1 varchar(16), column2 varchar(16))"); ps.executeUpdate(); ps.close(); ps = connection.prepareStatement( "CREATE OR REPLACE procedure p_callable_test (p1 IN VARCHAR, p2 IN VARCHAR) AS " + "BEGIN " + " INSERT INTO callable_statement_test(column1, column2) values(p1, p2); " + "END;" ); ps.executeUpdate(); ps.close(); } catch(Exception e) { e.printStackTrace(); throw e; } } @Before public void prepareTable() throws SQLException { Statement st = connection.createStatement(); st.executeUpdate("Truncate table callable_statement_test"); st.close(); } private void verify() throws SQLException { final Statement st = connection.createStatement(); ResultSet rs = st.executeQuery("Select * from callable_statement_test"); rs.next(); String column1 = rs.getString("column1"); String column2 = rs.getString("column2"); rs.close(); st.close(); Assert.assertEquals("1", column1); Assert.assertEquals("2", column2); } @Test public void testCallableStatementParameterInOrder() throws SQLException { final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}"); ps.setString("p1", "1"); ps.setString("p2", "2"); ps.execute(); ps.close(); verify(); } @Test public void testCallableStatementParameterNoOrder() throws SQLException { final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}"); ps.setString("p2", "2"); ps.setString("p1", "1"); ps.execute(); ps.close(); verify(); } @AfterClass public static void tearDown() throws SQLException { if(connection == null) throw new RuntimeException("No connection..."); PreparedStatement ps = connection.prepareStatement("Drop procedure p_callable_test"); ps.executeUpdate(); ps.close(); ps = connection.prepareStatement("Drop table callable_statement_test"); ps.executeUpdate(); connection.close(); } } |
In this test i actually used the same names of parameters like in the stored procedure and you clearly see p1 should be inserted in column1 and p2 in column2.
#testCallableStatementParameterInOrder succeeds, but #testCallableStatementParameterNoOrder fails. The only difference is that i set p2 before p1… To me, this is a severe bug as named parameters imply a very different behavior.
I strongly advice my colleagues to be very careful about using plain named parameters. The order in which they are set is important!
I could reproduce this behavior with OJDBC 11.1.0.7 and 11.2.0.3 against Oracle 10g and 11g. The example does not work at all with OJDBC 10.2.x.
6 comments
Hi Michael
Do you know if there is a Oracle SR for this bug?
Regards
Christian
Hi Christian,
no, don’t know… Would be interesting, indeed.
Regards,
Michael
I know the post is a bit old, but maybe somebody will be reading it…
If you take a look at the oracle specs they clearly mention that those methods are not supported anymore, you should use methods like setXXXAtName()
Reference: http://docs.oracle.com/cd/E246.....apxref.htm
Hi Omar,
sure i read this.
Have a look at the official JDBC Api:
CallableStatement
I don’t see any of the mentioned methods… I want to code against JDBC, not some specific vendor.
Don’t use :foo parameter markers. Use standard JDBC ‘?’ parameter markers. Then it will work fine. The String arg in setXXX(String,XXX) is the formal name of the stored procedure parameter not the name of the parameter marker.
:foo style parameter markers are supported via an Oracle proprietary API not setXXX(String,XXX). setXXX(String,XXX) is a JDBC standard API that is defined to refer to the formal parameter names in the stored procedure, not the :foo parameter markers. Oracle recommends using the JDBC standard parameter markers ‘?’ and supports :foo markers and corresponding proprietary API only for backward compatibility. Note I am not telling you what the proprietary API is intentionally.
Hello Douglas,
thank you very much for your comment. That sheds some light on the whole thing (and actually solves other problems). I’m well aware of the fact, that PL/SQL functions and procedures do support named parameter (as opposed to Java for example) and i like that very much.
In this case, the problem was somewhere around 40cm in front of the monitor and didn’t the api very well.
And please note, i’m not interested in the internal api, as i said before: I want to code against JDBC 😉
Have a nice day and thanks again!
Post a Comment