jdbc refuses to pass array argument using ARRAY[...] syntax

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: jdbc refuses to pass array argument using ARRAY[...] syntax
Date: 2009-02-07 16:13:55
Message-ID: 1234023235.9180.19.camel@huvostro
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hallo,

I am trying to get JDBC to pass an array of user_defined types to a
pl/pgsql function, via a preparedStatement and setObject()

function is defined thus:

CREATE FUNCTION mytypearrayfunc( long id_in, mytype[] typaarray_in)
RETURNS SETOF RECORD AS $$
...
$$ LANGUAGE plpgsql ;

And I did define my own list_of_objects class

public class MyTypeList extends PGobject
{

...

public MyTypeList()
{
setType("_mytype");
}
...
public getValue()
{
...
}
}

which returns representation in form

ARRAY[
ROW(7,'{5,6,7}','{4}')::mytype,
ROW(2,'{2}','{3,4}')::mytype,
ROW(1,'{1}','{}')::mytype
]

which is absolutely OK if I pass it to function in plpgsql but java gets
paranoid and demands me to start ARRAY[] argument with "{" :

Traceback (innermost last):
File "<console>", line 1, in ?
File "/home/hannu/work/M1/javatest/jythontest_func.py", line 62, in ?
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
at java.lang.reflect.Method.invoke(libgcj.so.90)

org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
ERROR: array value must start with "{" or dimension information

Can anyone point me where to look for this check ?

grepping for the error message in driver source gives me nothing ,
probably it is some internationalised string that is not present in
source ?

and yes, the full query string returned from myPrepared
Statement.toString() is valid SQL and does produce desired results when
I paste it in psql, so the problem is very likely overly paranoid checks
in PG jdbc driver.

m3=# select * from mytypearrayfunc( 1, ARRAY[
m3(# ROW(7,'{5,6,7}','{4}')::mytype,
m3(# ROW(2,'{2}','{3,4}')::mytype,
m3(# ROW(1,'{1}','{}')::mytype
m3(# ] )
m3-# ;
status | my_id | friends | foes
--------+-------+--------------+-----------
200 | 7 | {5,6,7} | {4}
200 | 2 | {2} | {3,4}
200 | 1 | {1} | {}
(3 rows)

and I get different error message when i do setType("somethingelse"); so
most of the PGObject machinery is working.

If really needed, I can prepare a full sample code ( a shell script, a
jython file, a java file and an sql file) but hopefully someone can tell
me the location of this check right away.

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2009-02-07 16:28:48 Re: jdbc refuses to pass array argument using ARRAY[...] syntax
Previous Message Kris Jurka 2009-02-06 22:24:29 Re: Need help installing/setting up JDBC on Solaris 10