Fork me on GitHub
#sql
<
2017-06-07
>
yogidevbear13:06:54

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?

donaldball13:06:31

That usage of the clojure.java.jdbc api seems deprecated tbh, though I imagine it would still work

donaldball13:06:28

But what specifically are you asking about? What jdbc library to use? How to configure the database connection? How to use the jdbc api?

yogidevbear13:06:44

I was looking at that, but none of the examples are for MS SQL Server

yogidevbear13:06:50

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?

seancorfield16:06:06

That wiki page is very out of date. Once I get to my desk, I'll dig up an example for you @yogidevbear

yogidevbear16:06:29

Great! Thank you Sean

yogidevbear16:06:51

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

seancorfield16:06:58

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).

yogidevbear16:06:01

I've downloaded sqljdbc_6.0 and added it to my classpath. I'm guessing that is still a required step

yogidevbear16:06:21

Ok, I'll go with the jtds one then

yogidevbear16:06:26

Is 1.3.1 the latest version?

donaldball16:06:11

Note it looks like Microsoft has begun publishing their jdbc driver to maven central recently: https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc

seancorfield16:06:00

Are you using Leiningen or Boot?

seancorfield16:06:13

(remember: you do not need to download JAR files to use them with Clojure)

seancorfield16:06:51

You can see what versions of JARs are available via Maven: http://search.maven.org/#search%7Cga%7C1%7Cjtds

seancorfield16:06:35

It shows what needs to go in Leiningen (or Boot) in :dependencies:

[net.sourceforge.jtds/jtds "1.3.1"]

seancorfield16:06:46

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"]

seancorfield16:06:04

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"})

seancorfield16:06:42

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.

seancorfield16:06:35

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.

seancorfield16:06:14

Hmm, looks like you shouldn’t need that for jTDS, only for the MS driver.

seancorfield16:06:35

@yogidevbear Let us know how you’re getting along so far…

seancorfield16:06:17

(if you use the MS driver, your :dbtype would be "mssql" by the way)

yogidevbear16:06:28

Will do. It'll be a bit later this evening (UK time)

seancorfield16:06:38

I’ll be here!

yogidevbear16:06:20

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"]]}})

yogidevbear16:06:59

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))

seancorfield16:06:50

Yeah, that’s the old-school JDBC connection spec. The new one is much simpler.

seancorfield16:06:12

You don’t need with-db-connection — you can just pass db directly to jdbc/query

seancorfield16:06:20

(defroutes approutes
  (GET "/" [] (jdbc/query db "SELECT TOP 10 * FROM dbo.sometable"))
  (route/not-found "Not Found"))
should work.

seancorfield16:06:56

(You only need the [ ] in the jdbc/query call if you’re providing parameters: ["SQL string" my params]

yogidevbear16:06:47

Progress (I think): I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

seancorfield16:06:31

Are you still using the db as shown above, with integratedSecurity etc? Or did you switch to the simpler spec I showed?

yogidevbear16:06:28

(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))

yogidevbear16:06:30

Something like that

seancorfield16:06:53

Yeah, I think that error is because of integratedSecurity which needs a native DLL.

seancorfield16:06:24

If you switch to the simpler db spec I showed, and provide a user name and password, you won’t have that issue.

seancorfield16:06:44

(I connect to MS SQL Server using jTDS from a Mac — so no DLL option)

yogidevbear16:06:34

Thank you Sean 🙂