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

Line 229 is - ResultSetOutput resultSetOutput = (ResultSetOutput) outputs.getCurrent(); — Damien, Dec 21 '15 at 21:59

score 5 · Accepted Answer · edited May 23 '17 at 12:04

5

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.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 22 '15 at 13:42

Lajos Arpad

64,414
37
100
175

yes that is the case I am experiencing here. The same code works 100% fine with a MySql stored procedure so I want to replicate that proc in Hsqldb for my unit tests – Damien Dec 22 '15 at 15:12
What does outputs.getCurrent().getClass() return? – Lajos Arpad Dec 23 '15 at 19:35
@Damo, if you can find out what is the class of that object, then you will be able to find the solution. – Lajos Arpad Dec 23 '15 at 19:36
getCurrent().getClass() returns org.hibernate.result.internal.UpdateCountOutputImpl for the Hsqldb stored proc. For the MySql stored proc, it returns org.hibernate.result.internal.ResultSetOutputImpl – Damien Dec 24 '15 at 13:03
Also - when run against Mysql stored proc in debug mode - I get the following output - [DEBUG] [194]org.hibernate.result.internal.OutputsImpl[buildOutput] - Building Return [isResultSet=true, updateCount=-1, extendedReturn=false. For the Hsqldb stored proc in debug mode - I get this output - [DEBUG] [194]org.hibernate.result.internal.OutputsImpl[buildOutput] - Building Return [isResultSet=false, updateCount=0, extendedReturn=false – Damien Dec 24 '15 at 13:05
That UpdateCountOutputImpl looks like being the implementation of UpdateCountOutput, isn't it? If you implement a converter method, then you can call that and if you overload that method to handle the relevant conversion types, then you can use that method at multiple places safely. – Lajos Arpad Dec 25 '15 at 01:01
Yeah im not sure if I want to go to that hassle just for testing my stored procs. I have test stored procs that return normal values or update values just fine - the problem is with the result sets – Damien Dec 30 '15 at 14:40
@Damo, I perfectly understand you, but you either create a converter, or you implement a class for doing the stuff you need. I perfectly understand your frustration due to the fact that your code by itself cannot be used as a standard because the thing you have to use was not well planned, but the solution is to fill the gaps yourself. You will not have a great time while doing so, but after you finished this task, your code will be usable as a standard. If my answer solves your problem, then you might consider marking it as the correct answer. – Lajos Arpad Dec 30 '15 at 15:03

score 5 · Answer 2 · answered Dec 30 '15 at 17:04

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.

Thanks for your assiatance guys - this indeed solved the issue. Looking at the abouve code sample - Hsqldb returned the UpdateCountOutput result first and the ResultSetOutput second. Mysql returned the ResultSetOutput first and the UpdateCountOutput second — Damien, Dec 31 '15 at 12:33
"Outputs can deliver multiple Output", why a stored procedure will produce multiple outputs? — user1169587, Feb 07 '20 at 18:21

Hsqldb - Return a Result Set from Stored Proc

2 Answers2