Re: pgsql-sq-owner

Hi,

It would be very helpful if anyone could help me with below issue.

I am using below stored proc:

 CREATE OR REPLACE FUNCTION FETCH_CALL_LOGS() RETURNS refcursor AS $$
DECLARE
call_log_rec call_log % rowtype;
call_log_cursor refcursor;
BEGIN
OPEN call_log_cursor FOR
SELECT *
FROM
  call_log
   WHERE aht_read_status = 0
          ORDER BY record_sequence_number ASC limit 20 FOR UPDATE;
LOOP
    FETCH NEXT FROM call_log_cursor INTO call_log_rec;
    EXIT WHEN call_log_rec IS NULL;
    UPDATE call_log SET aht_read_status = 1 WHERE record_sequence_number = call_log_rec.record_sequence_number;
END LOOP;
RETURN call_log_cursor;
END;
$$ LANGUAGE plpgsql;

and trying to read response in java:

           java.sql.CallableStatement proc =  c.prepareCall("{ ? = call fetch_call_logs() }");
           c.setAutoCommit(false); 
           proc.registerOutParameter(1, java.sql.Types.OTHER);
             proc.execute();
             ResultSet rset2 = (ResultSet) proc
                     .getObject(1);
                 while (rset2.next()) {
                    System.out.println(rset2
                        .getString(1));
                  }
                 rset2.close();
                // c.setAutoCommit(false);
                 proc.close();
                 c.close();

but i ma not able to get proper response back... if i comment out the fetch statement and tried doing some static update i am able to get proper response back. Stucked up with this...

I want to open a ref cursor with select for update and then update the records and get the ref cursor in response back in java.
But its not happening .... if i return ref cursor only after select it works fine but after fetch when i am returning the response back i am not getting..