4 ms·
Which db is that? Just curious, the few I have used take (?,?)
by rileymat2 5y ago
Which db is that? Just curious, the few I have used take (?,?)
- tehbeard 5y agoI 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.
- chopin 5y agoI'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).
- magicalhippo 5y agoSure 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.
- rileymat2 5y ago100% agree, it is ugly. I misunderstood thinking it would not allow them in the “in” statement.
- NackerHughes 5y agoWhy on earth would you need more than 2000 parameters?
- magicalhippo 5y agoThe 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.
- alexvoda 5y agoWhy not pass that as json or xml and then join the data?
- magicalhippo 5y agoFor 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.