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