Thread: 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
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)
>
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
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
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 >
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)
>
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
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
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