Callable Statement - PostgreSQL - Multiple Out Parameters-CodePudding

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;

CodePudding user response：

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