CreateNamedStoredProcedureQuery cannot get out parameter

Question

I'm trying to execute a stored procedure via JPA. I have the stored procedure set up via an annotation:

@NamedStoredProcedureQuery(
    name = 'getNextKey',
    procedureName = 'usp_getNextKey',
    parameters = [
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name='table_name'),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name='field_name'),
            @StoredProcedureParameter(mode = ParameterMode.OUT, type = Integer.class, name = 'nextValue')
    ]
)

The parameter exists in the database, and I am trying to call the procedure as follows:

    StoredProcedureQuery getNextKeyQuery = this.entityManager.createNamedStoredProcedureQuery("getNextKey")

    getNextKeyQuery.setParameter('table_name', "SESSION_CONTROL")
    getNextKeyQuery.setParameter('field_name', "session_key")

    getNextKeyQuery.execute()
    Object sessionKey = getNextKeyQuery.getOutputParameterValue('nextValue')

When I run the code above, I get the following error:

org.hibernate.exception.GenericJDBCException: Unable to extract OUT/INOUT parameter value
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.extract(AbstractParameterRegistrationImpl.java:417)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:46)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:273)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:374)
    at com.sun.proxy.$Proxy118.getOutputParameterValue(Unknown Source)
    at javax.persistence.StoredProcedureQuery$getOutputParameterValue$1.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at com.fhlbdm.collateral.cukes.steps.LoanListingSteps.aNewLoanListingFileIsSubmitted(LoanListingSteps.groovy:54)
    at ✽.a new loan listing file is submitted(com/fhlbdm/collateral/cukes/light-listing-loan-to-rai.feature:6)
Caused by: java.sql.SQLException: Statement closed.
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:110)
    at com.sun.proxy.$Proxy119.getInt(Unknown Source)
    at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:67)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:90)
    at org.hibernate.type.AbstractStandardBasicType.extract(AbstractStandardBasicType.java:341)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.extract(AbstractParameterRegistrationImpl.java:400)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:46)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:273)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:374)
    at com.sun.proxy.$Proxy118.getOutputParameterValue(Unknown Source)
    at javax.persistence.StoredProcedureQuery$getOutputParameterValue$1.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at com.fhlbdm.collateral.cukes.steps.LoanListingSteps.aNewLoanListingFileIsSubmitted(LoanListingSteps.groovy:54)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at cucumber.runtime.Utils$1.call(Utils.java:32)
    at cucumber.runtime.Timeout.timeout(Timeout.java:16)
    at cucumber.runtime.Utils.invoke(Utils.java:26)
    at cucumber.runtime.java.JavaStepDefinition.execute(JavaStepDefinition.java:37)
    at cucumber.runtime.StepDefinitionMatch.runStep(StepDefinitionMatch.java:40)
    at cucumber.api.TestStep.executeStep(TestStep.java:102)
    at cucumber.api.TestStep.run(TestStep.java:83)
    at cucumber.api.TestCase.run(TestCase.java:58)
    at cucumber.runner.Runner.runPickle(Runner.java:80)
    at cucumber.runtime.junit.PickleRunners$NoStepDescriptions.run(PickleRunners.java:140)
    at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:68)
    at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:23)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at cucumber.runtime.junit.FeatureRunner.run(FeatureRunner.java:73)
    at cucumber.api.junit.Cucumber.runChild(Cucumber.java:117)
    at cucumber.api.junit.Cucumber.runChild(Cucumber.java:55)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at cucumber.api.junit.Cucumber$1.evaluate(Cucumber.java:126)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

I'm stuck where it says caused by Statement Closed. I've tried wrapping it in a transaction, but the entitymanager is shared, and I cannot create a transaction there. When I wrap it in a transaction template, I get the same error as above.

Here is the configuration that is being used:

logging.level.org.hibernate.SQL=INFO
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
spring.jpa.properties.hibernate.type=trace
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

spring.jpa.show-sql=true
spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=create

#dburl=jdbc:hsqldb:mem:collateral-staging
dburl=jdbc:hsqldb:file:/Users/${user.name}/hsql/processes/staging

datasource.collateral.staging.driverClassName=org.hsqldb.jdbcDriver
datasource.collateral.staging.url=${dburl}
datasource.collateral.staging.username=SA
datasource.collateral.staging.password=

score 4 · Answer 1 · answered Dec 21 '18 at 15:16

@tfitzger The problem you have is releated with the transaction settings not been recognized by the proxy of your storedprocedure definition. You should use the @Transactional (JPA or Spring as appropriate) notation to solve the problem of your Statements been closed before you are able to extract the value of your output parameter. In this way both sentences (getNextKeyQuery.execute() and getNextKeyQuery.getOutputParameterValue('nextValue') ) will share the same database transaction and behave in the expected way.

Also don't forget to read the StoredProcedureQuery specification for your use case.

https://javaee.github.io/javaee-spec/javadocs/javax/persistence/StoredProcedureQuery.html

score 1 · Answer 2 · edited Mar 25 '20 at 10:18

1

I encounter the same issue and fixed it by removing the execute() line. getOutputParameterValue will execute the SP so we don't need to call execute().

edited Mar 25 '20 at 10:18

Alex Meuer

1,621
3
26
37

answered Mar 25 '20 at 09:34

Zhi Yuan Yan

11
1

Do you know if there is any documentation on the fact that getOutputParameterValue will execute the SP ? That might be the answer to my question here: https://stackoverflow.com/questions/63204077/get-output-parameter-from-stored-procedure-without-calling-execute – Phreneticus Aug 02 '20 at 07:19

CreateNamedStoredProcedureQuery cannot get out parameter

2 Answers2