Re: JDBC calling PL/pgSQL with array parameter

From: Kris Jurka <books(at)ejurka(dot)com>
To: "enishiseki(at)juno(dot)com" <enishiseki(at)juno(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: JDBC calling PL/pgSQL with array parameter
Date: 2004-11-17 02:45:54
Message-ID: Pine.BSO.4.56.0411162138570.11329@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

On Tue, 16 Nov 2004, enishiseki(at)juno(dot)com wrote:

> I'm trying to call a PL/pgSQL function from a Java program using
> JDBC. The function takes an integer array as an input parameter,
> sums the elements of the array, and returns the sum.
>
> I can successfully call the function from psql as:
> SELECT testit('{1,2,3}')
>
> int [] intArray = {1,2,3};
> stmt.setObject(2, intArray );
>
> When I try to run the program, I get the error,
> "missing dimension value".
>

The postgresql jdbc driver does not have support for using setOjbect with
array types. If you enable statement logging you will see something like
the following command on the backend side:

SELECT testarrfunc('[I(at)fd13b5')

If you use the 8.0 driver you will get a better error message:

Exception in thread "main" org.postgresql.util.PSQLException: Can't infer
the SQL type to use for an instance of [I. Use setObject() with an
explicit Types value to specify the type to use.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1594)

Using the 7.4 driver you can do a setString(x, "{1,2,3}"), but this won't
work with the 8.0 driver, for that you need a real java.sql.Array
implementation.

Kris Jurka

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Guido Fiala 2004-11-17 07:36:04 Re: Why is PGStream.flush() taking so much time?
Previous Message Oliver Jowett 2004-11-17 00:00:31 Re: Why is PGStream.flush() taking so much time?