CODEWITHSUNDEEP
javapostgresqlstored-proceduresspring-data-jpapostgresql-11
Abhishek Singh
Abhishek Singh

Reputation: 1517

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

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.

Upvotes: 7

Views: 9203

Answers (1)

Abhishek Singh
Abhishek Singh

Reputation: 1517

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

Upvotes: 22

Related Questions