Thread: Stored Procedure returns a ResultSet
I have searched the archives and tried two different approaches, both are giving me errors.
(Calling a stored function that returns e.g. an Integer works fine).
Could this be because I'm running PG 7.3.2 ? Would an upgrade to 7.3.4 help?
aa_test is a stored function that takes an integer and returns a refcursor. aa_test works as expected when run from pgsql command line.
Approach #1:
PreparedStatement ps = dbConn.prepareStatement("select aa_test(1)");
ResultSet rs = ps.executeQuery();
while (rs.next())
{
System.out.println("Got : " + rs.getString(2));
// or System.out.println(rs.getString("rpu_name"));
}
rs.close();
ps.close();
Gives the run-time error:
"The column index is out of range"
Approach #2:
CallableStatement cs = dbConn.prepareCall("{? = call aa_test(?) }");
cs.registerOutParameter(1,Types.OTHER);
cs.setInt(2,27);
try
{
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next())
{
System.out.println(rs.getString("rpu_name"));
}
}
catch (java.sql.SQLException ex)
{
System.out.println("test function exception :" + ex);
}
rs.close();
cs.close();
Gives the run-time error "No class found for refcursor"
jonathan.lister@vaisala.com writes: > I have searched the archives and tried two different approaches, both are > giving me errors. > (Calling a stored function that returns e.g. an Integer works fine). > > Could this be because I'm running PG 7.3.2 ? Would an upgrade to 7.3.4 help? > > aa_test is a stored function that takes an integer and returns a refcursor. > aa_test works as expected when run from pgsql command line. > > Approach #1: > PreparedStatement ps = dbConn.prepareStatement("select aa_test(1)"); > ResultSet rs = ps.executeQuery(); > while (rs.next()) > { > System.out.println("Got : " + rs.getString(2)); > // or System.out.println(rs.getString("rpu_name")); > } > rs.close(); > ps.close(); > > Gives the run-time error: > "The column index is out of range" > > Approach #2: > CallableStatement cs = dbConn.prepareCall("{? = call aa_test(?) }"); > cs.registerOutParameter(1,Types.OTHER); > cs.setInt(2,27); > try > { > cs.execute(); > ResultSet rs = (ResultSet) cs.getObject(1); > while (rs.next()) > { > System.out.println(rs.getString("rpu_name")); > } > } > catch (java.sql.SQLException ex) > { > System.out.println("test function exception :" + ex); > } > rs.close(); > cs.close(); > Gives the run-time error "No class found for refcursor" You need the latest JDBC driver. It will allow you to get the refcursor via the second method. We should support the first version but don't yet. -- Nic Ferrier http://www.tapsellferrier.co.uk
Nic wrote: > > We should support the first version but don't yet. > We almost do. The patch is in the list pending incorporation to the sources. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
I just wanted to add a couple of comments to this thread. The database supports two different ways of returning sets of information from a stored function (as of 7.3). RefCursors and Set Returning Functions. These two different mechanisms interact with client interfaces like jdbc in different ways. RefCursors - A query returning a refcursor is only returning a pointer to the client. So 'select aa_test(1)' is only returning the pointer (actually the string name of the cursor) to the client. So in jdbc you can call getString() to get the cursor name, and then issue a 'fetch ... from <name or cursor here>...' sql statement to get the results of the cursor. This is what the driver is doing in 7.4 when you call getObject() on a refcursor. Set Returning Functions - A query calling a set returning function actually gets the data back from the server (instead of a pointer as in refcursors). You generally call set returning functions with a different syntax. You would generally use the following form to get the results of a set returning function: select * from aa_test(1). With a set returning function, the client doesn't need to do anything special to support them since the server is sending the data back to the client in a normal fashion like any select statement. thanks, --Barry jonathan.lister@vaisala.com wrote: > I have searched the archives and tried two different approaches, both are > giving me errors. > (Calling a stored function that returns e.g. an Integer works fine). > > Could this be because I'm running PG 7.3.2 ? Would an upgrade to 7.3.4 help? > > aa_test is a stored function that takes an integer and returns a refcursor. > aa_test works as expected when run from pgsql command line. > > Approach #1: > PreparedStatement ps = dbConn.prepareStatement("select aa_test(1)"); > ResultSet rs = ps.executeQuery(); > while (rs.next()) > { > System.out.println("Got : " + rs.getString(2)); > // or System.out.println(rs.getString("rpu_name")); > } > rs.close(); > ps.close(); > > Gives the run-time error: > "The column index is out of range" > > Approach #2: > CallableStatement cs = dbConn.prepareCall("{? = call aa_test(?) }"); > cs.registerOutParameter(1,Types.OTHER); > cs.setInt(2,27); > try > { > cs.execute(); > ResultSet rs = (ResultSet) cs.getObject(1); > while (rs.next()) > { > System.out.println(rs.getString("rpu_name")); > } > } > catch (java.sql.SQLException ex) > { > System.out.println("test function exception :" + ex); > } > rs.close(); > cs.close(); > Gives the run-time error "No class found for refcursor" > >
Nic, Barry - many thanks for your help.
My project requires the use of stable release code and after the explanation from Barry I now have both RefCursors and SetReturning Functions working well with the production release.
The SRF seems like the more natural way to go - it's a nice feature.
thanks again,
Jonathan
-----Original Message-----
From: Barry Lind [mailto:blind@xythos.com]
Sent: 10 September 2003 17:31
To: jonathan.lister@vaisala.com
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Stored Procedure returns a ResultSet
I just wanted to add a couple of comments to this thread.
The database supports two different ways of returning sets of
information from a stored function (as of 7.3). RefCursors and Set
Returning Functions. These two different mechanisms interact with
client interfaces like jdbc in different ways.
RefCursors - A query returning a refcursor is only returning a pointer
to the client. So 'select aa_test(1)' is only returning the pointer
(actually the string name of the cursor) to the client. So in jdbc you
can call getString() to get the cursor name, and then issue a 'fetch ...
from <name or cursor here>...' sql statement to get the results of the
cursor. This is what the driver is doing in 7.4 when you call
getObject() on a refcursor.
Set Returning Functions - A query calling a set returning function
actually gets the data back from the server (instead of a pointer as in
refcursors). You generally call set returning functions with a
different syntax. You would generally use the following form to get the
results of a set returning function: select * from aa_test(1). With a
set returning function, the client doesn't need to do anything special
to support them since the server is sending the data back to the client
in a normal fashion like any select statement.
thanks,
--Barry
jonathan.lister@vaisala.com wrote:
> I have searched the archives and tried two different approaches, both are
> giving me errors.
> (Calling a stored function that returns e.g. an Integer works fine).
>
> Could this be because I'm running PG 7.3.2 ? Would an upgrade to 7.3.4 help?
>
> aa_test is a stored function that takes an integer and returns a refcursor.
> aa_test works as expected when run from pgsql command line.
>
> Approach #1:
> PreparedStatement ps = dbConn.prepareStatement("select aa_test(1)");
> ResultSet rs = ps.executeQuery();
> while (rs.next())
> {
> System.out.println("Got : " + rs.getString(2));
> // or System.out.println(rs.getString("rpu_name"));
> }
> rs.close();
> ps.close();
>
> Gives the run-time error:
> "The column index is out of range"
>
> Approach #2:
> CallableStatement cs = dbConn.prepareCall("{? = call aa_test(?) }");
> cs.registerOutParameter(1,Types.OTHER);
> cs.setInt(2,27);
> try
> {
> cs.execute();
> ResultSet rs = (ResultSet) cs.getObject(1);
> while (rs.next())
> {
> System.out.println(rs.getString("rpu_name"));
> }
> }
> catch (java.sql.SQLException ex)
> {
> System.out.println("test function exception :" + ex);
> }
> rs.close();
> cs.close();
> Gives the run-time error "No class found for refcursor"
>
>
hi, Where I can download the latest JDBC driver for 7.3.X and jdk1.4.X? support callable statements ? support RefCursors? support Set ? thanks!
jdbc.postgresql.org --Barry sc0698 wrote: > hi, Where I can download the latest JDBC driver for 7.3.X and jdk1.4.X? > support callable statements ? > support RefCursors? > support Set ? > > thanks! > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Which one? pg73jdbc3.jar ? support callable statements ? thanks!
The 7.3 driver supports callable statements. But you might also want to try the 7.4 driver as well. --Barry sc0698 wrote: > Which one? > pg73jdbc3.jar ? > support callable statements ? > > thanks! > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
sorry! In the sql progarm the table name "Handset" should be "test"!
Now the 7.4 pgsql and jdbc driver are beta version, they should have some problems! So I need a stable driver! I want to use the 7.3.4 pgsql and pg73jdbc3.jar! You say they support CallableStatement, but I can't get the right result! the java program: conn.setAutoCommit(false); cstmt = conn.prepareCall(m_query); cstmt.registerOutParameter(1, Types.OTHER); cstmt.execute(); ResultSet resultSet = (ResultSet)cstmt.getObject(1); the Function return refcursor: CREATE OR REPLACE FUNCTION Fuc_test4Query() RETURNS refcursor AS ' DECLARE r_Result refcursor; BEGIN r_QuerySQL := ''select ID,Name from Handset ''; OPEN r_Result FOR EXECUTE r_QuerySQL; return r_Result; END; ' language 'plpgsql'; Otherwise, the fuc_test4query1 How to do with java? thanks! the Function return setof CREATE OR REPLACE FUNCTION public.fuc_test4query1() RETURNS setof test AS ' DECLARE r_Result record; BEGIN r_QuerySQL := \'select ID,Name from Handset \'; FOR r_Result IN execute r_QuerySQL LOOP RETURN next r_Result; END LOOP; return NULL; END; ' LANGUAGE 'plpgsql' VOLATILE;
"sc0698" <sc0698@sina.com> writes: > Now the 7.4 pgsql and jdbc driver are beta version, they should have some > problems! > So I need a stable driver! > > I want to use the 7.3.4 pgsql and pg73jdbc3.jar! > > You say they support CallableStatement, but I can't get the right > result! But 7.3 doesn't support this behaviour... you'll have to use the BETA driver if you want this. Nic
7.3 does support callable statements, but only 7.4 supports returning result sets from a callable statement. --Barry sc0698 wrote: > Now the 7.4 pgsql and jdbc driver are beta version, they should have some > problems! > So I need a stable driver! > > I want to use the 7.3.4 pgsql and pg73jdbc3.jar! > > You say they support CallableStatement, but I can't get the right result! > > the java program: > > conn.setAutoCommit(false); > > cstmt = conn.prepareCall(m_query); > > cstmt.registerOutParameter(1, Types.OTHER); > cstmt.execute(); > > ResultSet resultSet = (ResultSet)cstmt.getObject(1); > > > the Function return refcursor: > > CREATE OR REPLACE FUNCTION Fuc_test4Query() > RETURNS refcursor AS ' > DECLARE > r_Result refcursor; > BEGIN > r_QuerySQL := ''select ID,Name from Handset ''; > OPEN r_Result FOR EXECUTE r_QuerySQL; > return r_Result; > END; > ' > language 'plpgsql'; > > > Otherwise, the fuc_test4query1 > How to do with java? > thanks! > > the Function return setof > > CREATE OR REPLACE FUNCTION public.fuc_test4query1() > RETURNS setof test AS > ' > DECLARE > r_Result record; > BEGIN > r_QuerySQL := \'select ID,Name from Handset \'; > FOR r_Result IN execute r_QuerySQL LOOP > > RETURN next r_Result; > > END LOOP; > > return NULL; > END; > ' > LANGUAGE 'plpgsql' VOLATILE; > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >