hello folks, when using seancorfield/next.jdbc, how do you make a parameterized query that uses IN?

depends on your jdbc driver

with some you can do

["SELECT * FROM table WHERE table_id IN ?" (into-array [1 2 3])]

and the jvm array will be coerced

with others you need to do it explicitly for the array conversion - maybe by extending settable parameter to vectors

or explicitly passing some other objeect

the most transportable way is to make your queries with honeysql

basically - if we have arrays in your db then the tips here apply

https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.689/doc/getting-started/tips-tricks

otherwise you need to produce the IN (?, ?, ?, ?, ...) - probably via generating the query with honeysql

As some background, it's probably worth pointing out that next.jdbc (and clojure.java.jdbc before it) are thin wrappers over JDBC: you give it SQL and parameters, it hands it off to JDBC and gives you back results (either as Clojure data or as a reducible). There's no "formatting" of SQL and no translation beyond what JDBC itself allows. HoneySQL, on the other hand, is all about generating SQL but it has no insight into JDBC or SQL data types (or even what constitutes valid SQL).

@emccue I'm using Postgres, and by playgin with HoneySQL on top, I see I would have to manually spread the query to look like ["SELECT * FROM table WHERE table_id IN (?, ?, ?)" 1 2 3)] (this is what I see HoneySQL doing)

humm, but now I note you used a standard array, I haven't tried that

yeah ANY(?) and a standard array

the previous way should work, but it'll run up against PG param count limits (smallint so like 32k or so parameters)

the field type in an UUID, if I sent the items on the list as UUID's it complains, but if I convert to strings it complains for a different reason

maybe extend settable parameter to uuid - that seems normal

https://stackoverflow.com/questions/17969431/postgres-uuid-jdbc-not-working

i see some examples of doing a kind of conversion

PGobject toInsertUUID = new PGobject();
 toInsertUUID.setType("uuid");
 toInsertUUID.setValue(uuid.toString());
 PreparedStmt stmt = conn.prepareStatement(query);
 stmt.setObject(placeHolder,toInsertUUID);
 stmt.execute();

@emccue I found an example that shows we have to use a typed array with the UUID type for that to work