I'm pretty certain that this is an EclipseLink bug. It is generating the
wrong syntax to call a stored procedure (actually a user-defined function)
in H2.
The syntax is
CALL logProc()
and not
EXECUTE PROCEDURE logProc()
On Sunday, 1 May 2016 19:47:02 UTC+2, Szymon Kuryło wrote:
>
> Table definition (src/test/resources/db.sql):
> create table if not exists users(
> user_id identity primary key,
> user_name varchar
> );
>
> insert into users(user_name) values('John Doe');
> insert into users(user_name) values('Jane Doe');
>
> create alias if not exists logProc as $$
> void loglog() {
> System.out.println("Hello world!");
> }
> $$;
>
> Persistence.xml:
> <persistence-unit name="h2">
> <class>com.pany.User</class>
> <exclude-unlisted-classes>true</exclude-unlisted-classes>
> <properties>
> <property name="javax.persistence.jdbc.driver"
> value="org.h2.Driver" />
> <property name="javax.persistence.jdbc.url"
>
> value="jdbc:h2:mem:test;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;INIT=runscript
>
> from 'src/test/resources/db.sql'" />
> <property name="eclipselink.jdbc.timeout" value="30" />
> <property name="eclipselink.target-database"
> value="org.eclipse.persistence.platform.database.H2Platform" />
> </properties>
> </persistence-unit>
>
> User class:
> package com.pany;
> import javax.persistence.*;
>
> @NamedStoredProcedureQuery(
> name = "log",
> procedureName = "logProc"
> )
> @Entity
> @Table(name = "users")
> public class User {
>
> @Id
> @GeneratedValue(strategy = GenerationType.IDENTITY)
> @Column(name = "user_id")
> public int userId;
>
> @Column(name = "user_name")
> public String userName;
> }
>
> Executing
> entityManager.createNamedStoredProcedureQuery("log").execute();
> or
> entityManager.createNamedQuery("log").getResultList();
> results in
>
> javax.persistence.PersistenceException: Exception [EclipseLink-4002]
> (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: org.h2.jdbc.JdbcSQLException: Function alias
> "PROCEDURE" not found; SQL statement:
> EXECUTE PROCEDURE logProc() [90077-191]
> Error Code: 90077
> Call: EXECUTE PROCEDURE logProc()
> Query: ResultSetMappingQuery(name="log" )
>
> at
> org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377)
> at
> org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
> at
> org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:316)
> at com.pany.UserDao.log(UserDao.java:22)
> at com.pany.MainTest.test(MainTest.java:16)
> 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:497)
> 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:117)
> 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:497)
> at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
> Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 2.5.0.v20130507-3faac2b):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: org.h2.jdbc.JdbcSQLException: Function alias
> "PROCEDURE" not found; SQL statement:
> EXECUTE PROCEDURE logProc() [90077-191]
> Error Code: 90077
> Call: EXECUTE PROCEDURE logProc()
> Query: ResultSetMappingQuery(name="log" )
> at
> org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1995)
> at
> org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.execute(DatasourceCallQueryMechanism.java:220)
> at
> org.eclipse.persistence.queries.ResultSetMappingQuery.executeDatabaseQuery(ResultSetMappingQuery.java:291)
> at
> org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
> at
> org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
> at
> org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
> at
> org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
> ... 29 more
> Caused by: org.h2.jdbc.JdbcSQLException: Function alias "PROCEDURE" not
> found; SQL statement:
> EXECUTE PROCEDURE logProc() [90077-191]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> at org.h2.message.DbException.get(DbException.java:179)
> at org.h2.message.DbException.get(DbException.java:155)
> at org.h2.command.Parser.parseExecute(Parser.java:1634)
> at org.h2.command.Parser.parsePrepared(Parser.java:387)
> at org.h2.command.Parser.parse(Parser.java:315)
> at org.h2.command.Parser.parse(Parser.java:287)
> at org.h2.command.Parser.prepareCommand(Parser.java:252)
> at org.h2.engine.Session.prepareLocal(Session.java:560)
> at org.h2.engine.Session.prepareCommand(Session.java:501)
> at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
> at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
> at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1551)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1500)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:619)
> ... 43 more
>
> I'm using example from
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures#Using_.40NamedStoredProcedureQuery_to_define_a_stored_procedure
> .
> Eclipselink version: 2.5.0 (same happens with the newest 2.6.3-M1)
> Is there something wrong with my code, or is H2 database not fully
> supported?
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
