Fork me on GitHub
#sql
<
2018-11-15
>
peterwestmacott17:11:27

is it possible with clojure.java.jdbc to stream data into the database?

seancorfield17:11:58

@peterwestmacott Not quite sure what you mean... could you elaborate?

peterwestmacott17:11:39

I can use eg. reducible-query to run a select statement and put each of the values onto eg. a manifold stream or core.async channel

peterwestmacott17:11:11

if I have a stream or channel of values can I combine those with an insert statement to stream the rows into a database table?

peterwestmacott17:11:20

I can fallback to executing the same insert statement multiple times, each with a batch of rows

peterwestmacott17:11:44

…but I wondered if maybe there was a better way

seancorfield17:11:31

SQL/JDBC isn't really a "streaming" target so I'm not sure how you could do any better than just a loop reading from a channel and calling insert!...

seancorfield17:11:52

...if you do think of anything, please report back. It's an interesting question.

peterwestmacott17:11:19

it seems asymmetrical to be able to execute one SQL query and get an ongoing sequence of data, but not be able to take an ongoing sequence of data and insert it back into the DB

peterwestmacott17:11:47

but I guess the use-case for writing like that is rarer

seancorfield17:11:21

I didn't find a good way to "pour" a result set into a channel either (beyond an explicit reduce that puts values)...

peterwestmacott17:11:00

and if I’m pooling my connections then the temporal overhead of doing multiple batch inserts (eg. insert-multi) probably isn’t so bad

seancorfield17:11:04

For writing to the DB, there are additional concerns, such as transactions, exception handling, etc.

peterwestmacott17:11:33

yes, I would expect to need a long running transaction to attempt such a thing

peterwestmacott17:11:08

thank you for your time, both now and generally in maintaining such a well-depended-upon library!

madstap17:11:15

I'm using postgres with a column of timestamptz type and I have a java.time.ZonedDateTime object in my clojure code, but using an object with that type throws an exception

1. Unhandled org.postgresql.util.PSQLException
   Can't infer the SQL type to use for an instance of
   java.time.ZonedDateTime. Use setObject() with an explicit Types
   value to specify the type to use.
How do I use this setObject()? Or should I convert to java.sql.Timestamp first (which appears to work)?

seancorfield17:11:35

@madstap You should convert to java.sql.Timestamp first I think. You can also extend the protocol so the conversion is done automatically.

seancorfield17:11:25

At some point I may extend java.jdbc to do the inbound conversions automatically, but that will require Java 8+...

madstap17:11:21

Which protocol should I extend?