Hsqldb - returns the result set from the saved Proc

Question

I am trying to call a stored procedure in HsqlDB and return a result set

My saved process 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 that I use to invoke this process 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 process 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 fine when I try to call a stored procedure in MySql

Any help is appreciated.

Thanks Damien

Answer 1

ProcedureOutputs is an interface that extends Outputs ( source ).

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

Basically, you expected the conversion to be correct, since you are converting the result to the Output sub-interface. What you do is called downcast . Let's look at the case.

You want to transfer the object to another. Perhaps fading is possible, since ResultSetOutput extends Output . Since it may not be possible to omit, you do not get a compile-time error, and when possible, for example, when you call a stored procedure in MySQL, downcast will succeed. However, when downcast is not possible, you get an exception at runtime. In our particular case, downcast is not possible, because .getOutputs returns a different classification, possibly a sub-interface, which is in a different inheritance branch, such as UpdateCountOutput .

Answer 2

Lajosh is right about the demotion. The problem is that you are coding under the assumption that the Output returned by ProcedureOutputs.getCurrent() will be ResultSetOutput , when in fact it could be UpdateCountOutput .

In fact, the Output interface has an isResultSet() method that helps you determine that:

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

Determine if this return is a result (with the ability to click on ResultSetOutput ). An alternative is that this is the number of updates (with the ability to click on UpdateCountOutput ).

Return:

true indicates that this can be safely attributed to ResultSetOutput ), otherwise it can be distinguished to UpdateCountOutput .

In addition to this, Outputs can transmit multiple Outputs , and the state of Output.getCurrent() controlled by Output.goToNext() .

So, in order to handle multiple results correctly, you should get the output 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 (?,?)}

Sleep mode: {call p_getTeamTasksForLastXDays (?,?)}

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

do something with an update counter

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 string: 0

1671 [main] DEBUG org.hibernate.loader.Loader - result line:

do something with result set result

PS: I don’t have mysql here, so I can’t confirm if it returns both ResultSetOutput and UpdateCountOutput , but in a different order than hsqldb, but maybe you can check it.