Clojurians Log v2

Hi! I'm using next.jdbc, how can I do something like that? (delete! ds :users ["email IN ?" a-set-of-mine]) How do I use placeholders with the IN keyword?

(delete! ds :users ["email IN (?, ?)" "a@com" "b@com"])

The code to programmatically build such a query from a collection can be prone to injection. I would highly recommend using honeysql. It supports adding in clauses that take a collection as the parameter and builds the expected query for you

Also depends on your database driver

like, I think the postgres driver lets you use "IN" with a primitive array

Yes, there’s even an example of that last suggestion in the docs.

I'm probably overlooking something obvious, is there a way to create a database with jdbc-next?

(with-open [conn (pg-autoconn/conn! :postgres-config)]
    (jdbc/execute-one! conn [create-db-cmd] ))

1. Unhandled org.postgresql.util.PSQLException
   ERROR: CREATE DATABASE cannot run inside a transaction block

(jdbc/execute-one!
   (doto (jdbc/get-connection (pg-autoconn/auto-conn-creds! "postgres")
                              {:auto-commit true, :read-only false})
     (.setCatalog "postgres"))
   [create-db-cmd])

it was :auto-commit false and the with-open, I think, that was messing me up

@goomba Unlike clojure.java.jdbc, next.jdbc does not create any implicit transactions. However, different databases have different rules about how DDL interacts with the auto-commit status on a connection.

In your second piece of code, you are leaking a connection so you should use with-open.

(with-open [con (jdbc/get-connection ..)]
  (.setCatalog con "postgres")
  (jdbc/execute-one! con [create-db-cmd]))

I can’t speak to what options you may or may not need to pass to get-connection for this. For MySQL, we do not need to pass any options for CREATE DATABASE.

aha! great catch

works beautifully!

Some databases do not allow DDL inside transactions — but I don’t see a transaction in your first code block so that’s a bit puzzling. But, hey, SQL! amirite?

hahha. the pg-autoconn/auto-conn! calls down to

(doto (jdbc/get-connection datasource {:auto-commit false, :read-only false})
    (.setCatalog "postgres"))

I infer that the .close of the with-open triggered a transaction, perhaps? I have no idea. It works now though. SQL! :party-corgi:

This is from https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#disable_auto_commit When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.) The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection: con.setAutoCommit(false); ... After the auto-commit mode is disabled, no SQL statements are committed until you call the method `commit` explicitly.

make sense. What I'm curious about is how a commit relates to a transaction

So maybe this might work from the first block,

(with-open [conn (pg-autoconn/conn! :postgres-config)]
    (jdbc/execute-one! conn [create-db-cmd] )
    (.commit conn)

Reason being, in postgres, you can't create a database inside of a transaction. That was the stumbling block

So I had to not trigger a transaction, somehow

@kslvsunil The issue here is DDL vs SQL: some databases do not let you run DDL inside a transaction, some databases auto-commit DDL regardless of the settings on the connection — so how SQL interacts with transactions can be different than how DDL interacts with them, unfortunately.

@goomba I would say you don’t need {:auto-commit true :read-only false} if you use jdbc/get-connection since those should be the default. Where is that pg-autoconn/auto-conn! coming from?

(calling it autoconn/`auto-conn!` when it seems to set the connection not auto-commit seems like terrible naming to me!)