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.