Re: Callable Statements

Nic:

Color me stupid!  I have had a need for exactly this functionality but was using a PreparedStatement to "select * from
function"

This is great news!

Tell ya what, I was just bragging on y'all here at work regarding how quickly and how helpful everyone is!

Well, thanks a lot for this bit of information...I did read the section regarding the ref cursor type, but I didn't put
theTypes.OTHER together with that section.  I feel naive and sorta silly... 

Thanks again,

Scot

-------Original Message-------
From: Nic Ferrier <nferrier@tapsellferrier.co.uk>
Sent: 04/09/03 08:42 AM
To: Mark French <frenchmb@tpg.com.au>
Subject: Re: [JDBC] Callable Statements

>
> Mark French <frenchmb@tpg.com.au> writes:

> Hi,
>
> I'm new to postgres JDBC and was wondering if it supported callable
> statements at all?  The documentation doesn't have any examples and
> would like to know it was possible to use them to call functions that
> return multiple rows?  An example would be greatly appreciated.
>

The CVS version of PG implements CallableStatements that can return
multiple rows.

Do it like this:

   try
    {
       CallableStatement proc
         = con.prepareCall("{ ? = call someproc (?) }");
       proc.registerOutParameter(1, Types.OTHER);
       proc.setInt(2, 1);
       proc.execute();
       ResultSet rs = (ResultSet) proc.getObject(1);
       while (rs.next())
        {
           System.out.println("ha!");
        }
       con.close();
    }

The proc should return a ref cursor type, much like it would in
Oracle. The PL/PGSQL manual explains how to do that.


Nic


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>