Re: Callable Statements

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(at)mindspring(dot)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(at)ptinovacao(dot)pt
PGP:0E129E31D803BC61