Thread: Stored Procedure returns a ResultSet

Stored Procedure returns a ResultSet

From
jonathan.lister@vaisala.com
Date:

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"

Re: Stored Procedure returns a ResultSet

From
Nic
Date:
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

Re: Stored Procedure returns a ResultSet

From
Fernando Nasser
Date:
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


Re: Stored Procedure returns a ResultSet

From
Barry Lind
Date:
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"
>
>




Re: Stored Procedure returns a ResultSet

From
jonathan.lister@vaisala.com
Date:

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"
>
>

Re: Stored Procedure returns a ResultSet

From
"sc0698"
Date:
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!



Re: Stored Procedure returns a ResultSet

From
Barry Lind
Date:
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
>




Re: Stored Procedure returns a ResultSet

From
"sc0698"
Date:
Which one?
pg73jdbc3.jar ?
support callable statements ?

thanks!



Re: Stored Procedure returns a ResultSet

From
Barry Lind
Date:
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
>




Re: Stored Procedure returns a ResultSet

From
"sc0698"
Date:
sorry! In the sql progarm  the table name "Handset" should be "test"!



Re: Stored Procedure returns a ResultSet

From
"sc0698"
Date:
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;




Re: Stored Procedure returns a ResultSet

From
Nic Ferrier
Date:
"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

Re: Stored Procedure returns a ResultSet

From
Barry Lind
Date:
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
>