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 -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 = 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

Mar 14, 2010 | Software | Leave A Comment »

Rails 2.3.3 to 2.3.5 and Jruby 1.4 Upgrade Notes

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 to work.

2.) ActiveSupport::JSON::ParseError no longer exists, so be sure to use ActiveSupport::JSON.parse_error.

3.) Update your rspec and rspec-rails gems to 1.3.x.

4.) Update your rack gem from 1.0.0 to 1.0.1

5.) Rails 2.3.5 works with the RailsXss plugin, in Rails 3 escaping content in erb will default, but if you want to ensure your site isn’t at risk of XSS, make sure you do this install.

Jruby upgrade notes:

If you are using Jruby < 1.4 and you use the net/http library, you better upgrade soon. We were having all sorts of problems with threading and exorbitantly long running requests. There were several bug fixes around the open and read timeout functionality in Jruby 1.4. Now network requests will timeout properly according to your open and read timeout settings.

Jan 15, 2010 | Software | Leave A Comment »

jruby-quartz 1.1 released

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 Rackspace. So if you’re looking to use gem install, you are out of luck until they re-write that system.

Nov 03, 2009 | Software | 1 Comment »

activerecord-jdbc-adapter 0.9.2

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 some digging I found a little problem with activerecord-jdbc-adapter, so I submitted a patch. The good news is that my patch has been included in the latest release. Thanks to Nick Sieger and the gang for including it.

== 0.9.2
- The main, highly awaited fix for this release is a solution to the
  rake db:create/db:drop issue. The main change is a new 'jdbc' rails
  generator that should be run once to prepare a Rails application to
  use JDBC. The upside of this generator is that you no longer will
  need to alter database.yml for JDBC. See the README.txt for details.
- Cleanup and reconnect if errors occur during begin/rollback
  (Jean-Dominique Morani, Christian Seiler)
...
- Fix for mysql tables with non standard primary keys such that the schema dump is correct (Nick Zalabak)

Nov 03, 2009 | Software | Leave A Comment »