Re: the IN clause saga - Mailing list pgsql-jdbc

>
>
>Here are the permutations I can remember:
>
>Option 1: add a method to PGStatement that explicitly sets an IN clause,
>  taking either a java.sql.Array, java.util.Collection + component type,
>  array + component type, or a custom postgresql object
>
>  + there's no confusion as to what it means
>  + using a custom object allows access via setObject(..., Types.OTHER)
>    consistently, as well as via the extension method.
>
it doesn't (at least, not in the current implementation) - Types.OTHER
ends up calling setString(), that makes it useless for IN parameters

>  - java.sql.Array and java.util.Collection have problems as PGStatement is
>    compiled for all JDKs and JDBC versions and those types may not be present
>    (we could do a PGJDBC2Statement or something, but that's getting messy)
>
you could declare it to take Object, I suppose (that would be the only
way anyway if you wanted to support arrays of primitive types anyway)

>  - have to downcast to a PGStatement to use it
>
>
>
>
>Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>
>  + no new methods or types needed
>  - setArray() behaves differently depending on query context
>  - user has to wrap the underlying array in a java.sql.Array
>
>Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>
>  + no new methods or types needed
>  - must assume that the contents of the collection use the default type mapping
>    if a type is not provided
>
You can require the type to be provided.

>  - if a type is provided and we apply it to the *components* of the
>    collection, this breaks the general getObject() interface of "bind this
>    object interpreting it as this particular type".
>  - not obvious what to do with objects that are both Collections and some
>    other SQL-relevant type; solutions make setObject's behaviour complex
>    and/or query-context-dependent
>
>

>Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>   java.util.Collection
>
>  + as 3, but the ambiguity of "object is both Collection and SQL type X"
>    goes away.
>
>Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>  in this way.
>
>  + no issues with server-side prepare
>  - obviously, you can't set IN clauses in one go any more.
>
>1-4 all need to disable server-side prepare when used.
>
>Did I miss anything? My personal order of preference is 1-2-4-5-3.
>
For what it's worth, mine is 3-4-1,2,5  (commas meaning that the last
three seem equally useless).

Dima

> I have a
>partial implementation of 2 written but it's easy to adapt that to whatever
>external interface.
>
>setArray() needs fixing regardless of what happens here. I hope to have a
>patch for that ready later today.
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>