The reason we didn't do it this way is that our queries were part of a
batch processing, and the input data was chunked anyway so most of the
times the exact nr. of parameters (the maximum one) was set.
the other advantage was easier audit of the query execution log, having
just 1 query for this operation instead of 100s.
That being said, dynamic queries are more elegant.
Cheers,
Csaba.
On Wed, 2003-02-19 at 19:12, David Wall wrote:
> > You're right... but it's actually based on user interaction (i.e.:
> > selecting items using checkboxes)
> > I guess I'll just have to issue a query per item... ugly :-(
>
> Why can't you simply build the prepared statement on the fly based on the
> number of values to be placed in the IN list? If you have 3 items, you just
> append three '?' and if you have 20 items, then you append 20 '?' to your
> list. This way the number of '?' will match the number of
> PreparedStatement.setXXX() calls and you don't have to rely on the database
> handling all the nulls. Since the list is fairly dynamic anyway, it's
> overall performance will be about the same (and it's much better than doing
> a series of queries instead).
>
> Of course, there are limits on how long a query can be for most databases,
> so you may find that if the list is too long, you'll run into query
> processor problems.
>
> David
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
