Function Issue

From: ALMA TAHIR <almaheena2003(at)yahoo(dot)co(dot)in>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Function Issue
Date: 2014-02-27 12:23:12
Message-ID: 1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 am 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..
Where am I doing the mistake or anything I am missing???? Please
help me with the same ... it would be very helpful.....

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jan Ostrochovsky 2014-02-27 14:20:18 how to effectively SELECT new "customers"
Previous Message amul sul 2014-02-25 04:59:08 Re: SRF_RETURN_NEXT Error: rows returned by function are not all of the same row type