Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql refcursor

plpgsql refcursor

From
Franco Bruno Borghesi
Date:
Hi all.

I'm having problems obtaining resultsets from a pgsql function, I've read the jdbc documentation, and read similar messages in the lists, but I still cannot make it work. Perhaps someone can see what's wrong with my code, because I can't find it :(

my plgpsql function is:
CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS '
DECLARE
   result REFCURSOR;
BEGIN
   OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
   RAISE NOTICE ''usuarios_load() -> %'', result;
   RETURN result;
END;
' LANGUAGE 'plpgsql';

my java code is:
conn.setAutoCommit(false);

cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.setInt(2, 33);
cstmt.execute();
rs=(ResultSet)stmt.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(0)); << throws exception
}

the logs says:
2003-12-15 16:54:35 LOG:  statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;
2003-12-15 16:54:35 LOG:  statement: set client_encoding = 'UNICODE'
2003-12-15 16:54:35 LOG:  connection received: host=192.168.1.162 port=49948
2003-12-15 16:54:35 LOG:  connection authorized: user=sapiens database=franco
2003-12-15 16:54:35 LOG:  statement: begin;
2003-12-15 16:54:35 LOG:  statement: select * from  usuarios_load( 33 )  as result;
2003-12-15 16:54:35 LOG:  statement:  SELECT * FROM usuarios WHERE id= $1
CONTEXT:  PL/pgSQL function "usuarios_load" line 5 at open
2003-12-15 16:54:35 NOTICE:  usuarios_load() -> <unnamed portal 1>
2003-12-15 16:54:35 LOG:  statement: SELECT   $1
CONTEXT:  PL/pgSQL function "usuarios_load" line 14 at return
2003-12-15 16:54:35 LOG:  statement: SELECT typname FROM pg_catalog.pg_type WHERE oid = 1790
2003-12-15 16:54:35 LOG:  statement: FETCH ALL IN "<unnamed portal 1>"; << this is where exception is thrown
2003-12-15 16:54:35 LOG:  statement: commit;begin;
2003-12-15 16:54:35 LOG:  unexpected EOF on client connection


thanks in advance!


Attachment

Re: plpgsql refcursor

From
Dave Cramer
Date:
Franco,

There is no Oth element, they start at 1

Dave
On Mon, 2003-12-15 at 15:06, Franco Bruno Borghesi wrote:
> Hi all.
>
> I'm having problems obtaining resultsets from a pgsql function, I've
> read the jdbc documentation, and read similar messages in the lists,
> but I still cannot make it work. Perhaps someone can see what's wrong
> with my code, because I can't find it :(
>
> my plgpsql function is:
> CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS
> '
> DECLARE
>    result REFCURSOR;
> BEGIN
>    OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
>    RAISE NOTICE ''usuarios_load() -> %'', result;
>    RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
>
> my java code is:
> conn.setAutoCommit(false);
>
> cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
> cstmt.registerOutParameter(1, Types.OTHER);
> cstmt.setInt(2, 33);
> cstmt.execute();
> rs=(ResultSet)stmt.getObject(1);
> while (rs.next()) {
> System.out.println(rs.getString(0)); << throws exception
> }
>
> the logs says:
> 2003-12-15 16:54:35 LOG:  statement: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
> 'UNKNOWN' else getdatabaseencoding() end;
> 2003-12-15 16:54:35 LOG:  statement: set client_encoding = 'UNICODE'
> 2003-12-15 16:54:35 LOG:  connection received: host=192.168.1.162
> port=49948
> 2003-12-15 16:54:35 LOG:  connection authorized: user=sapiens
> database=franco
> 2003-12-15 16:54:35 LOG:  statement: begin;
> 2003-12-15 16:54:35 LOG:  statement: select * from  usuarios_load( 33
> )  as result;
> 2003-12-15 16:54:35 LOG:  statement:  SELECT * FROM usuarios WHERE id=
> $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 5 at open
> 2003-12-15 16:54:35 NOTICE:  usuarios_load() -> <unnamed portal 1>
> 2003-12-15 16:54:35 LOG:  statement: SELECT   $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 14 at return
> 2003-12-15 16:54:35 LOG:  statement: SELECT typname FROM
> pg_catalog.pg_type WHERE oid = 1790
> 2003-12-15 16:54:35 LOG:  statement: FETCH ALL IN "<unnamed portal
> 1>"; << this is where exception is thrown
> 2003-12-15 16:54:35 LOG:  statement: commit;begin;
> 2003-12-15 16:54:35 LOG:  unexpected EOF on client connection
>
>
> thanks in advance!
>
>

Re: plpgsql refcursor

From
Franco Bruno Borghesi
Date:
It can't believe I spent all that time without seeing this!!!!

Thanks a lot!


On Mon, 2003-12-15 at 18:28, Dave Cramer wrote: 
Franco,

There is no Oth element, they start at 1

