Callable Statement - PostgreSQL - Multiple Out Parameters

Question

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;

Please provide a minimal reproducible example that includes a basic (simplified) example of the stored procedure you're executing and the Java code you use to execute. — Mark Rotteveel
– Mark Rotteveel, Commented Dec 21, 2021 at 11:12
Are you sure the procedure is defined in PostgreSQL database? The syntax resemble Oracle... — Marmite Bomber
– Marmite Bomber, Commented Dec 21, 2021 at 13:27
Yes, the procedure is from Oracle was recently migrated into PostgreSQL. The source copy for the PostgreSQL was not handy hence added the Oracle version of the procedure. The PostgreSQL procedure has no issues and runs fine when running from pgAdmin client. But, when invoked from JDBC it throws the above error. It appears to be some limitation on the Driver. — Sabari
– Sabari, Commented Dec 21, 2021 at 16:36

Mark Rotteveel · Accepted Answer · 2021-12-22 10:49:17Z

5

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

edited Dec 22, 2021 at 10:49

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered Dec 21, 2021 at 17:17

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marmite Bomber Over a year ago

Collectives™ on Stack Overflow

Callable Statement - PostgreSQL - Multiple Out Parameters

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related