JRuby: connect to a MSSQL database via ActiveRecord and create ActiveRecord::Base classes

In this snippet I’ll share some code that allows you to connect to a MSSQL database via ActiveRecord in a JRuby environment. In this code I decided to use the “Microsoft JDBC Driver for SQL Server”. It can be downloaded here. Copy the Java jar (sqljdbc4.jar) into your project space to get started.

Create a gem file to include the ActiveRecord JDBC adapter, file: Gemfile. Execute “bundle install” to install the dependencies.

source 'https://rubygems.org'

gem 'activerecord-jdbcmssql-adapter'

The following shows some sample code to establish an ActiveRecord connection to MSSQL using the JDBC driver.

#!/usr/bin/env jruby

# include JDBC MSSQL adapter for ActiveRecord
require 'activerecord-jdbcmssql-adapter'

# include Microsoft JDBC Driver for SQL Server
require 'sqljdbc4.jar'

# define hash of connection properties
config = {
  url: 'jdbc:sqlserver://MSSQLHOST;databaseName=MSSQLDATABASE',
  adapter: 'jdbc',
  username: 'MSSQLUSER',
  password: 'MSSQLPASSWORD',
  driver: 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
}

# establish connection
ActiveRecord::Base.establish_connection( config )

# define model and inherit from ActiveRecord Base
class MssqlTable < ActiveRecord::Base
  # override table name as necessary
  self.table_name = 'MssqlTable'
end

# query model table, examples:
MssqlTable.first
MssqlTable.where(col: 'wee')

Bonus: query the database for a list of tables and dynamically create an ActiveRecord model for each.

# fetch a list of tables form the database
sql = "SELECT * FROM information_schema.tables"
results = ActiveRecord::Base.connection.execute sql

# iterate over list and collect table names
table_list = results.each_with_object([]) do |r, obj|
  next unless r['TABLE_CATALOG'] == DB_NAME
  next unless r['TABLE_TYPE'] == 'BASE TABLE'
  obj << r['TABLE_NAME']
end

# iterate over tables and dynamically create an object that inherits from ActiveRecord::Base
table_list.each do |table|

  klass = Object.const_set table.capitalize, Class.new(ActiveRecord::Base)
  klass.class_eval {
    self.table_name = table
  }

end

# query model..
MssqlTable.first

Eric London