Re: Need java.sql.Array impl for PreparedStatement.setArray()
Posted by: Mark Matthews
Date: June 13, 2005 01:38PM

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 (?)


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.



Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.