Re: Callable statements and rowsets

On Wed, 10 Mar 2004, Adriaan Joubert wrote:

> Hi,
>
> It is not clear to me from the documentation whether the following
> should work, but I've definitely not had any luck getting it working.
> This is all in postgres 7.4.1 using the jdbc driver that comes with the
> distribution, using jdk-1.4.2 on debian linux.
>
> CREATE OR REPLACE FUNCTION
> fund_max_min_correlation(int,smallint,smallint,int,int)
> RETURNS SETOF correlation_type
> AS '$libdir/contrib/fund.so','fund_max_min_correlation'
> LANGUAGE 'C' STABLE STRICT;
>
> connection.setAutoCommit(false);
> CallableStatement proc = connection.prepareCall(
> "{ ? = call fund_max_min_correlation ( ?,
> CAST(? AS SMALLINT), CAST(? AS SMALLINT), ?, ? ) }");
> proc.registerOutParameter(1, Types.OTHER);
> proc.setInt(2, fundId);
> proc.setShort(3,fromMonth);
> proc.setShort(4,upToMonth);
> proc.setInt(5,nCorrelations);
> proc.setInt(6, nDataPoints);
> if (proc.execute()) {
> ResultSet results = (ResultSet) proc.getObject(1);
> etc...
>

This usage of proc.getObject() is for a function that returns a refcursor,
not a set. For this you don't want the CallableStatement interface at
all, but just a regular PreparedStatement that issues the SELECT statement
just like you do in psql.

Kris Jurka