Thread: jdbc refuses to pass array argument using ARRAY[...] syntax
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
On Sat, 7 Feb 2009, Hannu Krosing wrote:
> org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
> ERROR: array value must start with "{" or dimension information
This is a server error message, not a JDBC driver message.
> 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(# ] )
Since you're using a prepared statement, this isn't the same thing. The
equivalent is really:
PREPARE myplan ( mytype[] ) AS SELECT * FROM
mytypearraryfunc($1);
EXECUTE myplan ( 'ARRAY[..]'::mytype[] );
ARRAY is a grammar construct and cannot be part of the parameter.
Kris Jurka
On Sat, 2009-02-07 at 11:28 -0500, Kris Jurka wrote:
>
> On Sat, 7 Feb 2009, Hannu Krosing wrote:
>
> > org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
> > ERROR: array value must start with "{" or dimension information
>
> This is a server error message, not a JDBC driver message.
>
> > 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(# ] )
>
> Since you're using a prepared statement, this isn't the same thing. The
> equivalent is really:
>
> PREPARE myplan ( mytype[] ) AS SELECT * FROM
> mytypearraryfunc($1);
>
> EXECUTE myplan ( 'ARRAY[..]'::mytype[] );
>
> ARRAY is a grammar construct and cannot be part of the parameter.
weird, since this works too
m3=# prepare myplan(mytype[]) as SELECT * FROM mytypearraryfunc(1,$1);
PREPARE
m3=# execute myplan(ARRAY[
m3(# ROW(10, '{1,2,3}','{7,8,9}')::mytype,
m3(# ROW(11, '{1}','{3,4,5,6,7}')::mytype,
m3(# ROW(10, '{}','{7}')::mytype
m3(# ]);
status | file_id | delete_heirs | add_heirs
--------+---------+--------------+-------------
200 | 10 | {1,2,3} | {7,8,9}
200 | 11 | {1} | {3,4,5,6,7}
200 | 10 | {} | {7}
(3 rows)
m3=#
> Kris Jurka
--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Sat, 2009-02-07 at 23:57 +0200, Hannu Krosing wrote:
> On Sat, 2009-02-07 at 11:28 -0500, Kris Jurka wrote:
> >
> > On Sat, 7 Feb 2009, Hannu Krosing wrote:
> >
> > > org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
> > > ERROR: array value must start with "{" or dimension information
> >
> > This is a server error message, not a JDBC driver message.
> >
> > > 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(# ] )
> >
> > Since you're using a prepared statement, this isn't the same thing. The
> > equivalent is really:
> >
> > PREPARE myplan ( mytype[] ) AS SELECT * FROM
> > mytypearraryfunc($1);
> >
> > EXECUTE myplan ( 'ARRAY[..]'::mytype[] );
> >
> > ARRAY is a grammar construct and cannot be part of the parameter.
>
> weird, since this works too
>
> m3=# prepare myplan(mytype[]) as SELECT * FROM mytypearraryfunc(1,$1);
> PREPARE
> m3=# execute myplan(ARRAY[
> m3(# ROW(10, '{1,2,3}','{7,8,9}')::mytype,
> m3(# ROW(11, '{1}','{3,4,5,6,7}')::mytype,
> m3(# ROW(10, '{}','{7}')::mytype
> m3(# ]);
> status | file_id | delete_heirs | add_heirs
> --------+---------+--------------+-------------
> 200 | 10 | {1,2,3} | {7,8,9}
> 200 | 11 | {1} | {3,4,5,6,7}
> 200 | 10 | {} | {7}
> (3 rows)
> m3=#
Oh, did not notice the '' around :
so the right way to do it would be
m3=# execute myplan(E'{"(10,\\"{1,2,3}\\",\\"{7,8,9}\\")","(11,{1},\
\\"{3,4,5,6,7}\\")","(10,{},{7})"}'::inheritance_change_type[]);
status | file_id | delete_heirs | add_heirs
--------+---------+--------------+-------------
200 | 10 | {1,2,3} | {7,8,9}
200 | 11 | {1} | {3,4,5,6,7}
200 | 10 | {} | {7}
(3 rows)
Thanks :)
--
> ------------------------------------------
> Hannu Krosing http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
> Services, Consulting and Training
>
>
--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training