We have a stored procedure in a PostgreSQL DB that takes multiple input and multiple out parameters. The procedure call from the PG Admin client works fine when we do the following,
call proc1(input1, input2, output1, output2)
However, if we try to make this call through a JDBC CallableStatement, we get the below error,
org.postgresql.util.PSQLException: This statement does not declare an OUT parameter. Use { ?= call ... } to declare one.
at org.postgresql.jdbc.PgCallableStatement.registerOutParameter(PgCallableStatement.java:205)
The PostgreSQL driver is "org.postgresql.Driver"
The Driver version is postgressql-42.2.5.jar
How do we make a call to PostgreSQL procedure that has multiple output parameters from JDBC?
Please find below the code snippet,
public static void main(String args[]) throws SQLException {
Connection conn = null;
try {
String url = "jdbc:postgresql://<<hostname>>:<<port>>/<<DB>>";
Class.forName("org.postgresql.Driver");
Properties props = new Properties();
props.setProperty("user", "<<user>>");
props.setProperty("password", "<<pass>>");
conn = DriverManager.getConnection(url, props);
CallableStatement cs = conn.prepareCall("call schema.proc1(?,?,?,?)");
cs.setString(1, "test");
cs.setInt(2, 1000);
cs.registerOutParameter(3, Types.INTEGER);
cs.registerOutParameter(4, Types.VARCHAR);
cs.execute();
} catch (Exception e) {
log.error(e);
} finally {
if (conn != null) {
conn.close();
}
}
}
Below is the sample version of the Procedure
Procedure proc1 is (input1 IN varchar2(10),
input2 IN number, output1 OUT number,
output2 OUT varchar2(10)) IS
BEGIN
output2 := input1;
output1 := input2;
END;
I'm testing the procedure call with multiple OUT parameter in JDBC with PostgreSQL 14.1 and driver 42.2.20.
Some care must be taken, as the ANSI call fails
cn.prepareCall("{call proc1(?,?,?,?)}")
with org.postgresql.util.PSQLException: ERROR: proc1(character varying, integer) is a procedure Hint: To call a procedure, use CALL. This may be connected with this answer
Similar fails also the PostgreSQL CALL cn.prepareCall("call proc1(?,?,?,?)") with
Caught: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "null". This suggest a problem with the nullinteger OUTparamater.
I finally get it with a bit hack defining the integer parameter as INOUT and passing zero.
Procedure
create or replace PROCEDURE proc1(input1 IN varchar(10), input2 IN integer, output1 INOUT integer, output2 OUT varchar(10))
LANGUAGE plpgsql
AS $$
BEGIN
output2 := input1;
output1 := input2;
END;
$$;
JDBC
// procedure call with two OUT parameter
stmt = cn.prepareCall("call proc1(?,?,?,?)")
stmt.setString(1,'x')
stmt.setInt(2,100)
stmt.setInt(3,0)
stmt.registerOutParameter(3,Types.INTEGER)
stmt.registerOutParameter(4,Types.VARCHAR)
stmt.execute()
println stmt.getInt(3)
println stmt.getString(4)
returns as expected
100
x