How to call firebird selectable stored procedure using JPA?

Question

I'm trying to call a Firebird selectable procedure using JPA. The method looks like:

 public void addLineToBrokerReport(Map<String, Object> parametersForAddDealsProc) {

        MapUtils.debugPrint(System.out, "Parameters for procedure", parametersForAddDealsProc);

        String q = "select a.dol as id, a.out$error_code as error_code " +
            "from ADD_LINE (:IN$DOC," +
            ":IN$SHARE, " +
            ":IN$B_ACC, " +
            ":IN$S_ACC, " +
            ":IN$COMMENT) a";

      Query query = em.createNamedQuery(q, CallProcedureResult.class);
       for (Map.Entry<String, Object> entry : parametersForAddDealsProc.entrySet()) {
           query.setParameter("\"" + entry.getKey()+ "\"", entry.getValue());
        }
        CallProcedureResult result = (CallProcedureResult) query.getSingleResult();
        LOG.info("Error_code = " + result.getError_code()  + " dol = " + result.getId());

    }

After calling it is return the next error:

java.lang.IllegalArgumentException: Named query not found: select a.dol as id, a.out$error_code as error_code from P_TDA_ADD_LINE_TO_BROKER_REP (:IN$DOC,:IN$SHARE, :IN$B_ACC, :IN$S_ACC, :IN$COMMENT) a

at org.hibernate.ejb.AbstractEntityManagerImpl.createNamedQuery(AbstractEntityManagerImpl.java:665) 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.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344) at com.sun.proxy.$Proxy33.createNamedQuery(Unknown Source) at com.comp.app.TradesUpload.TradesUpload.addLineToBrokerReport(TradesUpload.java:454) at com.comp.app.TradesUpload.TradesUploadTest.addLineToBrokerReport(TradesUploadTest.java:69) 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) 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 org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:119) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74) 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Is it possible to call firebird select procedure using JPA?
Why "Named query not found" if there is Query query = em.createNamedQuery(q, CallProcedureResult.class); in method?

Note that selectable stored procedures behave like a normal select query, so you don't need to handle them like stored procedures; using CallProcedureResult is likely unnecessary. — Mark Rotteveel

Neil Stockton Neil Stockton · Accepted Answer · 2016-08-25T15:22:02

em.createNamedQuery first argument is supposed to be the NAME of the NamedQuery (not the JPQL). The NamedQuery itself you would have defined in an annotation or XML, and specified the NAME against it. Also if you mean to invoke SQL direct (rather than JPQL), you need a NamedNativeQuery which is for a native query.

JPA 2.1 has support for Stored Procedures; whether that would work for invoking Firebird's things I've no idea, but you could try it also

How to call firebird selectable stored procedure using JPA?

2 Answers