How to pass Cursor variable to postgres proceudre while calling from java?

Question

I am calling a postgres stored procedure from spring boot application which has 1 IN parameter of type text and 1 INOUT parameter of type refcursor. How to call this procedure from springboot application other than CallableStatement.

public class CallProc {
    public static void main(String[] args) throws ClassNotFoundException, java.sql.SQLException {
        
        Class.forName("org.postgresql.Driver");
        
        String url = "jdbc:postgresql://azure.com/test";
        Properties props = new Properties();
        props.setProperty("user","test");
        props.setProperty("password","test");
        props.setProperty("ssl","true");
        props.setProperty("escapeSyntaxCallMode", "callIfNoReturn");
        Connection conn = DriverManager.getConnection(url, props);

        // need a transaction
        conn.setAutoCommit(false);

        java.sql.CallableStatement callableStatement =
            conn.prepareCall("{call myProc(?, ?)}");

        callableStatement.setString(1, "user");
        callableStatement.setObject(2, null);

        callableStatement.registerOutParameter(2, java.sql.Types.REF_CURSOR);

        callableStatement.execute();
       
        java.sql.ResultSet rs =
            (java.sql.ResultSet) callableStatement.getObject(2);

        while (rs.next())
            System.out.println(rs.getInt(1));

        rs.close();
        conn.commit();

        conn.close();
    }
}

and my procedure definition is like this,

CREATE OR REPLACE PROCEDURE myapp.MyProc(
     in_user_id TEXT,
     INOUT user_roles refcursor)
language plpgsql
AS $BODY$
DECLARE

DERIVED USER_ID VARCHAR(50);

BEGIN
//body
...
//
END
$BODY$

Thanks

Becasue CallableStatement is giving org.postgresql.util.PSQLException: ERROR: helloworld() is a procedure Hint: To call a procedure, use CALL. this error @LaurenzAlbe can check limitation of PG JDBC driver limitation here highgo.ca/2020/11/25/… — Abhishek Patil
– Abhishek Patil, Commented Sep 13, 2021 at 12:17

Laurenz Albe · Accepted Answer · 2021-09-15 13:09:39Z

1

To call procedures using a java.sql.CallableStatement, use the connection parameter escapeSyntaxCallMode with the values call or callIfNoReturn and specify no return parameter. As the documentation says:

escapeSyntaxCallMode = String

Specifies how the driver transforms JDBC escape call syntax into underlying SQL, for invoking procedures or functions. In escapeSyntaxCallMode=select mode (the default), the driver always uses a SELECT statement (allowing function invocation only). In escapeSyntaxCallMode=callIfNoReturn mode, the driver uses a CALL statement (allowing procedure invocation) if there is no return parameter specified, otherwise the driver uses a SELECT statement. In escapeSyntaxCallMode=call mode, the driver always uses a CALL statement (allowing procedure invocation only).

The problem is that before procedures were added in v11, the JDBC driver converted a CallableStatement invocation to a function call, which does not work for procedures.

Here is a code sample that calls a procedure p(IN integer, INOUT refcursor), where the refcursor is on a result set of integers:

public class CallProc {
    public static void main(String[] args) throws ClassNotFoundException, java.sql.SQLException {
        Class.forName("org.postgresql.Driver");

        java.sql.Connection conn =
            java.sql.DriverManager.getConnection(
                "jdbc:postgresql:test?user=laurenz&password=something&escapeSyntaxCallMode=callIfNoReturn"
            );

        // need a transaction
        conn.setAutoCommit(false);

        java.sql.CallableStatement callableStatement =
            conn.prepareCall("{call p(?::text, ?::refcursor)}");

        callableStatement.setInt(1, 5);
        callableStatement.setObject(2, null);

        callableStatement.registerOutParameter(2, java.sql.Types.REF_CURSOR);

        callableStatement.execute();
       
        java.sql.ResultSet rs =
            (java.sql.ResultSet) callableStatement.getObject(2);

        while (rs.next())
            System.out.println(rs.getInt(1));

        rs.close();
        conn.commit();

        conn.close();
    }
}

edited Sep 15, 2021 at 13:09

answered Sep 13, 2021 at 12:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

13 Comments

Abhishek Patil Over a year ago

I am trying to like this { ? = call myProc(?,?)}

Abhishek Patil Over a year ago

I am trying like this // myProc(IN text,INOUT refcursor) query = "call myProc(?,?)"; CallabelStatement stmt = prepareCall(query); stmt.setString(1,"userID"); stmt.registerOutParameter(2,Types.REF_CUR); here I am getting error as statement does not declare an OUT parameter use { ? = call(?,?)query = "call myProc(?,?)"; stmt.setString(1,"userID"); stmt.registerOutParameter(2,Types.REF_CUR); ERROR-> statement does not declare an OUT parameter use { ? = call(?,?)

Abhishek Patil Over a year ago

and when I try with this String query = "{ ? = call myProc(?,?)}"; CallabelStatement stmt = prepareCall(query); stmt.registerOutParameter(1,Types.REF_CUR); stmt.setString(2,"userID); stmt.registerOutParameter(3,Types.REF_CUR); ERROR -> function myProc does not exist.

Laurenz Albe Over a year ago

I have revised the answer and added an example.

Abhishek Patil Over a year ago

Hi Laurenz, even though I specified escapeSyntaxCallMode=call or escapeSyntaxCallMode=callIfNoReturn its giving error that function myProc(character varying, unknown) does not exist. No function matches the given name and arguments. add explicit type cast

|

Collectives™ on Stack Overflow

How to pass Cursor variable to postgres proceudre while calling from java?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related