This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2017-06-07
Channels
- # aleph (19)
- # aws (1)
- # beginners (75)
- # boot (28)
- # cider (1)
- # cljs-dev (12)
- # cljsrn (20)
- # clojure (350)
- # clojure-argentina (1)
- # clojure-chicago (2)
- # clojure-dev (2)
- # clojure-russia (5)
- # clojure-spec (2)
- # clojure-uk (14)
- # clojure-ukraine (3)
- # clojurescript (68)
- # component (87)
- # core-async (25)
- # core-logic (13)
- # cursive (4)
- # data-science (72)
- # datascript (59)
- # datomic (15)
- # defnpodcast (7)
- # emacs (33)
- # hoplon (5)
- # immutant (73)
- # jobs (21)
- # klipse (6)
- # lumo (14)
- # off-topic (26)
- # om (23)
- # onyx (6)
- # parinfer (37)
- # protorepl (4)
- # re-frame (13)
- # ring (2)
- # rum (3)
- # spacemacs (2)
- # specter (22)
- # sql (47)
- # uncomplicate (10)
- # unrepl (79)
- # untangled (66)
- # vim (47)
- # yada (17)
Is this the most sensible approach for connecting to a MS SQL Server database? https://en.wikibooks.org/wiki/Clojure_Programming/Examples/JDBC_Examples#Microsoft_SQL_Server Or are the better ways of doing this?
That usage of the clojure.java.jdbc api seems deprecated tbh, though I imagine it would still work
You might refer to e.g. https://github.com/clojure/java.jdbc/#example-usage
But what specifically are you asking about? What jdbc library to use? How to configure the database connection? How to use the jdbc api?
I was looking at that, but none of the examples are for MS SQL Server
So I suppose my main question is, what is the best way of connecting to a MS SQL Server database within a Clojure Ring application?
That wiki page is very out of date. Once I get to my desk, I'll dig up an example for you @yogidevbear
Great! Thank you Sean
No rush. I'm hoping to play around a bit later tonight with getting some data out of a DB within a Ring web project
The first stumbling block with sql server is which jdbc driver to use. For simplicity I suggest the jtds one (otherwise you have to download Microsoft's and learn how to get it into your local maven repo).
I've downloaded sqljdbc_6.0 and added it to my classpath. I'm guessing that is still a required step
Ok, I'll go with the jtds one then
Is 1.3.1 the latest version?
Hmmm.. This one? https://sourceforge.net/projects/jtds/
Note it looks like Microsoft has begun publishing their jdbc driver to maven central recently: https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc
Are you using Leiningen or Boot?
(remember: you do not need to download JAR files to use them with Clojure)
You can see what versions of JARs are available via Maven: http://search.maven.org/#search%7Cga%7C1%7Cjtds
This is the most recent http://search.maven.org/#artifactdetails%7Cnet.sourceforge.jtds%7Cjtds%7C1.3.1%7Cjar
It shows what needs to go in Leiningen (or Boot) in :dependencies
:
[net.sourceforge.jtds/jtds "1.3.1"]
Ah, yes, MS has published it’s JDBC driver! Finally! http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22com.microsoft.sqlserver%22%20AND%20a%3A%22mssql-jdbc%22
So you could use this if you wanted for MS SQL Server instead of jTDS now:
[com.microsoft.sqlserver/mssql-jdbc "6.1.0.jre8"]
So, onto your db-spec
. The easier approach is to use :dbtype
and :dbname
etc since java.jdbc
can figure out the rest. For the jTDS dependency, you’d want:
(def db-spec {:dbtype "jtds" :dbname "your_db_name" :host "127.0.0.1" :port 1433 :user "sa" :password "secret"})
Assuming you’re using a DB on the same machine, with the standard SQL Server port. :user
/`:password` should be adjusted for whatever credentials you’re using to access the DB.
You might need "127.0.0.1\\SQLEXPRESS"
for :host
depending on which version of SQL Server you have installed (I test against SQL Server Express so I need \\SQLEXPRESS
in the hostname.
Hmm, looks like you shouldn’t need that for jTDS, only for the MS driver.
@yogidevbear Let us know how you’re getting along so far…
(if you use the MS driver, your :dbtype
would be "mssql"
by the way)
Will do. It'll be a bit later this evening (UK time)
I’ll be here!
My project.clj file looks something like this though:
(defproject demo-test "0.1.0-SNAPSHOT"
:description "FIXME: write description"
:url ""
:min-lein-version "2.0.0"
:dependencies [[org.clojure/clojure "1.8.0"]
[compojure "1.5.1"]
[ring/ring-defaults "0.2.1"]
[org.clojure/java.jdbc "0.7.0-alpha3"]
[net.sourceforge.jtds/jtds "1.3.1"]]
:plugins [[lein-ring "0.9.7"]]
:ring {:handler cfcamp-demo.handler/app}
:profiles
{:dev {:dependencies [[javax.servlet/servlet-api "2.5"]
[ring/ring-mock "0.3.0"]]}})
And the handler.clj (which might be very far off 😉) looks something like this:
(ns demo-test.handler
(:require [compojure.core :refer :all]
[compojure.route :as route]
[ring.middleware.defaults :refer [wrap-defaults site-defaults]]
[clojure.java.jdbc :as jdbc]))
(def db {:subprotocol "jtds:sqlserver"
:subname "//127.0.0.1:1433;database=mydbname;integratedSecurity=true"})
;; :subname "//127.0.0.1//mydbname"
;; :user "theusername"
;; :password "thepassword"})
(defroutes app-routes
(GET "/" []
(with-db-connection [connection db]
(let [result (query connection
["SELECT TOP 10 * FROM dbo.sometable"])]
result)))
(route/not-found "Not Found"))
(def app
(wrap-defaults app-routes site-defaults))
Yeah, that’s the old-school JDBC connection spec. The new one is much simpler.
You don’t need with-db-connection
— you can just pass db
directly to jdbc/query
(defroutes approutes
(GET "/" [] (jdbc/query db "SELECT TOP 10 * FROM dbo.sometable"))
(route/not-found "Not Found"))
should work.(You only need the [ ]
in the jdbc/query
call if you’re providing parameters: ["SQL string" my params]
Progress (I think): I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
Are you still using the db
as shown above, with integratedSecurity
etc? Or did you switch to the simpler spec I showed?
(ns demo-test.handler
(:require [compojure.core :refer :all]
[compojure.route :as route]
[ring.middleware.defaults :refer [wrap-defaults site-defaults]]
[clojure.java.jdbc :as jdbc]))
(def db {:subprotocol "jtds:sqlserver"
:subname "//127.0.0.1:1433;database=mydbname;integratedSecurity=true"})
(defroutes app-routes
(GET "/" []
(jdbc/query db "SELECT TOP 10 * FROM dbo.sometable"))
(route/not-found "Not Found"))
(def app
(wrap-defaults app-routes site-defaults))
Something like that
Yeah, I think that error is because of integratedSecurity
which needs a native DLL.
ah okay
If you switch to the simpler db
spec I showed, and provide a user name and password, you won’t have that issue.
(I connect to MS SQL Server using jTDS from a Mac — so no DLL option)
Boom! Worked
Thank you Sean 🙂
Must dash