Postgresql 11:存储过程调用错误 - 要调用过程，请使用 CALL、Java [英] Postgresql 11: Stored Procedure call error - To call a procedure, use CALL, Java

问题描述

为了交易目的，我将 PGsql 函数转换为程序 (v11).但是在从 Java 代码调用该过程时，Java 代码抛出以下异常:

2021-01-10 21:52:56,604 WARN [ForkJoinPool.commonPool-worker-1] o.h.e.j.spi.SqlExceptionHelper(144) - SQL 错误:0，SQLState:428092021-01-10 21:52:56,605 错误 [ForkJoinPool.commonPool-worker-1] o.h.e.j.spi.SqlExceptionHelper(146) - 错误:some_procedure_name(字符变化，字符变化)是一个过程提示:要调用过程，请使用 CALL.职位:15

我使用的是 Spring Data JPA 的 @Procedure(name = "some_procedure_name")，并且这个过程在实体类中被声明为一个 @NamedStoredProcedure.问题是，这个注解还是像 PG 11 版之前一样，以 Function 方式调用.

这方面的任何帮助.

解决方案

在 Postgresql 11 之后，PostgreSQL JDBC 驱动团队在 Postgresql 中引入了 ENUM 名称 EscapeSyntaxCallMode驱动程序版本 42.2.16.所以我们可以在创建数据库连接或 DataSource 对象时使用这个枚举.这个枚举有 3 种类型的值:

1. "func"- 当我们总是想调用函数时设置它.
2. "调用"- 当我们总是想调用程序时设置它.
3. "callIfNoReturn"- 它检查调用函数/过程中的返回类型，如果返回类型存在 postgres 将其视为函数并将其称为函数方式.否则它称之为程序方式.所以在我的项目中，我使用了这个callIfNoReturn"，因为我想让 postgres 自动检测我是在调用函数还是过程.

因此，要解决此问题，您只需执行以下步骤:

1. 在 pom.xml 或 gradle 中将您的 PostgreSQL JDBC 驱动程序版本从任何旧版本升级到 42.2.16 或更高版本.

   <依赖><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.2.16</version></依赖>

2. 当然，你必须在你的机器上安装 PostgreSQL 服务器版本 >= 11 才能创建一个过程.

3. 如果您使用的是 Spring，那么在创建数据源对象时，您需要在 "jdbcUrl" 中附加 escapeSyntaxCallMode 作为查询字符串，如下所示:

   <属性名称=jdbcUrl"；value=jdbc:postgresql://localhost:5432/dev_db?escapeSyntaxCallMode=${cibase.db.app.procedureCallPolicy}"/><属性名称=用户名"值=${cibase.db.app.user}"/><属性名称=密码"值=${cibase.db.app.password}"/></bean>

   ?escapeSyntaxCallMode=${cibase.db.app.procedureCallPolicy}:这里我从属性文件中选择了枚举值，但是你可以直接在"func"/中输入任何枚举值根据您的要求调用"/callIfNoReturn".

现在你运行你的代码，它会正常工作.

注意:无论您是使用纯JDBC代码还是Spring Data Jpa中的@Procedure，您都不需要更改过程调用方式.

更多详情请点击此链接https://github.com/pgjdbc/pgjdbc>

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.

解决方案

After Postgresql 11, PostgreSQL JDBC driver team has introduced a ENUM name EscapeSyntaxCallMode in Postgresql driver version 42.2.16. So 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 postgres 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 postgres 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

这篇关于Postgresql 11:存储过程调用错误 - 要调用过程，请使用 CALL、Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！