Intro to Clojure-clr: Connecting to SQL Server and MySQL

Today’s post is a quick introduction to how connect and retrieve rows from a SQL Server and  MySQL databases.

The Setup

If you haven’t already done so setup Clojure-clr.  Obviously you’ll need to have access to a SQL Server and MySQL instances.  If you do not have one or both of the databases you can download and install them from here:  SQL Server Express, MySQL.  After downloading and installing the databases grab the data and schema for this post from here: SQL Server version, MySQL version.  Next load the data/schema files then you are ready to go!

Connecting to SQL Server

Since we are working with Clojure-clr I think It would only be proper to start off with connecting to SQL Server.  In order to do that I need to load the System.Data assembly.  It contains the necessary classes for interacting with the database.

Once I have System.Data loaded its time to start the connection process.  The first thing I need to do is create a connection and then open it.

Now that I have an open connection I can create a SqlCommand object.  The SqlCommand constructor version I’m using takes two parameters a SQL Statement and a database connection object.  After creating the SqlCommand object I run SQL statement by calling the ExecuteReader method.  Now the data is ready to be retrieved.

To keep the blog post simple I’m going to grab the results and print out the player id value using a while loop. When the while loop completes I close the reader and the database connection objects.

That is the quick and dirty way to connect to SQL Server from Clojure-clr.  It isn’t as elegant as it is in the JVM version but I hope to get it that way some day.  I am working on a project at work that,  as time permits, I am attempting to port the java.jdbc code over to the CLR.  If/When I get it working I will be sure to blog about it.  Now it is time to connect to a MySQL database.

Connecting to MySQL

Connecting  to MySQL follows the same process as connecting to SQL Server. In fact if you don’t look too closely you might think I’m using the same code to connect to MySQL.  I wish that was the case but its not. In order to connect to MySQL from .NET you’ll need to download the assembly Mysql.Data from the MySQL developer site.  I have used the Mysql.Data assembly for awhile so I added it to the GAC which allows me to load it into my Clojure code the same way I did for the SQL Server version. If you don’t want to add Mysql.Data to the GAC you can load it using the assembly-load-from function: (assembly-load-from “the path to the dll”). 

I now have the MySQL libraries loaded and I’m ready to grab the playerId’s from the database. Here is the my-run-it function:

As you can see the method names are almost the same, everything is prefaced with My.  You still do the same process, create the connection, the command, execute the command and read from the reader.

Summary

Connecting to SQL Server and MySQL is pretty straight forward. Just load the appropriate assembly and you are off.  In the future I hope to have a cleaner method of interacting with databases through either a direct port of java.jdbc or something very similar.

I am relatively new to Clojure so if you see code that I’ve written that makes you cringe please feel free to leave a comment with your suggestion.  I am all ears.

About the Data

The data I’m using for this blog post comes from the Baseball Databank project.  The project has gathered all the baseball status from previous season and offers the data in many different formats. 

Resources

Clojure-clr Setup: Clojure-clr Download Page and Getting Started with Clojure-clr.

Database Downloads: SQL Server Express, MySQL

Data:  SQL Server version, MySQL version

MySQL Assembly: Mysql.Data

My Source: https://github.com/rippinrobr/clojure-clr-intro/zipball/master