How to access the procedure that return setof refcursor from PostgreSQL in Java?

Question

Need to access a procedure that return setof refcursor from PostgreSQL.

I am able to access the first object but not rest of object not rest of objects.

    con.setAutoCommit(false);
    try (CallableStatement proc = 
             con.prepareCall("{ ? = call usp_sel_article_initialdata_new1() }")) {
        proc.registerOutParameter(1, Types.OTHER);
        proc.execute();
        ResultSet results = (ResultSet) proc.getObject(1); 
        while (results.next()) {
            System.out.println("Result Id" + results.getString(1));
            System.out.println("Results Name" + results.getString(2));
    }

This give me the first refcursor values but when i try to use second refcursor it give me error I use this line:

proc.registerOutParameter(2, Types.OTHER);

It is giving errors . Also tried:

proc.registerOutParameter(1, Types.REF);

This also not work. Procedure Example is:

    CREATE OR REPLACE FUNCTION usp_sel_article_initialdata_new1()
    RETURNS SETOF refcursor AS
    $BODY$
    Declare swv_refcur refcursor;
    swv_refcur2 refcursor;
    DECLARE r record;
    BEGIN

    open SWV_RefCur for Select OM.opID as ID,OM.OperatorName as Name from operator
    AS OM (OPid bigint,OperatorName character varying(100),status boolean)
    where OM.status = true 
    order By OperatorName;
    return next SWV_RefCur;

    open SWV_RefCur2 for Select CC.cirid as ID,CC.cirnm as Name from  circle 
    AS CC (cirid bigint,cirnm character varying(100),status boolean)
    where Status = true and cirid not in(28,31) 
    order by cirnm;
    return next SWV_RefCur2;

Please help me how to access second object.

@a_horse_with_no_name But then it will return this : <unnamed portal 1> <unnamed portal 2> I am not able to use this return values. — pankaj369
– pankaj369, Commented Sep 8, 2014 at 7:04

score 3 · Accepted Answer · 2014-09-08 12:59:09Z

3

returns setof refcursor means you get a regular ResultSet where each "row" contains another ResultSet when calling getObject():

The following works for me:

ResultSet rs = stmt.executeQuery("select * from usp_sel_article_initialdata_new1()");
if (rs.next())
{
  // first result set returned
  Object o = rs.getObject(1);
  if (o instanceof ResultSet)
  {
    ResultSet rs1 = (ResultSet)o;
    while (rs1.next())
    {
       int id = rs1.getInt(1);
       String name = rs1.getString(2);
       .... retrieve the other columns using the approriate getXXX() calls
    }
  }
}

if (rs.next()) 
{
  // process second ResultSet 
  Object o = rs.getObject(1);
  if (o instanceof ResultSet)
  {
    ResultSet rs2 = (ResultSet)o;
    while (rs2.next())
    {
       ......
    }
  }
}

From within psql you can also use select * from usp_sel_article_initialdata_new1() you just need to use FETCH ALL afterwards. See the manual for an example: http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#AEN59018

postgres=> select * from usp_sel_article_initialdata_new1();
 usp_sel_article_initialdata_new1
----------------------------------
 <unnamed portal 1>
 <unnamed portal 2>
(2 rows)

postgres=> fetch all from "<unnamed portal 1>";
 ?column?
----------
        1
(1 row)

postgres=> fetch all from "<unnamed portal 2>";
 ?column?
----------
        2
(1 row)

postgres=>

(I created a dummy function for the above example that only returns a single row with the value 1 for the first cursor and 2 for the second cursor)

Edit:

In order for this to work, this needs to be run inside a transaction. Therefor autocommit must be turned off:

connection.setAutoCommit(false);

edited Sep 8, 2014 at 12:59

answered Sep 8, 2014 at 9:03

user330315

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

9 Comments

pankaj369 Over a year ago

I have use this code it is giving error at this line: Object o = rs.getObject(1); "org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist"

user330315 Over a year ago

@pankaj369 It worked for me using Postgres 9.3 and the current 9.3 JDBC driver (build 1102). Which Postgres version and driver version are you using?

pankaj369 Over a year ago

@ If i print it string value like this : if (rs.next()) { System.out.println("first result set"+rs.getString(1)); Then it is printing this: "first result set<unnamed portal 1>"

user330315 Over a year ago

You should not use getString() you have to use getObject() and then cast it to a ResultSet as I have done in my code.

pankaj369 Over a year ago

I was using your code. By printing i was just checking what it is returning. After removing System.out.println also it is giving same error. And i was using old jar but now i have changed it to 9.3 JDBC driver and i am using Postgres9.3 .But still same error exist.

|

babell00 · Accepted Answer · 2014-09-08 08:24:50Z

0

When i call stored procedures i use EntityManager. Below I attached simple example:

//StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("[database_name].[schema_name].[procedure_name]");
StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("[Mydatabase].[dbo].[process_user]");

//registering INPUT parameters
storedProcedure.registerStoredProcedureParameter("Id",Integer.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("Name",String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("UserType",Integer.class, ParameterMode.IN);

//registering OUTPUT parameters
storedProcedure.registerStoredProcedureParameter("ErrorCode",Integer.class, ParameterMode.OUT);
storedProcedure.registerStoredProcedureParameter("ErrorMessage",String.class, ParameterMode.OUT);

//setting INPUT parameters
storedProcedure.setParameter("Id", 12345);
storedProcedure.setParameter("Name", "Mick");
storedProcedure.setParameter("UserType", 1);

//executing procedure
storedProcedure.execute();

//getting results 
Integer errorCode = (Integer) storedProcedure.getOutputParameterValue("ErrorCode");
String errorMessage = (String) storedProcedure.getOutputParameterValue("ErrorMessage");

Hope this will help

answered Sep 8, 2014 at 8:24

babell00

817 bronze badges

1 Comment

pankaj369 Over a year ago

I havent use hibernate and in this project also i am not using hibernate so it is not possible for me to implement this code. My main problem is that i am not able to access the second cursor in procedure that return setof refcursor.

Mahi · Accepted Answer · 2015-12-09 05:36:09Z

This worked for me in following version

springframework-version :- 4.2.1.RELEASE

hibernate.version:- 5.0.1.Final

postgresql.version :- 9.1-901-1.jdbc4

// Call the stored procedure
ProcedureCall procedureCall =  getSession().createStoredProcedureCall("get_all_matching_email_profile");

// Register the output parameter, postgres support ref_cursor as first parameter only, so don't change it
procedureCall.registerParameter(1, void.class, ParameterMode.REF_CURSOR);

// Inpur parameters
procedureCall.registerParameter(2, BigDecimal.class, ParameterMode.IN);
procedureCall.registerParameter(3, String.class, ParameterMode.IN);
procedureCall.getParameterRegistration(2).bindValue(companyId);
procedureCall.getParameterRegistration(3).bindValue(tempArray[i]); 

 // Execute the procedure and get the result
ProcedureOutputs procedureOutputs = procedureCall.getOutputs();
ResultSetOutput resultSetOutput = (ResultSetOutput)procedureOutputs.getCurrent();
List results = resultSetOutput.getResultList();

log.info("The result is "+results.size());

for(Integer j=0;j<results.size();j++) {
Object[] objects = (Object[]) results.get(j);
log.info("The result is "+objects.length);
 }

David Buck · Accepted Answer · 2020-05-07 13:19:58Z

This worked for me:

Connection conn = DBConnection.getNMSDBConnection();    
            conn.setAutoCommit(false);
             CallableStatement properCase = conn.prepareCall("{ ? = call data.temperature_raw_data( ?,?,?,? ) }") ;
                 properCase.registerOutParameter(1, Types.REF_CURSOR);
                 properCase.setLong(2, startDate);
                 properCase.setLong(3, endDate);
                 properCase.setString(4, deviceIp);
                 properCase.setString(5, customers);
                 properCase.execute();
                ResultSet rs = (ResultSet) properCase.getObject(1);
                while (rs.next()) 
                {
                    HashMap<String, Object> record = new HashMap<>();                       
                    record.put("date", rs.getTimestamp(1));
                    record.put("nodeIp", rs.getString(2));
                    record.put("avg", rs.getDouble(3));
                    records.add(record);
                }

Ashutosh · Accepted Answer · 2020-06-18 12:42:28Z

if you want to write a java code which will call PostgreSQL Stored procedure where 
there is an INOUT refcursor.
PostgreSQL SP:

 CREATE OR REPLACE PROCEDURE  Read_DataDetails(INOUT  my_cursor REFCURSOR = 'rs_resultone')
 LANGUAGE plpgsql
 AS $$                                                    
 BEGIN
 OPEN my_cursor FOR Select * from MyData;      
 END;
 $$;

 corresponding java code will be:

connection = getConnection(your connection parameter);
if (connection != null) {
connection.setAutoCommit(false); // This line must be written just after the 
//connection open otherwise it will not work since cursor will be closed immediately. 
String readMetaData = "CALL Read_DataDetails(?)";
callableStatement = connection.prepareCall(readMetaData);
callableStatement.registerOutParameter(1, Types.REF_CURSOR);
callableStatement.execute();
rs = (ResultSet) callableStatement.getObject(1);
if (rs != null) {
    while (rs.next()) {
                 <Your Logic>
                }//end while
            }//end if

}//end if

Collectives™ on Stack Overflow

How to access the procedure that return setof refcursor from PostgreSQL in Java?

5 Answers 5

9 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related