Sunday, March 25, 2007

Ruby, ADO, and SQLServer

Ruby and ActiveX Data Objects (ADO) make working with Microsoft SQL Server databases simple. Here's a simple example of a class that manages the SQL Server database connection and queries:

require 'win32ole'

class SqlServer
# This class manages database connection and queries
attr_accessor :connection, :data, :fields

def initialize
@connection = nil
@data = nil

def open
# Open ADO connection to the SQL Server database
connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "User ID=USER_ID;"
connection_string << "password=PASSWORD;"
connection_string << "Initial Catalog=DATABASE;"
connection_string << "Data Source=IP_ADDRESS;"
connection_string << "Network Library=dbmssocn"
@connection ='ADODB.Connection')

def query(sql)
# Create an instance of an ADO Recordset
recordset ='ADODB.Recordset')
# Open the recordset, using an SQL statement and the
# existing ADO connection
recordset.Open(sql, @connection)
# Create and populate an array of field names
@fields = []
recordset.Fields.each do |field|
@fields << field.Name
# Move to the first record/row, if any exist
# Grab all records
@data = recordset.GetRows
@data = []
# An ADO Recordset's GetRows method returns an array
# of columns, so we'll use the transpose method to
# convert it to an array of rows
@data = @data.transpose

def close

You can then use this class as follows:

db =
field_names = db.fields
players =

The above code is, of course, incomplete and can certainly be improved and extended (error handling, etc.). But, hopefully, it provides you with a solid foundation on which to build.

UPDATE: You might like to know that you can automate many of your SQL Server administrative tasks by leveraging Distributed Management Objects (SQL-DMO). I've explained this in a later article here.

Thanks for stopping by!

Digg my article


Huw Collingbourne said...

You might like to see how you can use Ruby In Steel to integrate SQL Server, Ruby and Rails inside Visual Studio. See my article on the subject, here:

best wishes

Miro said...

Hi David
thanks for the hint!. Worked like charm.

I have made some minor improvements to make the class easier to use and to configure by parameters.
The modified version with changes is available at



David Mullet said...

Thanks, Miro!


Anonymous said...

You rock. This helped muchly, since the Ruby DBI interface had not been playing nicely.

Unknown said...

Thank you. Your posts have helped me a lot. Thanks.

Display name said...

Thanks a lot!

hoornet said...

What about active-record and sql-server,can you give an example for this outside RAILS, please

Sunny said...

Hi David,

Thanks for the post.. Gud One.. !!!

Could you extend this to the SQL Server Express 2005 this one is not working for the Express database.

Thanks in Advance !!!

Anonymous said...

This doesn't work for me -- I get a method missing error for the .Open method.

There is no .Open method in win32ole that I can see.

I am using SQL Server 2008 Express

Anonymous said...

@Sunny, @Anonymous: if you are using a database in the sqlexpress instance, don't forget to escape the data source name like this:
connection_string << "Data Source=localhost\\sqlexpress;"
If you just use "localhost\sqlexpress", you'll get the error you describe.

Justin said...

I have been using this for cucumber testing scripts. I became very handy to use a hash instead of an array. I posted my change here. make sure you declare @data_hash in the first part of the class.

left handed engineer said...

Thanks for posting this! We use a modified version of Miro's version at my company and it's very intuitive.

I need to add Unicode support to the query methods but haven't yet dug into the WIN32OLE object. Any tips perchance?

left handed engineer said...

Actually found the solution on a Rails board.

Added the line below before instantiating the'ADODB.Connection') object.

WIN32OLE.codepage = WIN32OLE::CP_UTF8