Clojurians Log v2

[next.jdbc] Is it safe to keep all the options [for connections, prepared statements, result sets etc.] in a single map or we should separate them?

Trying to understand the difference between these two sql statements (using clojure.java.jdbc) .

(j/execute! redshift ["create table dev.jimmy_test_networks (like names.networks);
insert into dev.jimmy_test_networks values (1, 'test', 'test', 0, null);
drop table dev.jimmy_test_networks;"] )

;; Errors with [Amazon](500310) Invalid operation: relation "dev.jimmy_test_networks" does not exist;

(j/with-db-transaction [tr redshift]
  (j/execute! tr ["create table dev.jimmy_test_networks (like names.networks)"])
  (j/execute! tr ["insert into dev.jimmy_test_networks values (1, 'test', 'test', 0, null);"])
  (j/execute! tr ["drop table dev.jimmy_test_networks "]))

;; Succeeds

(coworker suggested db-do-commands which might be the right thing, just want to understand the behavior)

First off, not all databases accept multiple statements in a single JDBC operation, and even when they do, the semantics may differ across databases. What I think is happening here is that you're mixing DDL and SQL but the changes are not committed until the end of the operation so your SQL fails because the DDL hasn't actually been committed. This may be made worse in your case due to clojure.java.jdbc attempting to wrap each call in an implicit transaction. In the separate three calls, the DDL is being committed (since it is in its own JDBC call) before the insert -- even though you've wrapped the three calls in a transaction anyway. SQL/JDBC can be weird like that 😞

Hmmm interesting. Doing this via db-do-commands makes this work whether it is all one big string or a vector of separate statements. Which actually throws me off even more. Any lower level tools I could use to test your theory above? Like ways to get rid of the implicit transactions and see what happens.

execute! and db-do-commands use different JDBC APIs behind the scenes. The latter is intended for DDL operations.

In next.jdbc, this is all streamlined and a single JDBC API is used which solves most of these problems. In addition, it does not try to wrap operations in transactions (which is a cause of some strange behaviors in clojure.java.jdbc).

Thanks definitely helps me out and makes me feel a bit better about db-do-commands being the right way to do things.

I see Jimmy beat me to asking this question, but I’ll clarify one thing, the first expression had multiple SQL statements, but they were wrapped in a BEGIN .. END like below, which should be treated as single statement in a single transaction afaik. That’s how it seems to work in standalone SQL editor at least

(j/execute! redshift ["BEGIN;
create table dev.jimmy_test_networks (like names.networks);
insert into dev.jimmy_test_networks values (1, 'test', 'test', 0, null);
drop table dev.jimmy_test_networks;
END;"] )

Also, I’m really surprised that some execute! statements can be committed separately even if they’re wrapped in single with-db-transaction . Do you mean they’d be visible from outside that wrapping transaction?

@grzegorz.caban Just to clarify: some DBs auto-commit DDL statements.

The TL;DR is that this sort of thing is very DB-specific (and even JDBC-driver-specific)...

I see what you mean and thanks for confirming that db-do-commands is the way to go. That solved the problem