problems returning a resultset from a function

From: Leo Martin Orfei <orfeileo(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: problems returning a resultset from a function
Date: 2004-09-04 16:07:47
Message-ID: 20040904160747.66889.qmail@web51806.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

hi.

I have a problem with postgres functions.
I need return a resultset from a postgres function and
browse the resultset in a java app.
I try with this simple function:

create function test() returns catalog.refcursor as'
declare aux refcursor;
BEGIN
OPEN aux FOR SELECT name, address FROM table;
RETURN aux;
END;
'LANGUAGE 'plpgsql';

and the java

CallableStatement cs = null;
ResultSet rs = null;
cs = con.prepareCall("{ ? = call test()}");
cs.registerOutParameter(1, java.sql.Types.OTHER);
rs = cs.executeQuery();
rs.next();
System.out.println("name: " +rs.getString(1));

but throws te following error:

cursor "<unnamed portal 1>" does not exist

I try this too:

Statement s = null;
rs = s.executeQuery("select test()");
rs.next();
System.out.println("name: " +rs.getString(1));

and don't throws an error, but show me the following
result:

name: <unnamed portal 1>

anybody can help me with this?

thnx;


__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wes 2004-09-04 18:26:51 Re: How to determine a database is intact?
Previous Message Google Mike 2004-09-04 16:07:22 How do I list the schema for a table or procedure?

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2004-09-06 01:56:00 Re: Connections dropped randomly
Previous Message Shanmugasundaram Doraisamy 2004-09-03 01:05:18 Out of topic - Need help with connecting Java programs on Linux to Access DB - reg.