Postgresql 11: Stored Procedure call error - To call a procedure, use CALL, Java

Question

I converted a PGsql Function to Procedure (v11) for transaction purpose. But while calling that procedure from Java code, java code is throwing below exception:

2021-01-10 21:52:56,604 WARN  [ForkJoinPool.commonPool-worker-1] o.h.e.j.spi.SqlExceptionHelper(144) - SQL Error: 0, SQLState: 42809
2021-01-10 21:52:56,605 ERROR [ForkJoinPool.commonPool-worker-1] o.h.e.j.spi.SqlExceptionHelper(146) - ERROR: some_procedure_name(character varying, character varying) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15

I am using Spring Data JPA's @Procedure(name = "some_procedure_name"), and this procedure is declared as a @NamedStoredProcedure in entity class. The problem is, this annotation is still calling it as a Function way like before PG version 11.

Any help in this.

Answer 1

After PostgreSQL 11, PostgreSQL JDBC driver team has introduced a ENUM name EscapeSyntaxCallMode in PostgreSQL driver version 42.2.16. We can use this enum while creating a database connection or a DataSource object. This enum has 3 types of values:

1. "func" - set this when we always want to call functions.
2. "call" - set this when we always want to call Procedures.
3. "callIfNoReturn" - It checks for the return type in calling function/procedure, if return type exists PostgreSQL considers it as a function and calls it as a function way. Otherwise it calls it as procedure way. So in my project I used this "callIfNoReturn", as I wanted PostgreSQL to auto detect whether I am calling function or procedure.

So, to fix this issue, you only need to follow these below steps:

1. Upgrade your PostgreSQL JDBC driver version from any older version to 42.2.16 or greater in pom.xml or gradle.

   <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
       <version>42.2.16</version>
   </dependency>
   

2. And of course, you must have PostgreSQL Server version >= 11 installed in your machine to create a procedure.

3. If you are using Spring, then while creating Data Source object, you need to append escapeSyntaxCallMode as a query string in "jdbcUrl" like this:

   <bean id="dataSource" parent="com.zaxxer.hikari.HikariDataSource">
       <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/dev_db?escapeSyntaxCallMode=${cibase.db.app.procedureCallPolicy}"/>
       <property name="username" value="${cibase.db.app.user}"/>
       <property name="password" value="${cibase.db.app.password}"/>
   </bean>
   

   ?escapeSyntaxCallMode=${cibase.db.app.procedureCallPolicy}: here I picked enum value from property file, but you can directly type any enum value among "func"/"call"/"callIfNoReturn" according to your requirement.

Now you run your code, and it will work properly.

Note: You don't need to change anything in the way of procedure call whether you are using plain JDBC code or @Procedure in Spring Data Jpa.

for more detail please follow this link https://github.com/pgjdbc/pgjdbc