Re: IN clauses via setObject(Collection) [Was: Re: Prepared Statements]

On Mon, Jul 21, 2003 at 01:51:41PM -0400, Fernando Nasser wrote:
> Oliver Jowett wrote:> On Tue, Jul 22, 2003 at 03:47:49AM +1200, Oliver
> Jowett wrote:

> >Also.. what would we do with this object?
> >
> >public class AnnoyingObject implements java.util.Collection,
> >java.sql.Array {
> >  // ...
> >}
> >
> >then setObject(n, new AnnoyingObject(), Types.ARRAY);
> >
> >Is that an Array, or an IN clause of Arrays? :)
> >
> >(Array is the obvious candidate for also being a Collection, but
> >potentially
> >you could do it with other types too)
> >
>
> I am not sure if this is an useful or usual Java class at all, but if you
> want to pass a list of this AnnoyingObject you can always create a
> Collection of such objects (like an ArrayList).

Um, no, that's not my point.

Consider this (more realistic) example:

  public class MutableArray extends ArrayList implements java.sql.Array {
     // implementation of java.sql.Array in terms of ArrayList methods
  }

  MutableArray myarray = new MutableArray();
  myarray.add("abcd");
  myarray.add("efgh");

  stmt.setArray(1, myarray);  // This sets param 1 as an array of strings
  stmt.setObject(1, myarray); // but what does this do?
  stmt.setObject(1, myarray, Types.ARRAY);   // and this?
  stmt.setObject(1, myarray, Types.VARCHAR); // and this?

Yes, you can avoid this by using composition not inheritance. But this is a
very fragile and nonobvious way for setObject to behave. Adding an extra,
commonly used, non-JDBC, non-Postgresql interface to an existing class
shouldn't cause large changes to how the driver treats it!

> With setObject, if the specified type is Array and the passed type is an
> Array of some sort we have to honor that.  So, in the obscure case where
> someone wants to set a list of Arrays they will have to add the Arrays to a
> Collection (that is not an Array itself).

So the extension becomes "If you pass a Collection.. unless the Collection
is also an Array and you specify Types.ARRAY.. or it's also a Blob and you
specify Types.BLOB.. or ...". This is getting messy.

> Also, we may limit this behavior with Collections to the IN clause only.
> Where else could we need lists to replace the '?' ?

Ideally, we want an interface where the API user can provide the JDBC driver
with enough information to say "this is definitely an IN clause parameter"
without having to inspect the query. Otherwise you have the situation where
the same setObject() call expands differently depending on parameter context,
which is pretty nasty.

-O