Re: 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(dot)lister(at)vaisala(dot)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(at)redhat(dot)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(dot)lister(at)vaisala(dot)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
>

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;

sorry! In the sql progarm the table name "Handset" should be "test"!

"sc0698" <sc0698(at)sina(dot)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
>