Discussions

General J2EE: Can I call CallableStatement.getObject(int) ?

  1. I caught an "SQLException: Invalid column index" from the following code and was wondering whether someone would know can I call CallableStatement.getObject(int) in the first place? (the javadocs don't say anything about not being allowed to call that)

    // the stored procedure has over 20 parameters
    CallableStatement cs = connection.prepareCall("{call myproc(?,?,?,...)}");
    cs.setString(1, "foobar");
    ...
    Object value = cs.getObject(1); // throws the exception

    The stack trace looks like this:

    java.sql.SQLException: Invalid column index
            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
            at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:2797)
            at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:4858)
            at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:4839)
            at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:585)
            at weblogic.jdbc.pool.Statement.getObject(Statement.java:745)
            at weblogic.jdbc.rmi.internal.CallableStatementImpl.getObject(CallableStatementImpl.java:184)
            at weblogic.jdbc.rmi.SerialCallableStatement.getObject(SerialCallableStatement.java:291)
  2. Before you can invoke getObject() on a callable statement, you must designate that parameter as an output parameter. Furthermore you must do so before you call the execute() method of the callable statement.

    cs.registerOutParameter(1, Types.DOUBLE); // Or whatever the type is
    ...
    cs.execute();
    Object o = cs.getObject(1);

    For this to work:

    a) The parameter in question must be an output parameter
    b) The type that you specify must be correct

    The valid types are constants in the java.sql.Types class.