Calling H2 stored procedure results in 'JdbcSQLException: Function alias "PROCEDURE" not found'
1,313 views
Skip to first unread message
Szymon Kuryło
May 1, 2016, 1:47:02 PM5/1/16
to H2 Database
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-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?
Steve McLeod
May 2, 2016, 3:57:02 AM5/2/16
to H2 Database
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()
Reply all
Reply to author
Forward
0 new messages