0

After using this code

   CallableStatement callableStatement = null;
    
   String sqlCall = "{call my_schema.my_procedure(?,?,?,?,?,?)}";

   callableStatement = connection.prepareCall(sqlCall);

when I print callableStetement to check, it suprisingly turns into

"select * from my_schema.my_procedure(?,?,?,?,?,?) as result".

But why?

When I try to run this SQL directly in DBMS, it says "ERROR: my_shema.my_procedure(integer, unknown, unknown, unknown, unknown, unknown) is a procedure Hint: To call a procedure, use CALL.";

For the first parameter I`m supposed to use registerOutParameter method.

How to run the procedure using JDBC? May be there`s something related to the database/procedure settings?

For JDBC I use 42.2.5 PostgreSQL Driver.

The version of PostgreSQL is 11.5

NEON
  • 23
  • 4
  • Currently you can't use the JDBC `{call ...}` syntax with a CallableStatement. You need to use a `PreparedStatement` with a "plain" SQL statement: `call my_procedure(...)` –  Nov 09 '21 at 17:43
  • a-horse-with-no-name, thanks, but I need to register out parameter (id) from the DB. So how to do that? As I understand I can`t do it using "plain" statements. – NEON Nov 09 '21 at 17:48
  • Historically, the PostgreSQL JDBC driver uses `call` to execute stored functions. Stored procedures are a recent addition to PostgreSQL. You need to configure it (`escapeSyntaxCallMode`, value `callIfNoReturn`), see https://jdbc.postgresql.org/documentation/head/callproc.html#call-procedure-example. – Mark Rotteveel Nov 09 '21 at 18:22
  • If you want to return something, then why not use a function to begin with? –  Nov 09 '21 at 18:37
  • a_horse_with_no_name, there`s no real need to return anything. – NEON Nov 09 '21 at 18:43

0 Answers0