I am unable to get scalar int value returned from my stored procedure.
My stored procedure that returns scalar value 0 or -1
CREATE PROCEDURE MY_PROC
@ID BIGINT
AS
IF EXISTS ( SELECT NULL FROM USER_TABLE WHERE ID = @ID )
RETURN 0
ELSE
RETURN -1
GO
Code :
SQLQuery query = null;
{
query = session.createSQLQuery("{CALL MY_PROC(:ID)}");
query.setParameter("ID", 1);
Object status =query.uniqueResult();
System.out.println(status.toString());
}
STACKTRACE :
WARN: SQL Error: 0, SQLState: null
Aug 24, 2015 11:15:50 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: The statement did not return a result set.
org.hibernate.exception.GenericJDBCException: could not extract ResultSet
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
at com.hooks.Hook.preHook(Hook.java:39)
at com.hooks.Hook.main(Hook.java:24)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:392)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 16 more
According to my understanding that this procedure returns scalar value not resultset hence hibernate doesn't allow to get scalar value returned from stored procedure. Hibernate can only get resultSet returned from stored procedure. Hibernate reference doc
