Re: What do people use for connection pooling with PostgreSQL JDBC

I think you can extract the underlying PGConnection object by calling conn.getInnerMostDelegate() (sp?) method and then do your array stuff. Your dbcp datasource has to allow access to underlying JDBC objects however (there was a property to allow this).

From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
Sent: Tuesday, April 07, 2009 1:24 PM
To: Hannu Krosing
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] What do people use for connection pooling with PostgreSQL JDBC

On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:

On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:
> huh ? What makes you think it doesn't deal with arrays ?

I have a sample function, whit takes an array argument

CREATE OR REPLACE FUNCTION arraySum(
multiplier bigint,
valuelist bigint[],
OUT result int
) AS
$$
BEGIN
result = 0;
FOR idx IN array_lower(valuelist, 1)..array_upper(valuelist, 1) LOOP
result = result + multiplier * valuelist[idx];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

When I call this function directly via JDBC or using proxool I get a
result, when doing it via c3p0 or dbcp I get an exception -

Long[] array = { 1L, 2L };
PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM arraySum(?, ?)");
ps.setLong(1, 3L);
ps.setArray(2, conn.createArrayOf("bigint", array));
ps.execute();

OK, createArrayOf is a relatively new method. So .... you have a few ways around this.

1) don't use it, create your own arrays.
2) fix the dbcp code
3) complain to dbcp people

--

Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training