CODEWITHSUNDEEP
javapostgresqlstored-procedures
Abhishek Patil
Abhishek Patil

Reputation: 15

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

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

Upvotes: 1

Views: 2574

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

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();
    }
}

Upvotes: 1

Related Questions