英文:
How to fix SQLState: 42809 when calling a procedure of postgres db from spring boot application
问题
我正在从我的 Spring Boot 应用程序调用一个过程，数据库是 Postgres 11。现在返回的 SQLState 是：42809。有人能帮我找到原因吗？
我的应用程序代码：
Session session = entityManager.unwrap(Session.class);
Integer commentCount = session.doReturningWork(connection -> {
try (CallableStatement function = connection.prepareCall(
"{ ? = CALL sp_profile_creation_default(?,?,?) }")) {
function.registerOutParameter(1, Types.INTEGER);
function.setInt(2, dbUser.getId().intValue());
function.setInt(3, dbUser.getId().intValue());
function.setString(4, dbUser.getNameNick());
function.execute();
return function.getInt(1);
}
});
错误日志输出为：
2020-04-08 15:34:01.045 WARN 32324 --- [nio-8082-exec-1] o.h.e.j.s.SqlExceptionHelper : SQL Error: 0, SQLState: 42809
2020-04-08 15:34:01.046 ERROR 32324 --- [nio-8082-exec-1] o.h.e.j.s.SqlExceptionHelper : ERROR: sp_profile_creation_default(integer, integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
2020-04-08 15:34:01.051 ERROR 32324 --- [nio-8082-exec-1] c.c.a.a.v.e.RestEndpointExceptionHandler : error executing work
这个过程在 postgres 命令中执行正常：
call public.sp_profile_creation_default(123, 0, 'sontest12112', 0);
并且按预期执行。
该过程的定义脚本：
CREATE PROCEDURE public.sp_profile_creation_default(
IN p_user_id INT,
IN p_parent_event_id INT,
IN p_name_nick core.profile.name%TYPE,
INOUT p_default_profile_id INT
)
...
英文:
I am calling a procedure from my spring boot application, the database is Postgres 11.
It's now returning SQLState: 42809. Could anyone help me find the cause?
Code of my application:
Session session = entityManager.unwrap(Session.class);
Integer commentCount = session.doReturningWork(
connection -> {
try (CallableStatement function = connection
.prepareCall(
"{ ? = CALL sp_profile_creation_default(?,?,?) }" )) {
function.registerOutParameter( 1, Types.INTEGER );
function.setInt( 2, dbUser.getId().intValue());
function.setInt( 3, dbUser.getId().intValue());
function.setString( 4, dbUser.getNameNick());
function.execute();
return function.getInt( 1 );
}
} );
The error log output is:
2020-04-08 15:34:01.045 WARN 32324 --- [nio-8082-exec-1] o.h.e.j.s.SqlExceptionHelper : SQL Error: 0, SQLState: 42809
2020-04-08 15:34:01.046 ERROR 32324 --- [nio-8082-exec-1] o.h.e.j.s.SqlExceptionHelper : ERROR: sp_profile_creation_default(integer, integer, character varying) is a procedure
Hint: To call a procedure, use CALL.
Position: 15
2020-04-08 15:34:01.051 ERROR 32324 --- [nio-8082-exec-1] c.c.a.a.v.e.RestEndpointExceptionHandler : error executing work
The procedure is working file, I tried to execute it in postgres command:
call public.sp_profile_creation_default(123, 0, 'sontest12112',0);
and it executed as expected.
The procedure definition script:
CREATE PROCEDURE public.sp_profile_creation_default(
IN p_user_id INT,
IN p_parent_event_id INT,
IN p_name_nick core.profile.name%TYPE,
INOUT p_default_profile_id INT
)
...
专注分享java语言的经验与见解，让所有开发者获益！
评论