sql 2022-02-13 | Slack Archive

rmxm00:02:49

what is the recommended way to deal with connection problems? should I wrap my db calls in try and manually perform reconnects or does connection pools(libs) offer that automatically?

seancorfield00:02:59

@roguas Use HikariCP (connection pooling). Always.

seancorfield00:02:08

next.jdbc supports it out of the box.

seancorfield00:02:47

We used c3p0 at work for years and it was fine. We just switched to HikariCP and it was painless and it much better maintained, documented, and supported.

seancorfield00:02:14

Create a connection pooled datasource at app startup. Pass it to every next.jdbc call. End of story.

rmxm01:02:27

thanks, exactly the info i wanted 🙂

orlandow01:02:52

Hello > What does this mean for your use of `next.jdbc`? In `plan`, `execute!`, and `execute-one!`, you can use `col = ANY(?)` in the SQL string and a single primitive array parameter, such as `(int-array [1 2 3 4])`. That means that in `next.jdbc.sql`’s functions that take a where clause (`find-by-keys`, `update!`, and `delete!`) you can specify `["col = ANY(?)" (int-array data)]` for what would be a `col IN (?,?,?,,,?)` where clause for other databases and require multiple values. Is there an equivalent to this for SqlServer? I found https://github.com/microsoft/mssql-jdbc/issues/972 but it was rejected. Is there anything better than creating the (?,?,?,,,?) section in the query? Thanks in advance

isak15:02:50

#Also sent to the channel

Yea, you just use https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15. Here is an example:

(defn select-objects-by-ids [conn]
    (next.jdbc/execute!
      conn
      ["select * 
        from sys.objects
        where object_id in (
          select id 
          from openjson(?) with (id int '$')
       )"
       (cheshire.core/generate-string [1 2 3])]))

seancorfield01:02:16

@orlando No idea about SQL Server -- never used it -- but databases vary a huge amount and JDBC is a very minimal commonality.

👍 1

isak15:02:50

replied to a thread:Hello > What does this mean for your use of `next.jdbc`? In `plan`, `execute!`, and `execute-one!`, you can use `col = ANY(?)` in the SQL string and a single primitive array parameter, such as `(int-array [1 2 3 4])`. That means that in `next.jdbc.sql`’s functions that take a where clause (`find-by-keys`, `update!`, and `delete!`) you can specify `["col = ANY(?)" (int-array data)]` for what would be a `col IN (?,?,?,,,?)` where clause for other databases and require multiple values. Is there an equivalent to this for SqlServer? I found https://github.com/microsoft/mssql-jdbc/issues/972 but it was rejected. Is there anything better than creating the `(?,?,?,,,?)` section in the query? Thanks in advance

Yea, you just use https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15. Here is an example:

(defn select-objects-by-ids [conn]
    (next.jdbc/execute!
      conn
      ["select * 
        from sys.objects
        where object_id in (
          select id 
          from openjson(?) with (id int '$')
       )"
       (cheshire.core/generate-string [1 2 3])]))

2022-02-13

Channels