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

Re: Callable statements and rowsets

From: Kris Jurka <books(at)ejurka(dot)com>
To: Adriaan Joubert <a(dot)joubert(at)k2defender(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Callable statements and rowsets
Date: 2004-03-14 03:04:33
Message-ID: Pine.BSO.4.56.0403132202090.491@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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


In response to

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-03-14 03:09:22
Subject: Re: About using drivers....
Previous:From: Barry LindDate: 2004-03-11 17:03:14
Subject: Re: updateable resultset only working for tables?

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