Hi,
I am trying to debug an issue when using StoredProcedureQuery on a PostgreSQL database.
Say you have a sored procedure defined as follow:
CREATE OR REPLACE PROCEDURE public.testproc(IN anint bigint, IN astring character varying)
LANGUAGE plpgsql
AS $procedure$
begin
raise notice 'you called testproc !';
end; $procedure$
;
and a jdbc url
jdbc:postgresql://database:5432/postgres?escapeSyntaxCallMode=callIfNoReturn
Using plain jdbc simply works:
Connection conn = DriverManager.getConnection(url, props);
CallableStatement s = conn.prepareCall("{call testproc(?, ?)}");
s.setInt(1, 2984);
s.setString(2, "CSLMF14");
s.execute();
while JPA StoredProcedureQuery keeps on calling the procedure with SELECT statement instead of CALL :
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();
here's the relevant stacktrace:
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]
If I remove the connection parameter "escapeSyntaxCallMode" then I get the same error on plain jdbc too.
here's the persistence.xml I am using :
<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>
Am I missing something in the configuration ? It really looks like the driver isn't properly loaded or the connection url is overridden.
TIA
] 
Search
Help
Register
Login
Home
