So here I was wanting to query some database tables that get populated during our build process and then modify some text files / source control based on what's in the table. Seemed like a good excuse to use some ruby but low and behold ActiveRecord doesn't seem to care for the structure of our database tables.

Enter Sequel

Sequel is an ORM framework for Ruby. Sequel provides thread safety, connection pooling, and a concise DSL for constructing queries and table schemas.

Sequel makes it easy to deal with multiple records without having to break your teeth on SQL.

cool, gem install sequel

But how to connect to SQL server? Sequel supports DBI, but I could find no tailor made examples (plenty of SQLLite, MySql and Postgres though).

Well first things first, you'd better make sure you have DBI installed and configured correctly. If you are running using the Windows One Click installer version of ruby you are almost there, you just need to copy the ADO driver. For instructions on that see this rails howto

I however am running under cygwin and installed the ruby cygwin package so I need to install DBI myself.

Download the ruby-dbi source from http://rubyforge.org/projects/ruby-dbi/

Extract your sources (tar -xvf dbi-0.1.1.tar.gz)
cd to your new folder and do these commands
ruby setup.rb config --with=dbi,dbd_msql,dbd_ado (don't use spaces after the commas it seems picky)
ruby setup.rb setup
ruby setup.rb install

At this point you might want to jump into irb and make sure all is well with the world.
require 'dbi' should return true.

Ok, now let's connect to the database.
I started by working from this post:

Sequel version 0.1.3 has just been released. This release adds a DBI
adapter, and thereby support for ODBC, ADO, Frontbase and other
databases. For example:

require 'sequel/dbi'
DB = Sequel.open 'dbi:/odbc:my_dsn'
DB[:my_table].all #=> returns all rows in the table as hashes
# etc...

As well as this:

require 'dbi'

class Server
 attr_reader :name
 def initialize(name)
  @server_name=name
  @dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI;Data Source=#{name};Integrated Security=SSPI")
 end
 
 def databases
  db=Array.new
  @dbh.select_all('SELECT name FROM master.sys.databases ORDER BY 1') do | row |
   db.<< Database.new(@dbh,row[0])
  end  
  db
 end
end

class Database
 attr_reader :name
 def initialize(dbh,name)
  @dbh=dbh
  @name=name
 end
end


server=Server.new("localhost")
server.databases.each {|x| p x.name}

My first feeble attempt when something like this:

require 'sequel/dbi'

db = Sequel.open 'dbi:/ado:Provider=SQLNCLI;Data Source=(local)\\sqlexpress;Integrated Security=SSPI'

Which failed with URI::InvalidURIError because Sequel.open wants to do some URI parsing and I did not give it a valid URI. Thankfully though I can look at source code.

My first check is in dbi.rb. If your playing along at home it will be in /usr/lib/ruby/gems/1.8/gems/sequel-0.1.9.2/lib/sequel on cygwin and C:\ruby\lib\ruby\gems\1.8\gems\sequel-0.1.9.2\lib\sequel on windows

The connect method of this class looks interesting

 def connect
        dbname = @opts[:database] =~ /^DBI:/ ? \
          @opts[:database] : @opts[:database] = 'DBI:' + @opts[:database]
        ::DBI.connect(dbname, @opts[:user], @opts[:password])
 end

So all it really cares about is a value in an @opts has for :database. So to see how I can get that there I need to look at where @opts comes from. Well I'll save you the trouble and tell you it comes from self.connect in database.rb (connect is aliased to open btw) Have a look at this snippet:

# Converts a uri to an options hash. These options are then passed
    # to a newly created database object.
    def self.uri_to_options(uri)
      {
        :user => uri.user,
        :password => uri.password,
        :host => uri.host,
        :port => uri.port,
        :database => (uri.path =~ /\/(.*)/) && ($1)
      }
    end
    
    # call-seq:
    #   Sequel::Database.connect(conn_string)
    #   Sequel.connect(conn_string)
    #   Sequel.open(conn_string)
    #
    # Creates a new database object based on the supplied connection string.
    # The specified scheme determines the database class used, and the rest
    # of the string specifies the connection options. For example:
    #   DB = Sequel.open 'sqlite:///blog.db'
    def self.connect(conn_string, more_opts = nil)
      uri = URI.parse(conn_string)
      c = @@adapters[uri.scheme.to_sym]
      raise SequelError, "Invalid database scheme" unless c
      c.new(c.uri_to_options(uri).merge(more_opts || {}))
    end

Looking at this it seems like connect and hence open will take an optional second hash that is merged with what gets parsed from the URI. This is promising because I had seen several examples that just passed a scheme to open so I thought I'd try this

require 'sequel/dbi'
db = Sequel.open 'dbi:/ado', {:database => 'DBI:ADO:Provider=SQLNCLI;' +
                                          'Data Source=(local)\\sqlexpress;' +
                                          'Integrated Security=SSPI'}

Hey, that didn't give me an error, in fact it gave me a Sequel::DBI::Database object. Surely it won't work, I'd better check.

db.execute("SELECT Name from master.sys.databases order by 1").fetch_all

Well look at that it did work. And there you have it. You've just connected to SQLExpress 2005 with sequel. Happy hunting.