agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: ALMA TAHIR <almaheena2003@yahoo.co.in>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Function Issue
Date: Thu, 27 Feb 2014 20:23:12 +0800 (SGT)
Message-ID: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.....

view thread (7+ messages)  latest in thread

Message-ID: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com>
Permalink:  ../1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com/
Also on:    postgresql.org/message-id/1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: almaheena2003@yahoo.co.in
  Subject: Re: Function Issue
  In-Reply-To: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox