Is it possible to invoke sql server 2008 stored procedures using Hibernate Query?

I am trying to call a stored procedure using Hibernate's Query class:

Query q = ssn.createSQLQuery("{ ? = call SEARCH_RESULT(?,?,?) }");

int idx = 0;
q.setParameter(idx, sc.getId(), StandardBasicTypes.INTEGER); 
q.setString(++idx, sc.getNum() == null ? null : sc.getNum()
        .toString()); // second parameter
q.setString(++idx, sc.getName());
List list = q.list();

But it gives following exception, although there is no 4th parameter: Request helpful sug开发者_运维知识库gestions and potential solution.

com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 4.
    com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildParamTypeDefinitions(SQLServerPreparedStatement.java:262)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:221)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(SQLServerPreparedStatement.java:598)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:386)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
    com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
    org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    org.hibernate.loader.Loader.doQuery(Loader.java:802)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    org.hibernate.loader.Loader.doList(Loader.java:2533)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    org.hibernate.loader.Loader.list(Loader.java:2271)
    org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)

I think there are 4 parameters

Query q = ssn.createSQLQuery("{ ? = call SEARCH_RESULT(?,?,?) }");

There are 4 question marks, one appears to be an out parameter, and 3 input parameters. Or 1 return and 3 inputs?

Not sure on NHibernate, never used it for executing stored procs, but it looks like you're telling NHibernate to expect 4 parms and only giving it 3.

Hibernate doesn't know what kind of object to convert your stored procedure result to. I usually specify a single return code/value for all my SPs, then makes sure to map the result class fields with the same column alias names in my SP return result.

For example...

{ call my_function(:param1, :param2) }

return_code = 404, message = "Page not found"

return_code = 200, message = "OK"

The class is mapped like any other POJO, just be sure to make it Serializeable. I use something like this:

@NamedNativeQueries({
  @NamedNativeQuery(name = "myFunction",
                  query = "{ call my_function(:param1, :param2) }",
                  resultClass = StoredProc.class)
})
@Entity
public class StoredProc implements Serializable {

  private Integer returnCode;
  private String message;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "return_code", nullable = false, unique = true)
  public Integer getReturnCode() {
    return returnCode;
  }
  public void setReturnCode(Integer returnCode) {
    this.returnCode = returnCode;
  }

  @Column(name = "message")
  public String getMessage() {
    return message;
  }
  public void setMessage(String message) {
    this.message = message;
  }

}

Note the slight difference in the way I call my stored procs--I don't use the assignment style "{ ? = call my_function(?, ?) }" but instead use a straight "{ call my_function(:param1, :param2) }". In my code, I define these SPs with @NamedNativeQuery annotations and wrap them all in a @NamedNativeQueries annotation (plural!) and stick it on my StoredProc class. That way everything is all in the right place... nice and tidy.

When I want to call one of these from code, it's dead-simple:

(StoredProc) getSession().getNamedQuery("myFunction")
                         .setParameter("param1", value)
                         .setParameter("param2", value2)
                         .uniqueResult();

Nice and simple. And if the SP throws any errors, I have them in the return message that I can then handle however I like (auto-email a bug report, send a message to the UI, etc.)