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
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 |