Hello,
I have problems with calling PostgreSQL functions with StoredProcedureQuery.
My persistence.xml file:
<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http//www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http//xmlns.jcp.org/xml/ns/persistence
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd"
version="2.2">
<persistence-unit name="mydbPU">
<exclude-unlisted-classes />
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql//myserver:5432/mydb" />
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
<property name="javax.persistence.jdbc.user" value="myuser" />
<property name="javax.persistence.jdbc.password" value="mypassword" />
<property name="datanucleus.mapping.Schema" value="public" />
<property name="datanucleus.identifier.case" value="MixedCase" />
</properties>
</persistence-unit>
</persistence>
PostgreSQL function:
CREATE OR REPLACE FUNCTION public.mysum(v1 integer, v2 integer, OUT vout integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
SELECT v1+v2 INTO vout;
END;
$function$
Code:
EntityManagerFactory emf = Persistence.createEntityManagerFactory("mydbPU");
EntityManager em = emf.createEntityManager();
StoredProcedureQuery spq = em.createStoredProcedureQuery("mysum");
spq.registerStoredProcedureParameter("v1", Integer.class, ParameterMode.IN);
spq.registerStoredProcedureParameter("v2", Integer.class, ParameterMode.IN);
spq.registerStoredProcedureParameter("vout", Integer.class, ParameterMode.OUT);
spq.setParameter("v1", 1); // exception here
spq.setParameter("v2", 2);
spq.execute();
System.out.println(spq.getOutputParameterValue("vout"));
em.close();
emf.close();
I get an exception:
Exception in thread "main" This RDBMS does not support stored procedures!
org.datanucleus.exceptions.NucleusUserException: This RDBMS does not support stored procedures!
at org.datanucleus.store.rdbms.query.StoredProcedureQuery.compileInternal(StoredProcedureQuery.java:97)
at org.datanucleus.store.query.Query.setImplicitParameter(Query.java:975)
at org.datanucleus.api.jpa.JPAQuery.setParameter(JPAQuery.java:548)
at org.datanucleus.api.jpa.JPAStoredProcedureQuery.setParameter(JPAStoredProcedureQuery.java:85)
at org.datanucleus.api.jpa.JPAStoredProcedureQuery.setParameter(JPAStoredProcedureQuery.java:41)
at vma.demo.FunctionTest.main(FunctionTest.java:19)
I also tried creating an empty function without parameters:
CREATE OR REPLACE FUNCTION public.my_empty_func()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
END
$function$
The exception is different in this case:
Exception in thread "main" Error encountered when extracting results for SQL query "my_empty_func"
org.datanucleus.exceptions.NucleusDataStoreException: Error encountered when extracting results for SQL query "my_empty_func"
at org.datanucleus.store.rdbms.query.StoredProcedureQuery.performExecute(StoredProcedureQuery.java:594)
at org.datanucleus.store.rdbms.query.StoredProcedureQuery.executeQuery(StoredProcedureQuery.java:143)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1855)
at org.datanucleus.store.query.Query.execute(Query.java:1837)
at org.datanucleus.api.jpa.JPAStoredProcedureQuery.execute(JPAStoredProcedureQuery.java:209)
at vma.demo.FunctionTest.main(FunctionTest.java:24)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "CALL"
Позиция: 1
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:78)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
at org.datanucleus.store.rdbms.query.StoredProcedureQuery.performExecute(StoredProcedureQuery.java:444)
... 5 more
Not sure if this is relevant but I also get these warnings:
1507:20,552 (main) WARN [DataNucleus.MetaData] - MetaData Parser encountered an error in file "file:/C/Users/Administrator/eclipse-workspace/demo/target/classes/META-INF/persistence.xml" at line 6, column 16 : cvc-complex-type.3.1: Value '2.2' of attribute 'version' of element 'persistence' is not valid with respect to the corresponding attribute use. Attribute 'version' has a fixed value of '2.1'. - Please check your specification of DTD/XSD and the validity of the MetaData XML header that you have specified.
15:0721,303 (main) WARN [DataNucleus.Datastore.Schema] - You have specified the default schema as public but for this datastore this has been changed to "public". This is likely due to missing quote characters, or the datastore storing things in a different case
I use PostgreSQL 9.6.7 in Debian 9.4. Here're the artifacts I used for running the code:
