Wednesday, November 30, 2011

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

DataSQL Server version, MySQL version

MySQL Assembly: Mysql.Data

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

13 comments:

  1. Great article. Following you. :)

    ReplyDelete
  2. Thanks nCdy. Let me know if there is a post you'd like to see on ClojureCLR

    ReplyDelete
  3. Brilliant informatioon.it is easy to understand the reources in this blog.I read this article compltely and is very useful to for me.thanks for this information.
    java training in chennai

    ReplyDelete
  4. This blog gives the details about database information. This provides the information of the way of connecting sql server and mysql. All that details are explained clearly. Thank you very much for this blog.
    Dotnet Training in Chennai

    ReplyDelete
  5. This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.e commerce web development

    ReplyDelete
  6. .Net is most favored programming dialect among programming engineers everywhere throughout the world. It is likewise considered as most trusted and successful stage to construct high performing desktop or venture application.Keep sharing morelike this.
    Regards,
    DOTNET Training in Chennai | .net training Chennai | .net course in Chennai

    ReplyDelete
  7. Awesome! I am looking for ways to add my website ranking and found this guide written by you. Will do it. Thanks.
    Core JAVA Training in Chennai | JAVA Training in Chennai

    ReplyDelete
  8. The article gave me idea about connecting SQL server with MY sql
    Dot Net Training in Chennai

    ReplyDelete
  9. Thanks Admin for sharing such a useful post, I hope it’s useful to many individuals for whose looking this precious information to developing their skill.
    Regards,
    Best DOT NET Training in Chennai|DOT NET Training in Chennai|DOT NET Course in Chennai

    ReplyDelete
  10. Your post is really awesome. Your blog is really helpful for me to develop my skills in a right way. Thanks for sharing this unique information with us.
    - Learn Digital Academy

    ReplyDelete
  11. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles.

    Digital Marketing Training in Chennai

    Digital Marketing Course in Chennai


    ReplyDelete