JRuby, ActiveRecord, JDBC to SQL Server

JRuby, ActiveRecord, JDBC to SQL Server

I recently had to figure out a good way to export data in XML form from a SQL Server database. I searched high and low for a good ActiveRecord adapter written in pure Ruby to talk to SQL Server. Unfortunately, the setup and overhead to get the right ODBC driver, DSN configs, and all those funky libraries to work properly on any *Nix-based machine were adding up way too quickly and complicating matters. So back to the drawing board.

RESTful service from .NET – no

Way too much work for such a simple task that only runs but once a week. Beside anything Microsoft is my nemesis and I am not even going to put my toe in that water.

SQL Server bcp export – no.

The bottom line – writing code in your database is always bad news. Sure the bcp export is the quick and dirty, but it might be too quick and dirty. It can make upgrades and migrations almost impossible. Just think how difficult it would be to migrate from SQL Server to say MySQL if the use of the bcp utility compounds over time. More importantly, how do you test it?

JDBC – yes!

Really? I was kind of shocked to learn that Microsoft jumped on the Java bandwagon and wrote a JDBC adapter. Woo-hoo!  Wait…I don’t want to write a bunch of junky DAO’s in Java.

Enter – my good friends JRuby and ActiveRecord.

I can write sweet sugary Ruby code while utilizing the power of any or all of the existing Java libraries – which so happens to include JDBC!

So in order to glue this together it is quite simple.

• First install JRuby, it’s simple and there are 2 million blog posts about how to do this, all you need is Java pre-installed on your machine. Download it here from here.
• jruby> gem install activerecord
• jruby> gem install activerecord-jdbc-adapter
• Download the SQL Server JDBC driver JAR, I recommend getting version 2 or higher
• activerecord-jdbc-adapter is a nifty JRuby specific gem that acts as an adapter for ActiveRecord to speak JDBC  (yet another thanks to Nick Sieger).

Once your have these things installed you can write a simple Ruby class to establish the connection and define any number of ActiveRecord classes to map to the tables you’re after. Here is a small snippet on how easy it is to connect to SQL Server using this technique.

#assuming this file is inline with your sqljdbc.jar or just put it into your $JRUBY_HOME/lib directory



require 'rubygems'
require 'active_record'
require 'active_record/connection_adapters/jdbc_adapter'
require 'sqljdbc4.jar'

ActiveRecord::Base.establish_connection(
      :adapter => "jdbc",
      :username => "XXXX",
      :password => "XXXX",
      :driver => "com.microsoft.sqlserver.jdbc.SQLServerDriver",
      :url=> "jdbc:sqlserver://Server;databaseName=XXXX"
)

class Tclient < ActiveRecord::Base
	set_table_name "CLIENT"
end

puts Tclient.column_names()

Approximately 10 lines of actual code and you’re done. It can’t be done any more elegant or simple than that. Here is the really beauty part, you can actually test your “script”. Use RSpec or TestUnit, but it is tested! Let cron or whatever scheduling mechanism of your choice run your script with confidence