Skip site navigation (1) Skip section navigation (2)

Re: plpgsql refcursor

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 (view raw or flat)
Thread:
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!
> > 
> > 
> 
> 

In response to

Responses

  • REMOVE at 2003-12-16 12:06:03 from Germán Gazzoni

pgsql-jdbc by date

Next:From: ravi kumarDate: 2003-12-16 04:28:48
Subject: Regarding install pgsql with JDBC support
Previous:From: Dave CramerDate: 2003-12-15 21:28:32
Subject: Re: plpgsql refcursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group