Fork me on GitHub
#sql
<
2021-07-26
>
souenzzo02:07:31

do JDBC accept URL's like "" ? If not, there is a library to turn "" into jdbc:" ?

seancorfield03:07:13

It should have jdbc: on the front but, yes, next.jdbc accepts that.

seancorfield03:07:55

Pretty sure there are examples in the docs but I'm not at my computer right now @souenzzo

zackteo09:07:18

How does jdbc handle execute! on lazy sequences? From debugging it seems I might need to force evaluation of my data

zackteo09:07:55

okay ^ not certain of the issue but into worked. Might be simply because I needed it in a vector

seancorfield14:07:04

@zackteo Not sure what you mean. next.jdbc/execute! should produce a vector by default and it is not lazy (laziness doesn't enter into it). Are you asking about passing sequences into execute!? Are you still talking about JSON, and you are asking about passing in a lazy sequence as JSON? Could you show some code?

zackteo15:07:06

Actually now that I look at it again it should almost definitely me about the need to format it as a vector. Was doing this and I think my result was not a vector. And following your documentation on JDBC on json/jsonb, I do not extend the protocol for a seq which was likely why into worked

(sql/update! ds :results
               {:result result}
               {:id id})

seancorfield16:07:32

Yes, to trigger the auto-conversion, the actual type needs to match (so vector rather than just a sequence).

đź‘Ť 3
markbastian21:07:17

Hey all, trying to get into next.jdbc and was wondering what the fundamental difference is between a connection and a datasource. I see that the next.jdbc ns has both get-connection and get-datasource and it looks like I can invoke execute! on both. Is a datasource just a higher level abstraction on a connection?

markbastian21:07:22

Ah, looks like a Java thing. Found https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html docs. It looks like datasource is preferred. Just for future reference, any reason I’d want a connection or should I generally just use a datasource?

seancorfield21:07:34

You get a DataSource object from a db-spec hash map or URL. You get a Connection from a DataSource. Behind the scenes, next.jdbc can navigate that for you so if you call next.jdbc/execute! with a db-spec hash map, it calls get-datasource and then calls get-connection.

seancorfield21:07:50

Standing up new connections against a database can be expensive so you generally want to reuse connections or pool them. Hence c3p0 and HikariCP support in next.jdbc to produce "pooled connection datasources" which maintain connections behind the scenes you can get-connection and then "close" it generally very quickly.

seancorfield21:07:09

How much background do you have with JDBC stuff, if any, @U0JUR9FPH?

markbastian21:07:50

Right, familiar with connection pooling and cost of lots of connections. Aside from that I really just am more on the “execute the sql” side than anything else. Not at all an expert in the bowels of jdbc.

seancorfield22:07:46

OK. Did my comment above help, about how it's db-spec -> DataSource -> Connection?

markbastian22:07:35

Yes, and the docs https://github.com/seancorfield/next-jdbc#usage also help a lot. One question - is it more performant to follow the pattern in the linked example in the docs to get a datasource (let’s say it’s a connection pool) and then do a with-open with each execute! vs. just passing in the datasource? E.g.

(let [my-datasource (jdbc/get-datasource {:dbtype "..." :dbname "..." ...})]
    (with-open [connection (jdbc/get-connection my-datasource)]
      (jdbc/execute! connection [...])
      (reduce my-fn init-value (jdbc/plan connection [...]))
      (jdbc/execute! connection [...])))
vs.
(let [my-datasource (jdbc/get-datasource {:dbtype "..." :dbname "..." ...})]
    (jdbc/execute! my-datasource [...])
      (reduce my-fn init-value (jdbc/plan my-datasource [...]))
      (jdbc/execute! my-datasource [...]))
I am assuming that reusing the connection within the defined scope is better as the second case would require getting a new connection with each form - or does the pool make that efficient?

seancorfield22:07:40

Reusing a connection is always faster, but with a connection pool it doesn't make a huge amount of difference.

markbastian22:07:15

Cool. Thanks for the help!

seancorfield22:07:45

That's because get-connection is generally just pulling an existing connection out of the pool rather than standing up an entire new connection to the DB -- as long as you have the pool configured reasonably to match your connection load.

đź‘Ť 2