#1431 HSQLDB setting wrong input parameter name for procedure when preparing callablestatement

[Ramiro Serrato]

I am using HSQLDB as in memory database in one of my junit tests for a generic set of classes that I wrote for calling stored procedures.

(Note: This is also posted in http://stackoverflow.com/questions/34779581/hsqldb-setting-wrong-input-parameter-name-for-procedure-when-preparing-callables)
Initializing the db:

@BeforeClass
public static void initializeDB() throws SQLException, ClassNotFoundException {
    dataSource = new JDBCDataSource();
    dataSource.setDatabase("jdbc:hsqldb:mem:testdb;shutdown=true");
    dataSource.setLoginTimeout(10);
    dataSource.setUser("sa");

    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE users (id INTEGER, name VARCHAR(25), PRIMARY KEY(id))");
    statement.close();
    statement = connection.createStatement();
    statement.executeUpdate("INSERT INTO users VALUES(1, 'Ramiro')");
    statement.executeUpdate("INSERT INTO users VALUES(2, 'Chanukya')");
    statement.close();

    String storedProcedure1 = "CREATE PROCEDURE sp_say_hi(IN greeting_p VARCHAR(10)) "
            + "READS SQL DATA DYNAMIC RESULT SETS 2 "
            + "BEGIN ATOMIC "
            + "DECLARE result CURSOR WITH RETURN FOR SELECT COALESCE(greeting_p, 'Hi')+' '+name as greeting FROM users FOR READ ONLY; "
            + "DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM users FOR READ ONLY; "
            + "OPEN result; "
            + "OPEN result1; "
            + "END";

    statement = connection.createStatement();
    statement.execute(storedProcedure1);
    statement.close();

    connection.commit();
}

Then at some point inside the class that prepares the statement I use setObject method to register the input parameter:

...
CallableStatement callableStatement = con.prepareCall(sqlCall);
callableStatement.setObject("greeting_p", "Hola");
...

The setObject method throws a JavaSqlException with cause:

Caused by: org.hsqldb.HsqlException: Column not found: greeting_p
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)

By using the debugger, I was able to find that there is a package private method in org.hsqldb.jdbc.JDBCCallableStatement class which is HSQLDB's implementation of CallableStatement, the method name is findParameterIndex which is called inside the setObject method, which checks if the provided parameter exists in the map of parameters from the procedure:

int findParameterIndex(String parameterName) throws SQLException {
    if (isClosed || connection.isClosed) {
        checkClosed();
    }

    int index = parameterNameMap.get(parameterName, -1);

    if (index >= 0) {
        return index + 1;
    }

    throw JDBCUtil.sqlException(ErrorCode.JDBC_COLUMN_NOT_FOUND,
                            parameterName);
}

by exploring that map using the debugger I was able to see that the parameter name is wrongly set by hsqldb, at least in this map:

[@p1, null, null, null, null, null, null, null]

I was able to verify by changing the name in the setObject method call:

callableStatement.setObject("@p1", "Hola");

After that it worked fine.
The weird thing is that if I retrieve the metadata of that procedure using DatabaseMetaData.getProcedureColumns method, the name of the param is correct from the jdbc metadata perspective:

DatabaseMetaData dbMetadata = con.getMetaData();
    ResultSet rs = dbMetadata.getProcedureColumns(con.getCatalog(),
            con.getSchema(),
            "SP_SAY_HI",
            "%_P");
        while(rs.next()) {
          // get stored procedure metadata
          String procedureCatalog     = rs.getString(1);
          String procedureSchema      = rs.getString(2);
          String procedureName        = rs.getString(3);
          String columnName           = rs.getString(4);
          short  columnReturn         = rs.getShort(5);
          int    columnDataType       = rs.getInt(6);
          String columnReturnTypeName = rs.getString(7);
          int    columnPrecision      = rs.getInt(8);
          int    columnByteLength     = rs.getInt(9);
          short  columnScale          = rs.getShort(10);
          short  columnRadix          = rs.getShort(11);
          short  columnNullable       = rs.getShort(12);
          String columnRemarks        = rs.getString(13);

          System.out.println("stored Procedure name="+procedureName);
          System.out.println("procedureCatalog=" + procedureCatalog);
          System.out.println("procedureSchema=" + procedureSchema);
          System.out.println("procedureName=" + procedureName);
          System.out.println("columnName=" + columnName);
          System.out.println("columnReturn=" + columnReturn);
          System.out.println("columnDataType=" + columnDataType);
          System.out.println("columnReturnTypeName=" + columnReturnTypeName);
          System.out.println("columnPrecision=" + columnPrecision);
          System.out.println("columnByteLength=" + columnByteLength);
          System.out.println("columnScale=" + columnScale);
          System.out.println("columnRadix=" + columnRadix);
          System.out.println("columnNullable=" + columnNullable);
          System.out.println("columnRemarks=" + columnRemarks);
        }

It prints out:

stored Procedure name=SP_SAY_HI
procedureCatalog=PUBLIC
procedureSchema=PUBLIC
procedureName=SP_SAY_HI
columnName=GREETING_P
columnReturn=1
columnDataType=12
columnReturnTypeName=CHARACTER VARYING
columnPrecision=10
columnByteLength=0
columnScale=0
columnRadix=0
columnNullable=1
columnRemarks=null

Note that the name is coming in upper case, I already check that. I changed the registering of the parameter name to upper case in the setObject method to see if that helped, but didn't make a difference.

[Ramiro Serrato]

I am using this version of hsqldb:

<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.3.3</version>
  <scope>test</scope>
</dependency>

[Fred Toussi]

Thanks for reporting. This was actually marked in the code as a todo item a long time ago. It has now been fixed and committed to SVN.