Fork me on GitHub
#sql
<
2018-11-14
>
michaellindon00:11:04

im having some difficulties with authentication but i dont think it is anything to do with clojure.java.jdbc - ive had trouble getting other clients to work

michaellindon00:11:18

im going to keep playing around with it

michaellindon00:11:00

in my other client it was necessary to specify the domain

michaellindon00:11:05

i was using datagrip

michaellindon00:11:03

I just added the key :domain with the appropriate value and it works

seancorfield00:11:37

Yup, the db-spec can have pretty much arbitrary keys that correspond to JDBC parameters to be passed in.

💯 4
seancorfield00:11:55

If you run into problems with the jTDS driver, you can always add the MS driver in your dependencies and try :dbtype "mssql" to select that driver at runtime.

seancorfield00:11:12

(see the group/artifact and version in the deps.edn linked above)

seancorfield00:11:38

I test with both drivers, from a Mac, connected to Windows XP running in a VM, with SQL Server Express.

michaellindon00:11:49

thats awesome 🙂

michaellindon00:11:44

i am pulling a rather large table from an mssql database, and inserting it into a table in a postgres database. Reading the docts for insert-multi! suggests that its better to supply the data as columns and not as rows. Is it possible to have the query function already return the data in that format, for which it is more efficient for the insert-multi! function? It looks like query returns rows but insert-multi! is most efficient for columns

hiredman01:11:01

:as-arrays? true

michaellindon01:11:14

i think this returns a sequence of arrays instead of maps

michaellindon01:11:20

but 1 array is 1 row

michaellindon01:11:36

from what i understand in the docs, looks liked the most efficient way is 1 array for every column?

seancorfield02:11:54

Well, even tho' you would be doing a lot of individual inserts, you might find the easiest way is to do a reducible-query against the MS SQL DB and reduce the result with a function that inserts rows (you may even be able to use the raw result set format, to avoid conversion from the Java ResultSet to a sequence of Clojure hash maps).

seancorfield02:11:47

I just looked at the docs for insert-multi! and it is expecting rows, not columns. Not sure what makes you think it wants columns? (if you can point at something in the docs that indicates that, I can try to improve the docs)

seancorfield02:11:55

Here's an example from the test suite

(let [r (sql/insert-multi! db
                               :fruit
                               [:name :cost]
                               [["Mango" 722]
                                ["Feijoa" 441]])]
      (is (= '(1 1) r)))

seancorfield02:11:13

The query with :as-arrays? true is going to return a sequence with a vector of column names in the first element and then rows of data, so you would need to do (sql/insert-multi! pg-db :table (first rs) (rest rs)) where rs was the result from the (sql/query ms-db ["select ..." ...] {:as-arrays? true}) -- but there's a bigger issue there about the size of your result set...

michaellindon21:11:42

Hi @U04V70XH6 Thanks for clarifying. I don't think the table is large enough to run into memory issues, but it does take a very long time to populate the table using insert-multi! I'm doing something like

(sql/insert-multi! my-db :my-table my-table)
where my-table is a result form (sql/query ...)

michaellindon21:11:02

its taking longer than if I had dumped my query as a csv, and then copied it over using command line psql

michaellindon21:11:15

is there a way i can do this more efficiently?

michaellindon21:11:26

Is it inserting all the rows as a batch, or does it do one insert statement per row?

seancorfield21:11:45

Your call above is not quite right: you need to pass the column names as a vector and the rows as a vector of vectors.

seancorfield21:11:50

(sql/insert-multi! my-db :my-table (first my-table) (rest my-table))

seancorfield21:11:22

I'm a bit surprised your code works at all if my-table is the result of a query with :as-arrays? true...

seancorfield21:11:05

(You must use :as-arrays? true in the query to get the appropriate format back to use with insert-multi! if you want the batch insert)

michaellindon21:11:10

ah let me try this and get back to you, thank you 🙂

michaellindon21:11:13

p.s. will you be at the conj?

michaellindon21:11:55

that's great! I was there last year, it was a great crowd. Sadly i cant go this year, i only get 15 days holiday in my job.

michaellindon21:11:10

oh yes! with as-arrays true it works dramatically faster!!! I didn't have this option before, thank you! 🙂

4
seancorfield21:11:54

I'm lucky -- my company sends me to the Conj. In the past, they've sent me to both Clojure/West and Clojure/conj but there was no "West" this year and Strange Loop would have pushed the budget too far (given that I'm West Coast so "West" has often been much cheaper to attend).

michaellindon22:11:53

which company do you work for?

seancorfield22:11:30

World Singles Networks. Online dating.

seancorfield22:11:58

We have about 85K lines of Clojure at work.

seancorfield02:11:05

...if you're really dealing with a "rather large table" then it may not all fit in memory anyway and so reading the whole thing in as arrays and inserting it all in one operation may simply not be practical @michaellindon