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 -S gem install activerecord
• jruby -S 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 'sqljdbc.jar'
 
config = {:url => "jdbc:sqlserver://localhost;databaseName=sucky_sqlserver_db", :adapter => "jdbc", :username => "user", :password => "pass"}
 
ActiveRecord::Base.establish_connection( config )
 
class Person < ActiveRecord::Base; end
 
people = Person.find(:all)
 
ActiveRecord::Base.clear_active_connections!
 
file = File.new("my_xml.xml", "w")
file.write(people.to_xml)
file.close

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

This past week I’ve had the opportunity to really dig in and give processing.js a work out. For those not familiar with the technology, processing.js is a JavaScript library based on the Java based processing library available for download at http://dev.processing.org/. Processing.js uses the HTML5 Canvas element to draw shapes and designs while using a [...]

Rails upgrade notes: 1.) Rails now has seeding functionality and along with it comes a new task, db:seed. If you use the seed_fu gem be aware that your db:seed call that worked with the gem is now executing the Rails version of db:seed. You will need to call rake db:seed_fu to get the gem version [...]

It is becoming increasingly popular to mix technologies, frameworks, and languages to power a web site. So what happens when you want to share session data between some permutation of a Ruby, PHP, .NET, Python, Java web framework? Well of course using cookies is the first thing that should pop into your head, and with [...]

As I stumbled my way into the weekend feeling sick and hacking up a lung from this nasty cold. I thought the weekend would be a no frills, real simple, lay on the couch and get better stretch. Weeks ago Lauren invited a friend from out of town, Brandy, and she told me we would [...]

Update: I’ve made magic_meta_methods a gem available at http://rubygems.org/gems/magic_meta_methods A while back I wrote a plugin for Rails that serializes text and data structures into a singular column called magic_meta_methods on an ActiveRecord. The plugin then makes the data readable and writable through meta-programmed methods and allows the user to save the ActiveRecord just as [...]

Last semester at Depaul University I had the opportunity to give 2 lectures on Ruby on Rails. The topics were “Rails Hosting & Deployment” and “Rails Security”. It was an awesome experience and really rewarding to talk about something that I am passionate about. A big thanks to Igor Polevoy who introduced and recommended me [...]

Update: jruby-quartz is a gem at http://rubygems.org/gems/jruby-quartz I just released jruby-quartz 1.1 on github. Notable changes include support for programmatically firing a job from the base job scheduler instead of relying solely on the scheduled job pool. As a side note, I just realized that github has temporarily stopped building gems since they’ve moved to [...]

About 2 months back I was working on getting a db dump on my current project. I noticed a bunch of the primary keys were missing from tables in the dump file. Irked, I observed that many of tables we hook up to have residual Java Hibernate-isms with non-standard primary keys, basically not ‘id’. After [...]