Hsqldb - Return a Result Set from Stored Proc

Question

I am trying to call a stored procedure in HsqlDB and return a Result Set

My Stored proc is as follows

    CREATE PROCEDURE p_getTeamTasksForLastXDays(IN teamId BIGINT, IN numberOfDays BIGINT) READS SQL DATA
  DYNAMIC RESULT SETS 1
  BEGIN ATOMIC
   declare curs cursor for select taskId, taskName from V_TASK_DETAILS;
   open curs;
  END;
  /;

The Java and hibernate code I am using to call this proc is as follows

public void getTaskExecutionLogs(Long teamId, Long numberOfDays) {
        LOG.info("Entered getTaskExecutionLogs Method - teamId:{}, numberOfDays: {}", teamId, numberOfDays);

        ProcedureCall procedureCall = currentSession().createStoredProcedureCall("p_getTeamTasksForLastXDays");
        procedureCall.registerParameter( TEAM_ID, Long.class, ParameterMode.IN ).bindValue( teamId );
        procedureCall.registerParameter( NUMBER_OF_DAYS, Long.class, ParameterMode.IN ).bindValue( numberOfDays );

        ProcedureOutputs outputs = procedureCall.getOutputs();
        ResultSetOutput resultSetOutput = (ResultSetOutput) outputs.getCurrent();

        List resultSetList = resultSetOutput.getResultList();

}

The error I get when I try to call this proc is as follows

java.lang.ClassCastException: org.hibernate.result.internal.UpdateCountOutputImpl cannot be cast to org.hibernate.result.ResultSetOutput
    at com.mct.dao.database.impl.TaskDetailsDAOImpl.getTaskExecutionLogs(TaskDetailsDAOImpl.java:229)

The exact same code works ok when I try to call a stored proc in MySql

Any help is greatly appreciated

Thanks Damien

Answer 1

ProcedureOutputs is an interface which extends Outputs (source).

ResultSetOutput is an interface which extends Output (source). When you call getCurrent() of Outputs, you will get an Output (source).

Basically, you expected the conversion to be correct, since you convert the result into a sub-interface of Output. The thing you are doing is called downcast. Let's see the case.

You want to cast an object to another. The downcast might be possible, since ResultSetOutput extends Output. Since the downcast might be possible, you do not get a compile time error and when it is possible, such as in the case when you call a stored procedure in MySQL, the downcast will be successful. However, when the downcast is not possible, you get a runtime exception. In our particular case, the downcast is not possible, since .getOutputs returns another classification, maybe a sub-interface which is on another inheritance branch, like UpdateCountOutput.

Answer 2

Lajos is right about the downcast. The problem is that you're coding under the assumption that the Output returned by ProcedureOutputs.getCurrent() will be a ResultSetOutput, when in reality, it may be an UpdateCountOutput.

In fact, the Output interface has a method isResultSet() to help you determine that:

boolean org.hibernate.result.Output.isResultSet()

Determine if this return is a result (castable to ResultSetOutput). The alternative is that it is an update count (castable to UpdateCountOutput).

Returns:

true indicates that this can be safely cast to ResultSetOutput), other wise it can be cast to UpdateCountOutput.

In adition to that, Outputs can deliver multiple Output and the state of Output.getCurrent() is controlled by the Output.goToNext().

So, in order to properly handle multiple results, you have to get the outputs with something like this:

ProcedureOutputs outputs = procedureCall.getOutputs();

do {
    Output current = outputs.getCurrent();

    if (current.isResultSet()) {
        ResultSetOutput resultSetOutput = (ResultSetOutput) current;
        System.out.println("do something with result set output");
    } else {
        UpdateCountOutput updateCountOutput = (UpdateCountOutput) current;
        System.out.println("do something with update count output");
    }            
} while (outputs.goToNext());

outputs.release();

In my tests I get:

1647 [main] DEBUG org.hibernate.SQL - {call p_getTeamTasksForLastXDays(?,?)}

Hibernate: {call p_getTeamTasksForLastXDays(?,?)}

1668 [main] DEBUG org.hibernate.result.internal.OutputsImpl - Building Return [isResultSet=false, updateCount=0, extendedReturn=false

do something with update count output

1669 [main] DEBUG org.hibernate.result.internal.OutputsImpl - Building Return [isResultSet=true, updateCount=-1, extendedReturn=false

1671 [main] DEBUG org.hibernate.loader.Loader - Result set row: 0

1671 [main] DEBUG org.hibernate.loader.Loader - Result row:

do something with result set output

PS: I don't have mysql here, so I cannot confirm if it returns both the ResultSetOutput and UpdateCountOutput but in different order than hsqldb, but maybe you can verify that.