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

Re: selecting using array as IN condition parameter - possible?

From: John Lister <john(dot)lister(at)kickstone(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: selecting using array as IN condition parameter - possible?
Date: 2010-09-29 17:58:36
Message-ID: 4CA37E4C.10306@kickstone.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
  Hi, after some more searching on the net I came across references to 
PGobject and got this working using standard JDBC calls which is a good 
start, I don't suppose any one has any hints about using this in JPA 
queries under eclipselink? I've created a converter for the single case, 
but i get errors when I try and use an array.

If I try and receive an array I get a not implemented error on 
getArrayImpl due to the use of a Map. Conversely if I construct an array 
of my PGobjects subclasses and try to pass that as a parameter to a 
query I get an exception that setObject can't infer the sql type needed 
and to provide an explicit one. I was hoping that as PGobject has a 
getType method that it would be used?

Is it possible to specify the type when passing an array using JPA?

Thanks

John


On 29/09/2010 13:59, John Lister wrote:
> Hi, suppose I have a query such as
> select * from blah where (a,b) in ( (1,2),(2,3),(3,4)...)
> I'd like to do this using jdbc and use a list/array for the IN parameter.
> using postgresql 8.3, it is possible to rewrite it so that I am 
> passing in an array instead of a hardcoded list as so
> select * from blah where (a, b)::myPairType = any ( array of myPairType)
> provided I've created a type (myPairType) to represent my data pair 
> and a comparison operator for the new data type.
> My question is therefore is it possible to pass an array of this type 
> using jdbc or alternatively pass an array of arrays to represent my 
> pair and convert that in the sql?
> Thanks
> John
> --
> Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

pgsql-jdbc by date

Next:From: Mark KirkwoodDate: 2010-09-29 21:30:51
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: Craig RingerDate: 2010-09-29 13:57:28
Subject: Re: Loading a dump containing include commands

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