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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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