Skip site navigation (1) Skip section navigation (2)

Callable statements and rowsets

From: Adriaan Joubert <a(dot)joubert(at)k2defender(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Callable statements and rowsets
Date: 2004-03-10 10:37:17
Message-ID: 404EEFDD.2070009@k2defender.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: kerdemDate: 2004-03-10 11:16:49
Subject: COPY Command UtfToLocal: could not convert UTF-8
Previous:From: Bjørn T JohansenDate: 2004-03-10 08:46:17
Subject: Upgrading from 7.3.4 til 7.4.x?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group