Dave
On Mon, 2003-12-15 at 15:06, Franco Bruno Borghesi wrote:
> Hi all.
> 
> I'm having problems obtaining resultsets from a pgsql function, I've
> read the jdbc documentation, and read similar messages in the lists,
> but I still cannot make it work. Perhaps someone can see what's wrong
> with my code, because I can't find it :(
> 
> my plgpsql function is:
> CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS
> '
> DECLARE
>    result REFCURSOR;
> BEGIN
>    OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
>    RAISE NOTICE ''usuarios_load() -> %'', result;
>    RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
> 
> my java code is:
> conn.setAutoCommit(false);
> 
> cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
> cstmt.registerOutParameter(1, Types.OTHER);
> cstmt.setInt(2, 33);
> cstmt.execute();
> rs=(ResultSet)stmt.getObject(1);
> while (rs.next()) {
> System.out.println(rs.getString(0)); << throws exception
> }
> 
> the logs says:
> 2003-12-15 16:54:35 LOG:  statement: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
> 'UNKNOWN' else getdatabaseencoding() end;
> 2003-12-15 16:54:35 LOG:  statement: set client_encoding = 'UNICODE'
> 2003-12-15 16:54:35 LOG:  connection received: host=192.168.1.162
> port=49948
> 2003-12-15 16:54:35 LOG:  connection authorized: user=sapiens
> database=franco
> 2003-12-15 16:54:35 LOG:  statement: begin;
> 2003-12-15 16:54:35 LOG:  statement: select * from  usuarios_load( 33
> )  as result;
> 2003-12-15 16:54:35 LOG:  statement:  SELECT * FROM usuarios WHERE id=
> $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 5 at open
> 2003-12-15 16:54:35 NOTICE:  usuarios_load() -> <unnamed portal 1>
> 2003-12-15 16:54:35 LOG:  statement: SELECT   $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 14 at return
> 2003-12-15 16:54:35 LOG:  statement: SELECT typname FROM
> pg_catalog.pg_type WHERE oid = 1790
> 2003-12-15 16:54:35 LOG:  statement: FETCH ALL IN "<unnamed portal
> 1>"; << this is where exception is thrown
> 2003-12-15 16:54:35 LOG:  statement: commit;begin;
> 2003-12-15 16:54:35 LOG:  unexpected EOF on client connection
> 
> 
> thanks in advance!
> 
>
Attachment

REMOVE

From
Germán Gazzoni
Date:

 

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Franco Bruno Borghesi
Sent: Monday, December 15, 2003 6:39 PM
To: pg@fastcrypt.com
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] plpgsql refcursor

 

It can't believe I spent all that time without seeing this!!!!

Thanks a lot!


On Mon, 2003-12-15 at 18:28, Dave Cramer wrote: 

Franco,
 
There is no Oth element, they start at 1
 
Dave
On Mon, 2003-12-15 at 15:06, Franco Bruno Borghesi wrote:
> Hi all.
> 
> I'm having problems obtaining resultsets from a pgsql function, I've
> read the jdbc documentation, and read similar messages in the lists,
> but I still cannot make it work. Perhaps someone can see what's wrong
> with my code, because I can't find it :(
> 
> my plgpsql function is:
> CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS
> '
> DECLARE
>    result REFCURSOR;
> BEGIN
>    OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
>    RAISE NOTICE ''usuarios_load() -> %'', result;
>    RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
> 
> my java code is:
> conn.setAutoCommit(false);
> 
> cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
> cstmt.registerOutParameter(1, Types.OTHER);
> cstmt.setInt(2, 33);
> cstmt.execute();
> rs=(ResultSet)stmt.getObject(1);
> while (rs.next()) {
> System.out.println(rs.getString(0)); << throws exception
> }
> 
> the logs says:
> 2003-12-15 16:54:35 LOG:  statement: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
> 'UNKNOWN' else getdatabaseencoding() end;
> 2003-12-15 16:54:35 LOG:  statement: set client_encoding = 'UNICODE'
> 2003-12-15 16:54:35 LOG:  connection received: host=192.168.1.162
> port=49948
> 2003-12-15 16:54:35 LOG:  connection authorized: user=sapiens
> database=franco
> 2003-12-15 16:54:35 LOG:  statement: begin;
> 2003-12-15 16:54:35 LOG:  statement: select * from  usuarios_load( 33
> )  as result;
> 2003-12-15 16:54:35 LOG:  statement:  SELECT * FROM usuarios WHERE id=
> $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 5 at open
> 2003-12-15 16:54:35 NOTICE:  usuarios_load() -> <unnamed portal 1>
> 2003-12-15 16:54:35 LOG:  statement: SELECT   $1
> CONTEXT:  PL/pgSQL function "usuarios_load" line 14 at return
> 2003-12-15 16:54:35 LOG:  statement: SELECT typname FROM
> pg_catalog.pg_type WHERE oid = 1790
> 2003-12-15 16:54:35 LOG:  statement: FETCH ALL IN "<unnamed portal
> 1>"; << this is where exception is thrown
> 2003-12-15 16:54:35 LOG:  statement: commit;begin;
> 2003-12-15 16:54:35 LOG:  unexpected EOF on client connection
> 
> 
> thanks in advance!
> 
>