Обсуждение: Callable statements and rowsets

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.

I've got a C-function that does a rather large calculation on the
backend and returns a table of results.

CREATE TYPE correlation_type AS (
        fund_id INTEGER,
        num_points INTEGER,
        correlation FLOAT8
);

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;

 From psql this works great and returns me a table (with 20 rows) with 3
results per row.

Unfortunately I'm not able to execute the same query through jdbc. I've
tried to follow the examples in section 31.5.2 of the manual, but they
are a tad concise. What I have come up with is

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

which does not work. First I get an exception that the first output
parameter is of type integer, not other. Fair enough, but I cannot have
multiple return values. Debugger tells me the integer has value 56 - no
idea where that comes from, as it is not the number of expected rows,
nor the first returned value.

So, is it possible to get the results returned in this way? What am I
doing wrong?

Thanks,

Adriaan

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