Dan Cooperstock wrote:
> I want to be able to call setArray on a
> PreparedStatement to set the value of a prepared
> parameter to an array value, for instance if the
> SQL is something like:
>
> SELECT * from table WHERE column IN (?)
Dan,
java.sql.Array is for the _SQL_ array type (i.e. ARRAY OF [....], which becomes a _type_ of its own).
As far as I know, java.sql.Array can't be used in the fashion you propose to use it. Even if you _could_ use it that way, the MySQL server doesn't yet support the type, so there's really no way the JDBC driver could support it currently.
I'd be curious to know what vendors allow you to use it to bind to a multi-value list for prepared statement parameters, though (if any).
The cross-vendor workaround I've seen most people use is to repeat the parameter marker as many times as necessary to hold the largest "array" the application will need (or have a few prepared statements in the same manner with varying numbers of markers), and then set the "leftover" parameters to something that is guaranteed to not match the IN clause. Most optimizers deal fine with this.
Regards,
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html