[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
[eclipselink-users] Issues calling PostgreSQL stored procedures using EclipseLink
|
Hi all,
I have an issue calling a PG stored procedure. The stored procedure (any will do actually) can be something as simple as
---
CREATE OR REPLACE PROCEDURE testproc(IN anint bigint, IN astring character varying)
LANGUAGE plpgsql
AS $procedure$
begin
raise notice 'you called testproc !';
end; $procedure$
;
---
Now, in order to force PostgreSQL jdbc driver to use CALL rather than SELECT one has to use a (fairly) recent driver, a server version >= 11 (easy) and add a parameter in the url, namely escapeSyntaxCallMode=call/callIfNoReturn, like in: "jdbc:postgresql://localhost:5432/postgres?escapeSyntaxCallMode=callIfNoReturn"
If I test via simple jdbc everything woks flawlessly:
---
public static void main(String[] args){
String url = "";
Properties props = new Properties();
props.setProperty("user", "myuser");
props.setProperty("password", "mypass");
System.out.print("Executing stored procedure...");
try {
Connection conn = DriverManager.getConnection(url, props);
CallableStatement s = conn.prepareCall("{call testproc(?, ?)}");
s.setInt(1, 2984);
s.setString(2, "CSLMF14");
s.execute();
System.out.println("success ! ");
} catch (Exception e){
System.out.println("failed ! ");
e.printStackTrace();
}
}
---
But when I try to use EclipseLink JPA the call to testproc is always translated to SELECT * FROM testproc(?,?).
Here's my test case:
-- persistence unit
<persistence-unit name="manualPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<properties>
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://database:5432/postgres?escapeSyntaxCallMode=callIfNoReturn" />
<property name="javax.persistence.jdbc.user" value="myuser" />
<property name="javax.persistence.jdbc.password" value="mypass" />
</properties>
</persistence-unit>
-- java snippet
utx.begin();
EntityManagerFactory emf = Persistence.createEntityManagerFactory("manualPU");
EntityManager em = emf.createEntityManager();
em.joinTransaction();
StoredProcedureQuery query = em.createStoredProcedureQuery("testproc")
.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
.registerStoredProcedureParameter(2, String.class, ParameterMode.IN).setParameter(1, 2984)
.setParameter(2, "CSLMF14");
query.executeUpdate();
utx.commit();
No matter the way I load the jdbc driver, this always results thwrowing an exception :
...
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.11.v20220804-52dea2a3c0): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: testproc(integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
Error Code: 0
Call: SELECT * FROM testproc(?, ?)
bind => [2 parameters bound]
...
I am 100% sure that the parameter callIfNoReturn is working because if I remove it from the jdbc test I get the exact same exception as with JPA:
---
...
String url = "";
...
Executing stored procedure...failed !
org.postgresql.util.PSQLException: ERROR: testproc(integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
...
Using a native query is not an option as I'd rather be as db agnostic as (humanly) possible.
At the moment I am using Apache TomEE Plume 8.4 as my test bench and everything has been working flawlessly so far, but I am open to evaluate other test benches provided they exist as a docker image that can be configured as easily as TomEE.
Can anyone point me to a solution or work-around ?
TIA
Marco F.