Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ02Y-0001sL-U2 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 12:26:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJ02Y-0007PL-Ee for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 12:26:14 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ02X-0007PF-IV for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 12:26:13 +0000 Received: from nm31.bullet.mail.ne1.yahoo.com ([98.138.229.24]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ02T-00043A-De for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 12:26:12 +0000 Received: from [127.0.0.1] by nm31.bullet.mail.ne1.yahoo.com with NNFMP; 27 Feb 2014 12:26:06 -0000 Received: from [98.138.226.178] by nm31.bullet.mail.ne1.yahoo.com with NNFMP; 27 Feb 2014 12:23:13 -0000 Received: from [106.10.166.120] by tm13.bullet.mail.ne1.yahoo.com with NNFMP; 27 Feb 2014 12:23:12 -0000 Received: from [106.10.151.252] by tm9.bullet.mail.sg3.yahoo.com with NNFMP; 27 Feb 2014 12:23:12 -0000 Received: from [127.0.0.1] by omp1001.mail.sg3.yahoo.com with NNFMP; 27 Feb 2014 12:23:12 -0000 X-Yahoo-Newman-Property: ymail-4 X-Yahoo-Newman-Id: 482959.31880.bm@omp1001.mail.sg3.yahoo.com Received: (qmail 56393 invoked by uid 60001); 27 Feb 2014 12:23:12 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.in; s=s1024; t=1393503792; bh=jhRAkwK3kktyfV3ErRwNrbpfaovru75fIEB/spCIJuc=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=joC3EBRoYSmHOZxxqR8Ssobcf1nck7/J7zIxf5gAbTP0oO9vBwBdBylTcmn8HJtIfvUGHVjBIxUPp8+vy4tQ7dk9qHd2bagtn9uDBOfzZw5CW22zSl8ecJey5O6ZdDFFIPjndTnjWYXKPABR2dGtJDzTHHq1mPD9kjALCVgSplM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.in; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=BeC2VSRmORTn8PLOc1f8shczebtH7ha/Hg5H3DO0oOl1qx65O9g3Y8Z7k0tXgJstdnfRj00xE1+8omAD47Xnl406X2xpq0G+CoK6yyVYgN7S9DyNcl+t6GLBoeprjpN8NKgAPJnO9Z07rhoNTL2bCa6qOTVgi+qdn52KYpxoXSA=; X-YMail-OSG: oYDQ3cEVM1lE9A6jIfJrrJpdektBES2ZXoZetC1LqXBENTO I9e4JX1TLJtI_njlpCEM0OXo_ZhFB.figxWa_VS1hGAW.ymYXggV9cZYQyX1 yHcxJMK7f6IDAwfsgDsh6bTAB79Rr_qwc.M7_mLy70G.KESaEUDE21HDdDBt zQgHbXMuJsQ9K6BRbmKAmb1eyHlxnJH8AnskuWMZ2Yf7cLUEcPEIEDC1a1gD 3t8B71uLNmMOgusdnOvjLKxrMUMCN41bCevxiWXWccM1dwVOatPM84NKgnmS jkpy4.y18_N._meWTJWovgJ51d8MQotScq_tkcKwptGwa2WFqyWmq6t2Ixzg vt6OxVzqV9xyia2cS7ofNOR06sHKPYxXNgkh6gnPExiI3fnmJC0j7f40mHs_ ASvZ8TV4cTh0_aLH3HyGcXHT0MvcW8wg36E1SMgTDfIwizBvLPrrd6lnEU16 z.XCvUoaJdVgLwn1ChVJNu5cMF2MmAPKppnq3hT3b8YGg2JQeklUsfCFJEez A768v6zmf.XwR10trbtIbT231q2IK75l1QxBThjEL9JHjrYFpWnUJvOpHVSf aMK0jPMZS Received: from [106.216.137.100] by web192706.mail.sg3.yahoo.com via HTTP; Thu, 27 Feb 2014 20:23:12 SGT X-Rocket-MIMEInfo: 002.001, SXQgd291bGQgYmUgdmVyeSBoZWxwZnVsIGlmIGFueW9uZSBjb3VsZCBoZWxwIG1lIHdpdGggYmVsb3cgaXNzdWUuCkkgYW0gdXNpbmcgYmVsb3cgc3RvcmVkIHByb2M6CsKgQ1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gRkVUQ0hfQ0FMTF9MT0dTKCkgUkVUVVJOUwpyZWZjdXJzb3IgQVMgJCQKREVDTEFSRQpjYWxsX2xvZ19yZWMgY2FsbF9sb2cgJSByb3d0eXBlOwpjYWxsX2xvZ19jdXJzb3IgcmVmY3Vyc29yOwpCRUdJTgpPUEVOIGNhbGxfbG9nX2N1cnNvciBGT1IKU0VMRUNUICoKRlJPTSAKwqAgY2FsbF8BMAEBAQE- X-Mailer: YahooMailWebService/0.8.177.636 Message-ID: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> Date: Thu, 27 Feb 2014 20:23:12 +0800 (SGT) From: ALMA TAHIR Reply-To: ALMA TAHIR Subject: Function Issue To: "pgsql-sql@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-897285659-938561821-1393503792=:47079" X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ---897285659-938561821-1393503792=:47079 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable It would be very helpful if anyone could help me with below issue.=0AI am u= sing below stored proc:=0A=A0CREATE OR REPLACE FUNCTION FETCH_CALL_LOGS() R= ETURNS=0Arefcursor AS $$=0ADECLARE=0Acall_log_rec call_log % rowtype;=0Acal= l_log_cursor refcursor;=0ABEGIN=0AOPEN call_log_cursor FOR=0ASELECT *=0AFRO= M =0A=A0 call_log =0A=A0=A0 WHERE aht_read_status =3D 0 =0A=A0=A0=A0=A0=A0= =A0=A0=A0=A0 ORDER BY=0Arecord_sequence_number ASC limit 20 FOR UPDATE;=0AL= OOP=0A=A0=A0=A0 FETCH NEXT FROM call_log_cursor INTO call_log_rec;=0A=A0=A0= =A0 EXIT WHEN call_log_rec IS NULL;=0A=A0=A0=A0 UPDATE call_log SET aht_rea= d_status =3D 1 WHERE=0Arecord_sequence_number =3D call_log_rec.record_seque= nce_number;=0AEND LOOP;=0ARETURN call_log_cursor;=0AEND;=0A$$ LANGUAGE plpg= sql;=0A=A0=0Aand trying to read response in java:=0A=A0=0A=A0=A0=A0 =A0=A0= =A0=A0=A0=A0=0Ajava.sql.CallableStatement proc =3D=A0 c.prepareCall("{ ? = =3D call=0Afetch_call_logs() }");=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=0Ac.setAut= oCommit(false);=A0 =0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=0Aproc.registerOutParame= ter(1, java.sql.Types.OTHER);=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=0Aproc.e= xecute(); =0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0 ResultSet=0Arset2 =3D (Resu= ltSet) proc=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =0A.getObject(1);=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 while= =0A(rset2.next()) {=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=0ASystem.out.println(rset2=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=0A.getString(1));=0A=A0=A0=A0=0A=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=0A}=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=0Arset2.close();=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = //=0Ac.setAutoCommit(false);=0A=A0=A0=A0=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=0Aproc.close();=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=0Ac= .close();=0A=A0=0Abut i am not able to get proper response back... if i com= ment out=0Athe fetch statement and tried doing some static update i am able= to get proper=0Aresponse back. Stucked up with this...=0AI want to open a = ref cursor with select for update and then update=0Athe records and get the= ref cursor in response back in java.=0ABut its not happening .... if i ret= urn ref cursor only after select it works=0Afine but after fetch when i am = returning the response back i am not getting..=0AWhere am I doing the mista= ke or anything I am missing???? Please=0Ahelp me with the same ... it would= be very helpful..... ---897285659-938561821-1393503792=:47079 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
It would be very helpful if anyone could help me with below issue.
I am using below stored proc:
 CREATE OR REPLACE FUNCTION FETC= H_CALL_LOGS() RETURNS=0Arefcursor AS $$
=0ADECLARE
=0Acall_log_rec ca= ll_log % rowtype;
=0Acall_log_cursor refcursor;
=0ABEGIN
=0AOPEN c= all_log_cursor FOR
=0ASELECT *
=0AFROM
=0A  call_log
=0A=    WHERE aht_read_status =3D 0
=0A    &nb= sp;     ORDER BY=0Arecord_sequence_number ASC limit 20 = FOR UPDATE;
=0ALOOP
=0A    FETCH NEXT FROM call_log_cu= rsor INTO call_log_rec;
=0A    EXIT WHEN call_log_rec IS = NULL;
=0A    UPDATE call_log SET aht_read_status =3D 1 WH= ERE=0Arecord_sequence_number =3D call_log_rec.record_sequence_number;
= =0AEND LOOP;
=0ARETURN call_log_cursor;
=0AEND;
=0A$$ LANGUAGE plp= gsql;
 
and trying to read response in java:
 
 &nbs= p;        =0Ajava.sql.CallableStatement = proc =3D  c.prepareCall("{ ? =3D call=0Afetch_call_logs() }");
=0A&= nbsp;         =0Ac.setAutoCommit(fa= lse); 
=0A          = =0Aproc.registerOutParameter(1, java.sql.Types.OTHER);
=0A  &n= bsp;         =0Aproc.execute(); =0A             Res= ultSet=0Arset2 =3D (ResultSet) proc
=0A   =0A  =             &nb= sp; =0A.getObject(1);
=0A   =0A   &nb= sp;         while=0A(rset2.next()) = {
=0A   =0A       &nbs= p;       =0ASystem.out.println(rset2
= =0A   =0A        &nb= sp;          =0A.getStrin= g(1));
=0A   =0A       = ;      =0A}
=0A   =0A =            =0Arset2.= close();
=0A   =0A      &nb= sp;     //=0Ac.setAutoCommit(false);
=0A  =  =0A           =  =0Aproc.close();
=0A        &nb= sp;       =0Ac.close();
 
but i= am not able to get proper response back... if i comment out=0Athe fetch st= atement and tried doing some static update i am able to get proper=0Arespon= se back. Stucked up with this...
I want to open a ref cursor with select for update and then update=0Athe= records and get the ref cursor in response back in java.
=0ABut its not= happening .... if i return ref cursor only after select it works=0Afine bu= t after fetch when i am returning the response back i am not getting..=
=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A= =0A=0A=0A
Where am I doing the mistake or anythi= ng I am missing???? Please=0Ahelp me with the same ... it would be very hel= pful.....
---897285659-938561821-1393503792=:47079--