[eclipselink-users] Issues calling PostgreSQL stored procedures using Ec

[Marco Ferretti <marco.ferretti@xxxxxxxxxxx>]

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.