Thread: Callable Statements

Callable Statements

From
Mark French
Date:
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.

Cheers

Mark


Re: Callable Statements

From
floess@mindspring.com
Date:
Mark:

To answer your question, you can't use CallableStatement where you return a row, rows or RECORD type.

For a function returning multiple rows, a single row, or RECORD you would have to use a PreparedStatement.  The string
passedto your prepared statement would resemble "select * from function" 

I don't have an example on hand...but I may be able to get one for you later.

Scot

-------Original Message-------
From: Mark French <frenchmb@tpg.com.au>
Sent: 04/09/03 04:05 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Callable Statements

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

Cheers

Mark


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


Re: Callable Statements

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

Re: Callable Statements

From
Nic Ferrier
Date:
floess@mindspring.com writes:

> Mark:
>
> To answer your question, you can't use CallableStatement where you
> return a row, rows or RECORD type.
>
> For a function returning multiple rows, a single row, or RECORD you
> would have to use a PreparedStatement.  The string passed to your
> prepared statement would resemble "select * from function"
>
> I don't have an example on hand...but I may be able to get one for
> you later.

To clarify, PostgreSQL JDBC has always been able to call functions
that return ref cursor types as ResultSet's. If you look through the
archives of this list you'll find plenty of examples (searching for
my name will provide at least one example).

CallableStatement support for such procs has recently been added to
the CVS version of PostgreSQL JDBC.


Nic

Re: Callable Statements

From
floess@mindspring.com
Date:
Nic:

Here is a cheesy example question:

Assume I had a function, foo_function, that returns SETOF foo_table.  Internally, the function does nothing more than a
"select* from foo" (also assume it does the return next, etc - again this is a cheesy question), are you saying it will
bepossible to use a CallableStatement and get a ResultSet? 

If so, can I assume that the CallableStatement will outperform using a PreparedStatement and calling the function as
I'vementioned in my original post? 

My apologies for my response:  1) I am doing some contract work in another city and haven't kept up closely on latest
development,2) as of 7.3.2 this was the case. 

Thanks,

Scot

-------Original Message-------
From: Nic Ferrier <nferrier@tapsellferrier.co.uk>
Sent: 04/09/03 08:45 AM
To: floess@mindspring.com
Subject: Re: [JDBC] Callable Statements

>
> floess@mindspring.com writes:

> Mark:
>
> To answer your question, you can't use CallableStatement where you
> return a row, rows or RECORD type.
>
> For a function returning multiple rows, a single row, or RECORD you
> would have to use a PreparedStatement.  The string passed to your
> prepared statement would resemble "select * from function"
>
> I don't have an example on hand...but I may be able to get one for
> you later.

To clarify, PostgreSQL JDBC has always been able to call functions
that return ref cursor types as ResultSet's. If you look through the
archives of this list you'll find plenty of examples (searching for
my name will provide at least one example).

CallableStatement support for such procs has recently been added to
the CVS version of PostgreSQL JDBC.


Nic

>


Re: Callable Statements

From
floess@mindspring.com
Date:
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)
>


Re: Callable Statements

From
Nic Ferrier
Date:
floess@mindspring.com writes:

> Nic:
>
> Here is a cheesy example question:
>
> Assume I had a function, foo_function, that returns SETOF foo_table.
> Internally, the function does nothing more than a "select * from
> foo" (also assume it does the return next, etc - again this is a
> cheesy question), are you saying it will be possible to use a
> CallableStatement and get a ResultSet?

I don't know. I haven't done anything about SETOF.

What you CAN do is return a ref cursor. Here's an example proc:



 -- create or replace function list (  )  returns refcursor as '
 declare
   entrys refcursor;
 begin
   open entrys for
     select id, title, date, entry from someentrys;
   return entrys;
 end;
 -- ' language 'plpgsql';


> If so, can I assume that the CallableStatement will outperform using
> a PreparedStatement and calling the function as I've mentioned in my
> original post?

The performance characteristics of statements have been altered as
well. It's possible to turn off the downloading of the entire query.

In general procs will be quicker than PS's but only because they
generally live for longer.



Nic

Re: Callable Statements

From
Pedro Salazar
Date:
Hi Nic,

for what object you should map the cursor?

I have a sample that I register the parameter out to Types.OTHER. But,
when I execute the CallableStatement, I got this error trace:

No class found for refcursor
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.getObject(AbstractJdbc1Connection.java:693)
        at
org.postgresql.jdbc2.AbstractJdbc2Connection.getObject(AbstractJdbc2Connection.java:117)
        at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:147)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:329)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)

thanks,
Pedro Salazar.

On Wed, 2003-04-09 at 14:00, Nic Ferrier wrote:
> floess@mindspring.com writes:
>
> > Nic:
> >
> > Here is a cheesy example question:
> >
> > Assume I had a function, foo_function, that returns SETOF foo_table.
> > Internally, the function does nothing more than a "select * from
> > foo" (also assume it does the return next, etc - again this is a
> > cheesy question), are you saying it will be possible to use a
> > CallableStatement and get a ResultSet?
>
> I don't know. I haven't done anything about SETOF.
>
> What you CAN do is return a ref cursor. Here's an example proc:
>
>
>
>  -- create or replace function list (  )  returns refcursor as '
>  declare
>    entrys refcursor;
>  begin
>    open entrys for
>      select id, title, date, entry from someentrys;
>    return entrys;
>  end;
>  -- ' language 'plpgsql';
>
>
> > If so, can I assume that the CallableStatement will outperform using
> > a PreparedStatement and calling the function as I've mentioned in my
> > original post?
>
> The performance characteristics of statements have been altered as
> well. It's possible to turn off the downloading of the entire query.
>
> In general procs will be quicker than PS's but only because they
> generally live for longer.
>
>
>
> Nic

--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61


Re: Callable Statements

From
Nic Ferrier
Date:
Pedro Salazar <pedro-b-salazar@ptinovacao.pt> writes:

> Hi Nic,
>
> for what object you should map the cursor?
>
> I have a sample that I register the parameter out to Types.OTHER. But,
> when I execute the CallableStatement, I got this error trace:
>
> No class found for refcursor
>         at
> org.postgresql.jdbc1.AbstractJdbc1Connection.getObject(AbstractJdbc1Connection.java:693)
>         at
> org.postgresql.jdbc2.AbstractJdbc2Connection.getObject(AbstractJdbc2Connection.java:117)
>         at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:147)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:329)
>         at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
>

Are you using a version from CVS?


Nic