Oracle, JBDC, CallableStatements and named parameters
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.