This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-11-14
Channels
- # aleph (10)
- # announcements (2)
- # beginners (228)
- # calva (18)
- # cider (6)
- # clara (5)
- # cljdoc (25)
- # cljs-dev (22)
- # clojure (78)
- # clojure-dev (30)
- # clojure-europe (2)
- # clojure-finland (1)
- # clojure-italy (32)
- # clojure-nl (21)
- # clojure-uk (126)
- # clojurescript (34)
- # cursive (5)
- # data-science (2)
- # datascript (2)
- # datomic (26)
- # defnpodcast (1)
- # emacs (5)
- # figwheel (5)
- # figwheel-main (3)
- # fulcro (14)
- # graphql (5)
- # jobs (1)
- # keechma (4)
- # nrepl (5)
- # off-topic (35)
- # onyx (3)
- # pedestal (12)
- # random (1)
- # re-frame (35)
- # reagent (8)
- # reitit (20)
- # remote-jobs (5)
- # ring-swagger (20)
- # shadow-cljs (166)
- # sql (43)
- # vim (6)
- # yada (15)
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
im going to keep playing around with it
in my other client it was necessary to specify the domain
i was using datagrip
I just added the key :domain with the appropriate value and it works
Yup, the db-spec can have pretty much arbitrary keys that correspond to JDBC parameters to be passed in.
brilliant
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.
(see the group/artifact and version in the deps.edn
linked above)
I test with both drivers, from a Mac, connected to Windows XP running in a VM, with SQL Server Express.
thats awesome 🙂
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
i think this returns a sequence of arrays instead of maps
but 1 array is 1 row
from what i understand in the docs, looks liked the most efficient way is 1 array for every column?
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).
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)
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)))
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...
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 ...)its taking longer than if I had dumped my query as a csv, and then copied it over using command line psql
is there a way i can do this more efficiently?
Is it inserting all the rows as a batch, or does it do one insert statement per row?
See the documentation: http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#inserting-multiple-rows
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.
(sql/insert-multi! my-db :my-table (first my-table) (rest my-table))
I'm a bit surprised your code works at all if my-table
is the result of a query
with :as-arrays? true
...
(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)
ah let me try this and get back to you, thank you 🙂
p.s. will you be at the conj?
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.
oh yes! with as-arrays true it works dramatically faster!!! I didn't have this option before, thank you! 🙂
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).
which company do you work for?
World Singles Networks. Online dating.
We have about 85K lines of Clojure at work.
wow awesome
...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
Here's some docs on the reducible-query
stuff: http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#processing-a-result-set-lazily