Lists: | pgsql-jdbc |
---|
From: | Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | plpgsql refcursor |
Date: | 2003-12-15 20:06:39 |
Message-ID: | 1071518799.1909.16.camel@taz.oficina |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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!
From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar> |
Cc: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: plpgsql refcursor |
Date: | 2003-12-15 21:28:32 |
Message-ID: | 1071523712.1660.462.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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!
>
>
From: | Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar> |
---|---|
To: | pg(at)fastcrypt(dot)com |
Cc: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: plpgsql refcursor |
Date: | 2003-12-15 21:39:27 |
Message-ID: | 1071524366.2363.0.camel@taz.oficina |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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!
> >
> >
>
>
From: | Germán Gazzoni <gazzoni(at)hotmail(dot)com> |
---|---|
To: | "'Franco Bruno Borghesi'" <franco(at)akyasociados(dot)com(dot)ar>, <pg(at)fastcrypt(dot)com> |
Cc: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | REMOVE |
Date: | 2003-12-16 12:06:03 |
Message-ID: | !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAcOCCLdQPV0+DGSOsricq+8KAAAAQAAAAEDSimrDlHUqV6D3jco/QzQEAAAAA@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
_____
From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Franco Bruno Borghesi
Sent: Monday, December 15, 2003 6:39 PM
To: pg(at)fastcrypt(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)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!
>
>