CODEWITHSUNDEEP
javasql-serverhibernateentitymanager
Neil Hill
Neil Hill

Reputation: 61

hibernate createStoredProcedureQuery execute throws callablestatement.getmoreresults

First time I've used Java to call an SP so this is probably going to be a simple issues I'm overlooking.

I've got a couple of ways i'm trying to do this and am getting errors on both.

em is an EntityManager that's declared higher up the class, i've inspected it during debugging and the database connection is correct.

The first is using a StoredProcedureQuery which throws the error;

javax.persistence.PersistenceException: org.hibernate.SQLGrammarException: Error calling CallableStatement.getMoreResults

try {
    StoredProcedureQuery query = em.createStoredProcedureQuery("usp_myProc");
    query.registerStoredProcedureParameter(0, Integer.class, ParameterMode.IN);
    query.setParameter(0, id);
    query.execute();
} 
catch (Exception ex) {
    log.error(ex.toString());
}

My second attempt was to try and use a native query (which sounded right when I read the documentation)

try {
    Query query1 = em.createNativeQuery("EXEC usp_myProc ?");
    query1.setParameter(1, id);
    Integer val = (Integer) query1.getSingleResult();
}
catch (Exception ex) {
    log.error(ex.toString());
}

The Procedure has been stripped down to a simple 'Select 1' to make sure that's not a problem.

PROCEDURE [usp_myProc]
-- Add the parameters for the stored procedure here
@id INT
AS
BEGIN
   SELECT 1
END

I've had a google around (which is where I get the Native Query code from) but couldn't get anything to return. Even just a stored procedure call that doesn't return a value would be a god result for me now.

Thanks for any help you can give.

Upvotes: 2

Views: 43768

Answers (2)

J2RE
J2RE

Reputation: 31

I was facing the same problem and I have used the method provided by the EntityManager as follows:

entityManager.createNativeQuery(
"CALL  storedProcedureName(:param1, :param2, :param3)")
.setParameter("param1", parameter1)
.setParameter("param2", parameter2)
.setParameter("param3", parameter3)
.getSingleResult();

In my case, I also have casted the getSingleResult() to String because I was expecting it as INOUT StoredProcedure's parameter and it worked as it should.

I hope it helps you as well.

Upvotes: 0

Reimeus
Reimeus

Reputation: 159844

StoredProcedureQuery indices start at 1 

query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
query.setParameter(1, id);

Upvotes: 7

Related Questions