Hacker News Reader

Comment by magicalhippo | Hacker News Reader

At least our db doesn't support parameterized lists for use with IN operator. Not often I need this but when I do I'm glad our db layer has macros for SQL with automatic string escaping.

View on HN

Which db is that? Just curious, the few I have used take (?,?)

I assume they're on about an unbounded, pass array in situation (?) rather than having to dynamically build that part of the query (?,?,?...).

Done both, manually doing it looks messy, and higher chance of typos.

I've done the latter, in JDBC and I built the (?,?,...) part programmatically. However you have to be aware how many ?s are elsewhere in the PreparedStatement such that filling the query with the array contents works correctly. It'd be a much better design decision if JDBC actually supported named parameters (as JPQL does).

Sure but it also has a limitation of ~2000 parameters. And it's kind of a PITA to dynamically generate and set the parameters compared to just setting one parameter.

Sybase SQLAnywhere btw.

100% agree, it is ugly. I misunderstood thinking it would not allow them in the “in” statement.

Why on earth would you need more than 2000 parameters?

The list I pass to IN can have than 2000 entries, and if I were to use parameters and my database doesn't support array parameter as mentioned...

As for why more than 2000 entries in a IN list, well, some customers generate a fair bit of data, and our program allows for bulk updating via pasting from Excel or similarly tabulated data. Depending on the case, it's necessary to grab all the entries from the db and work on them as a batch, rather than one-by-one.

Why not pass that as json or xml and then join the data?

For our DB that would require a temporary table, in which case we could just do a LOAD TABLE statement. But we do do that as well, but it's a lot more overhead than just setting a single parameter, or macro when using IN.

Reply on news.ycombinator.com