Get Up and Running With SQL Server Express, Clojure, SQL Korma and Local Jars

Just a sweet and short little post to help others get up to speed accessing SQL Server Express 2008 with Clojure’s SQL Korma library.

Set Up SQL Server Express

I’m using SQL Server 2008 Express. To configure your DB server go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. Under SQL Server Network Configuration select Protocols for SQLEXPRESS. On the panel on the right size of the screen, make sure TCP/IP is Enabled, then right click it and select Properties. Select the IP Addresses tab, and make sure you have the following settings:

  1. For IP Address 127.0.0.1
    • Active: Yes
    • Enabled: Yes
    • TCP Dynamic Ports: Make sure this entry is empty.
    • TCP Port: Make sure this entry is empty.
  2. For IPAll:
    1. TCP Dynamic Ports: Make sure this entry is empty.
    2. TCP Port: 1433

Enable SQL Server mixed mode authentication (SQL Korma doesn’t do integrated/Windows authentication). Run regedit.exe and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer. Change the LoginMode to 2, and restart the SQL Server Express service.

Okay, so we can now create SQL accounts on SQL Server Express, so let’s enable the SA account with SQLCMD:

  • sqlcmd .\SQLEXPRESS -E
  • ALTER LOGIN sa ENABLE
  • GO
  • ALTER LOGIN sa with password=’aPassword’
  • GO
  • exit

Maybe you don’t want to enable the SA account, but rather create a new non-sysadmin account, as it’s a security risk to use SA for your apps. I just used it here, as it was the shortest way to get a SQL account ;-)

Load The SQL Server JDBC Driver Into a Local Artifactory Repository

Download JFrog’s Artifactory. Go to Artifactory’s bin folder and run InstallService.bat. Then launch Artifactory with  artifactory.bat, and browse to http://localhost:8081/artifactory/webappGrab Microsoft SQL Server JDBC Driver 3.0 and load it into a local Artifactory repository with the following settings:

  • GroupId: sqljdbc4
  • ArtifactId: sqljdbc4
  • Version: 3.0

The reason we’re using Artifactory, is that Leiningen demands that all your dependencies come from a repository somewhere. Since Microsoft’s JDBC driver isn’t available on a public repository like Clojars, is make it available from a local repository. You’re other option is to directly load it into a Maven repository, but trust me, this is time consuming and something you want to avoid.

Now add your local repository to Lein’s defproject, :repositories {“ext-release-local” “http://localhost:8081/artifactory/ext-release-local/”}. You can see I chose to load sqljdbc4.jar into the ext-release-local repository. Also add the SQL Server JDBC driver as a dependency: [sqljdbc4/sqljdbc4 “3.0”]

Make a Nice Clojure SQL Korma

And finally drop SQL Korma as a dependency into your Leiningen project, and do the rest of its configuration as specified on Git Hub:  [korma “0.3.0-RC4”]. If you’re new to SQL Korma, like I am, you might think that (defentity …) will also create your database objects for you. Not so! SQL Korma doesn’t have this feature currently (version 0.3.0) – hopefully they’ll add it in future. So make sure you’re DB objects exist and that the defentity statements correctly map to them.

Sounds like a few simple steps, but I took me unnecessarily long to piece all the above together, and get to the point of accessing SQL Server Express 2008 with SQL Korma. Hopefully the above will help others to reach SQL Server and Korma Nirvana, one time!

 


My New Chomma, Clojure

A few months ago I decided that it’s time to learn a new programming language that’s completely outside my normal frame of reference. After watching a video on InfoQ about the future of programming languages, I decided Clojure would make an interesting choice. I bought the Pragmatic Programmer book, and what can I say, we’ve been becoming good friends these days.

I think I’m taking to the functional programming paradigm, and like the idea of pure functions, without shared state. Even when I program C#, I tend to make heavy use of its closures like delegates, events, anonymous methods, and lambdas, using methods as data. Learning Clojure is quite a challenge if you’ve been conditioned by years of static typed, imperative programming.

A Very Basic Clojure Work Flow For Beginners

What I’ll describe here is a very basic workflow that will enable you to write some trivial Clojure apps and work through some of the examples you might find in books and on the web. Some of these tasks aren’t that clear reading the available material, as the authors usually skip to the more exciting parts of the language.

1. Get Counterclockwise for Eclipse
2. Create an executable command line script (in my case I called it repl.sh) to launch Clojure REPL (Read Evaluate Print Loop or Interactive Console) with the required libraries referenced in the class path (Clojure runs on the Java VM):

java -cp .:src:lib/JLine/jline-0_9_5.jar:lib/clojure-1.2.1.jar:lib/clojure-contrib.jar jline.ConsoleRunner clojure.main

This adds the necessary packages to the Java class path, and launches Clojure. JLine adds functionality to the Clojure REPL, like being able to press the up arrow to retrieve the previous command.

3. Compose your Clojure application by grouping related functions in the same namespaces. The first difference you’ll notice between Clojure, and an imperative language like Java or C#, is that it doesn’t have classes, only namespaces or packages. You define a namespace with the ns function:

(ns algorhythm.test.geometry.trigonometry-tests)

This tells Clojure to switch to that namespace, and if it doesn’t exist to create it, and to create all consecutive functions defined under it. To import another namespace to make its functions available you use the “(:use”:

(:use clojure.contrib.test-is algorhythm.geometry.trigonometry algorhythm.geometry.geometric-vector)

4. Start by writing some unit tests for your Clojure application. Or if you don’t do Test Driven Development (TDD), you can skip straight to 7, writing your actual implementation.

(ns algorhythm.test.geometry.trigonometry-tests
(:use clojure.contrib.test-is algorhythm.geometry.trigonometry algorhythm.geometry.geometric-vector))

(deftest find-longest-vertex-should-find-the-longest-vertex
(def triangle {:vertex-y (struct-map geometric-vertex :length 65 :link (struct-map vertex-link :angle 90 :vertex-name “vertex-x”)),
:vertex-x (struct-map geometric-vertex :length 99 :link (struct-map vertex-link :angle 20 :vertex-name “hypotenuse”)),
:hypotenuse (struct-map geometric-vertex :length 91 :link (struct-map vertex-link :vertex-name “vertex-y”))})

(is (= 99 (find-longest-vertex triangle))))

First create and switch to the namespace where we’re going to define our unit tests. Then we tell Clojure to reference the Clojure.contrib library, where Clojure’s unit test framework is located. You then declare your unit test functions, with (deftest …), and do assertions with (is …).

5. Launch Clojure’s REPL from terminal and load your unit test .clj files with (load-file …).

jacquesd@ubuntu:~> ./repl.sh

Clojure 1.2.1
user=> (load-file “source/algorhythm/test/geometry/trigonometry_tests.clj”)

6. Run all loaded unit tests on the Clojure REPL with (run-tests). They will fail.

user=> (run-tests)

7. Now, write your required functions and repeat the cycle from 5. More specifically in our example, we should be writing algorhythm.geometry.trigonometry and algorhythm.geometry.geometric-vector, that’s required by our example unit test.

Okay, that’s my 2c to help your Clojure baby steps along. Preferably you’d opt for a proper project build system, like Leinigen, instead of manually loading and executing files through the Clojure REPL. But that’s a story for another day…