How to call MS SQL stored procedures with more than one output parameters using Hibernate 4?

Question

Colleagues, could you help me with calling MS SQL 2008 store procedure with two input and two output parameters using Hibernate 4? The stored procedure looks like:

PROCEDURE Product_Create(
        @Return  int  output,
        @ProdID numeric(18,0)  output,

        @PassID numeric(18,0),
        @Amount numeric(18,2))

I am trying to use session.createSQLQuery method to call procedure:

Query qr = session.createSQLQuery("{? = Product_Create (:PassID, :Amount")});
qr.setParameter("PassID", new BigInteger("999999999"));
qr.setParameter("Amount", new BigDecimal("87384738"));

But there are some unclear moments:

How to register more than one output parameters?
Is session.createSQLQuery approach suitable to call stored procedures with more than one output parameter?

Update

I have a MS SQL procedure

PROCEDURE Product_Create(
        @Return  int  output,
        @ProdID numeric(18,0)  output,

        @IN$PassID numeric(18,0),
        @IN$Amount numeric(18,2))

and have mapping file:

<hibernate-mapping>
    <sql-query name="Product_Create" callable="true">
        <return-scalar column = "Return" type="int"/>
        <return-scalar column = "ProdID" type="long"/>
        <![CDATA[CALL IProduct_Create (:@IN$PassID, :@IN$Amount)]]>
  </sql-query>
</hibernate-mapping>

When i run application than i receive exception:

WARN : [Tue-06 10:11:39,374] jdbc.spi.SqlExceptionHelper - SQL Error: 102, SQLState: S0001 ERROR: [Tue-06 10:11:39,374] jdbc.spi.SqlExceptionHelper - Incorrect syntax near '@P0'. Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) 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:130) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:76) 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:1873) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332) at org.hibernate.dialect.AbstractTransactSQLDialect.getResultSet(AbstractTransactSQLDialect.java:226) at org.hibernate.dialect.SQLServerDialect.getResultSet(SQLServerDialect.java:42) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:121) ... 16 more

I tried to make some changes in

  <![CDATA[CALL IProduct_Create (:@IN$PassID, :@IN$Amount)]]>

but without success.

What is wrong in my mapping?

UPDATE 1

Added brackets {} to call procedures. It works when call looks like

 <![CDATA[{CALL Product_Create (:@IN$PassID, :@IN$Amount)}]]>

Jordi Castilla Jordi Castilla · Accepted Answer · 2015-10-05T14:35:19

Call it with getNamedQuery().

Query query = session.getNamedQuery("product_create")
    .setParameter("PassID", new BigInteger("999999999"))
    .setParameter("Amount", new BigDecimal("87384738"));

List result = query.list();  // here you have DTO List i.e. List<Product>

How to call MS SQL stored procedures with more than one output parameters using Hibernate 4?

1 Answers