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.

Mark Rotteveel · Accepted Answer · 2021-11-13 13:00:42Z

22

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:

"func" - set this when we always want to call functions.
"call" - set this when we always want to call Procedures.
"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:

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>

And of course, you must have PostgreSQL Server version >= 11 installed in your machine to create a procedure.
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

edited Nov 13, 2021 at 13:00

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jan 13, 2021 at 6:45

Abhishek Singh

1,5272 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belayer Over a year ago

If the answer resolved your issue then accept it. Please do not leave it as "unanswered". Accepting an answer helps later questioners that may have the same issue.

GC-Java Over a year ago

This was absolute the situation we've come across, and I can confirm that the solution works properly as described.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related