Skip site navigation (1) Skip section navigation (2)

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

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: jdbc refuses to pass array argument using ARRAY[...] syntax
Date: 2009-02-07 22:18:06
Message-ID: 1234045086.6818.34.camel@huvostro (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

pgsql-jdbc by date

Next:From: Lutischán FerencDate: 2009-02-11 15:30:57
Subject: JDBC bug?
Previous:From: Hannu KrosingDate: 2009-02-07 21:57:24
Subject: Re: jdbc refuses to pass array argument using ARRAY[...] syntax

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group