Re: JDBC calling PL/pgSQL with array parameter

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