Can I call Postgres stored procedure utilizing Spring SimpleJdbcCall

575 Views Asked by At

I got below error when I call postgres stored procedure by utilizing spring's SimpleJdbcCall.

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; 
bad SQL grammar [{call myProc(?, ?)}]; 
nested exception is org.postgresql.util.PSQLException: ERROR: myProc(numeric, character varying) is a procedure
  Hint: To call a procedure, use CALL.

It seems to me that postgres jdbc driver interprets the procedure as funtion. Does anyone have any idea?

Followings are SP and java code (jdk8 and tomcat are used)

stored procedure

CREATE OR REPLACE PROCEDURE myProc(
     INOUT p_errcode    numeric,
     INOUT p_errmessage character varying
)

db url

jdbc:postgresql://host:5432/sid?escapeSyntaxCallMode=call

maven dependency

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

java code snippets

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(template)
   .withProcedureName(procName)
   .withoutProcedureColumnMetaDataAccess();

simpleJdbcCall.setAccessCallParameterMetaData(false);
simpleJdbcCall.declareParameters(
   new SqlInOutParameter("p_errcode", Types.NUMERIC),
   new SqlInOutParameter("p_errmessage", Types.VARCHAR)
);
                     
SqlParameterSource parameters = new MapSqlParameterSource()
   .addValue("p_errcode ", 0)
   .addValue("p_errmessage ", "error");

try {
   Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(parameters);
   ... ... ...
0

There are 0 best solutions below