Fork me on GitHub
#sql
<
2021-08-16
>
wilkerlucio18:08:17

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

emccue18:08:36

depends on your jdbc driver

emccue18:08:07

with some you can do

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

emccue18:08:14

and the jvm array will be coerced

emccue18:08:02

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

emccue18:08:10

or explicitly passing some other objeect

emccue18:08:02

the most transportable way is to make your queries with honeysql

emccue18:08:44

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

emccue18:08:11

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

seancorfield18:08:45

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).

wilkerlucio18:08:36

@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)

wilkerlucio18:08:53

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

emccue18:08:33

yeah ANY(?) and a standard array

Darin Douglass18:08:22

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

wilkerlucio18:08:06

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

emccue18:08:28

maybe extend settable parameter to uuid - that seems normal

emccue18:08:34

i see some examples of doing a kind of conversion

emccue18:08:39

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

wilkerlucio19:08:15

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