Re: PreparedStatement cache and dynamic queries

Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> On 1-Mar-08, at 11:00 AM, Ken Johanson wrote:
>> 4) They want parameter escaping but in the context of lists:
>> WHERE foo IN ('a','b','c',...dynamic list).
>> Is this possible in a database neutral way?
>>
>> String[] ar = new String[]{"a","b"};
>> "WHERE foo IN ?",
>> ps.setObject(1,ar,Types.ARRAY);
>>
>> (I've never tried this snippet but presume it won't work due to the
>> zero-len case which should failfast according to sql, I believe)
>>
> As far as I know this won't work

Since about 8.2 the backend has been able to cater to this type of thing
using "scalar = ANY(array)" syntax: that does support pushing the whole
array through as one parameter and it doesn't fail for the zero-elements
case. It won't work with IN though; AFAICT the code snippet shown above
is a flat violation of the SQL spec. (The ANY(array) business isn't in
the SQL spec either, but since the spec doesn't assign a meaning to ANY
except with a subquery as argument, we felt we could get away with this
extension.)

Since this behavior is nowhere to be found in the spec, imagining that
you can have it in a "database neutral way" is sheer fantasy. Any DB
that can do it at all will have made their own choices about how to
shoehorn it into SQL syntax.

regards, tom lane