| 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: | Whole Thread | Raw Message | 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
| 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 |