0

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);
   ... ... ...
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
user3593086
  • 43
  • 1
  • 6
  • Have you looked at docs here [Calling functions/procedures](https://jdbc.postgresql.org/documentation/callproc/): *Example 6.5. Calling a stored procedure* – Adrian Klaver Apr 10 '23 at 15:54
  • I'd like to know if it is possible to use spring SimpleJdbcCall to invoke postgres SP. If not, I will switch to use JDBC PreparedStatement. – user3593086 Apr 10 '23 at 17:42
  • Try escapeSyntaxCallMode=callIfNoReturn, see [procedure-call-error](https://stackoverflow.com/questions/65696904/postgresql-11-stored-procedure-call-error-to-call-a-procedure-use-call-java) – PetrS Aug 28 '23 at 14:25

0 Answers0