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, Aug 25 '16 at 18:19

score 1 · Answer 1 · answered Aug 25 '16 at 15:22

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

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

Is it possible to call firebird select procedure using JPA?

Yes it is. You can use EntityManager#createNativeQuery instead.

Why "Named query not found" if there is Query query = em.createNamedQuery(q, CallProcedureResult.class); in method?

The first parameter of EntityManager#createNamedQuery must be a NamedQuery and it is not, it is an actual query in your case.

Unrelated to your problem, using named parameters in native queries is supported by Hibernate but it cannot be portably used across JPA implementations (https://stackoverflow.com/a/28829942/5078385).

edited May 23 '17 at 10:33

Community

1
1

answered Aug 25 '16 at 15:40

acm

2,086
3
16
34

If I use `em.createNativeQuery(q, CallProcedureResult.class);` then `query.setParameter("IN$SHARE", parametersForAddDealsProc.get("IN$SHARE") )` throws `Cannot resolve query parameter 'IN$SHARE' less... (Ctrl+F1) This inspection checks for the following Persistence model problems: Named query not found Query parameter not found` – May12 Aug 26 '16 at 06:28
This was Idea problem. – May12 Aug 26 '16 at 06:39
Now i receive in log: `Hibernate: select a.dol as id, a.out$error_code as error_code from ADD_LINE (?, ?, ?, ?, ?) a`. Where are parameters? – May12 Aug 26 '16 at 06:41
Is your query working then? If that is so, and you want to be able to see your parameters value in the log, maybe you are looking for the following answer: http://stackoverflow.com/a/1713464/5078385. – acm Aug 26 '16 at 10:01

How to call firebird selectable stored procedure using JPA?

2 Answers2