Fork me on GitHub
#sql
<
2019-06-26
>
danielcompton00:06:01

Is there a way to enforce that functions are part of a transaction, without creating a separate sub transaction? I have a function which makes three database inserts that need to part of the same transaction. This function could be used on its own, or as part of a larger function. In both cases a transaction needs to be used. What I'm looking for is something like with-db-transaction-if-not-already-in-a-transaction, but I'm not sure if that kind of thing exists and would work with clojure.java.jdbc?

danielcompton00:06:29

OTOH, maybe it doesn't really matter with Postgres, as sub-transactions aren't supported anyway: https://dba.stackexchange.com/a/137967

danielcompton00:06:28

Actually, they are sort of supported in 11 but only inside stored procedures https://dba.stackexchange.com/a/118419

seancorfield00:06:27

@danielcompton In clojure.java.jdbc, if you try to nest transactions, the inner one is a no-op and the outer one is used.

seancorfield00:06:46

So I think the behavior you want is actually the default behavior 🙂

seancorfield00:06:48

(and the behavior should be the same in next.jdbc BTW)

danielcompton00:06:45

Heh, I just saw that too when I went to read the docstring of db-transaction*. I came back here to doublecheck I was reading it right and saw your response. Thanks!

jjttjj13:06:18

is there a way to disable the use of qualified keywords in jdbc.next?

Jakub Holý (HolyJak)07:07:09

I cannot answer that but a possible workaround: replace SELECT * from MYTABLE with SELECT * from MYTABLE t - I believe I have experienced that when I alias the tables, the keys returned are unqualified.

credulous15:06:41

Hi! I had some issues yesterday correctly using next.jdbc, which I got by with some help. Thanks! On to the next puzzling thing. 🙂 I have this execute function:

credulous15:06:48

(defn execute!
  [sqlmap]
  (let [q (honey/format sqlmap)]
    (try 
      (log/info "Executing sql against datasource" @datasource)
      (jdbc/execute! @datasource q) 
      (catch Exception e
        (log/error (exceptions/get-stacktrace e))        
        (throw (ex-info "Exception in execute!" {:sqlmap sqlmap :query q}))))))

credulous15:06:39

It works without complaint on localhost against my production database. On my production server, it also works (I get the correct output in the api route that is calling this function), but it throws an exception:

mtbkapp16:06:14

@jjttjj you can by providing a :builder-fn option like this: (next.jdbc/execute! conn [your-query] {:builder-fn next.jdbc.result-set/as-unqualified-maps}). I'm not sure if there is a way to change the default builder-fn.

👍 4
dbernal16:06:09

is there a way to have HoneySQL not lowercase the column names coming back?

seancorfield16:06:21

At this point, in Clojure's overall development, and with Spec so prominent, I strongly recommend at least trying to work with the qualified column keys and adapting your coding style to use qualified keywords.

jjttjj16:06:22

great, thanks!

seancorfield16:06:36

(it's very deliberate that you can't change the default 🙂 )

gklijs17:06:37

I think that was a good decision. Really hope spec will stabilize soon, and most libraries will use namespaced maps.

seancorfield17:06:27

We've been using Spec (1) in production ever since it went alpha and we have a branch running Spec 2, ready to switch over. So "stabilize" is subjective 🙂

jjttjj16:06:58

yes, definitely a long term goal, just wanted to do something quick and dirty on something with pretty long table name

seancorfield16:06:59

@dbernal HoneySQL does not convert anything to lower case

user=> (-> (select :a :B :mixedCase :CamelCase) (from :tableName) (h/format))
["SELECT a, B, mixedCase, CamelCase FROM tableName"]

seancorfield16:06:00

If you're using clojure.java.jdbc, it converts column names to lowercase. You can pass :identifiers identity to suppress that behavior (and in clojure.java.jdbc you can often put that in your db-spec and it will act as a default for all operations).

seancorfield16:06:42

(and, as you can see from my next.jdbc comment above, I consider that a bad default these days -- but changing it would be breaking for a lot of code)

dbernal17:06:40

Awesome! Thank you

seancorfield16:06:21

@credulous That seems to be very Azure-specific (and it's only a warning in the log, right? Everything actually works, yes?)

credulous16:06:46

@seancorfield Yup, everything works.

credulous16:06:24

Happy to ignore stuff like that if I know it’s OK to ignore. 🙂

seancorfield16:06:50

You might want to check the Azure docs and see if they expect you to provide additional configuration options on DB connections?

credulous16:06:47

that’s a good idea. How did you figure out it’s azure specific? (for my own edification)

seancorfield16:06:50

Because it's happening at the SSL level, below the database driver.

seancorfield16:06:47

The JDBC driver is closing the connection, as expected, and the exception is coming from the java.base/sun.security.ssl.SSLSocketImpl

seancorfield16:06:35

If you use a connection pool, I'd expect that to go away, or at least minimize the occurrences.

seancorfield16:06:06

Also, I'd strongly recommend using Hikari or c3p0 to build a connection-pooled datasource when your program starts up and use that (as your @datasource) since it will be much faster overall -- opening/closing connections is slow, especially over a remote network like that.

4
credulous16:06:16

Thanks. I had Hikari in there but commented it out for now to try to tackle one problem at a time