JRuby, ActiveRecord, JDBC to SQL Server
JRuby, ActiveRecord, JDBC to SQL ServerI 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
页:
[1]