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
end

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 = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end

def query(sql)
# Create an instance of an ADO Recordset
recordset = WIN32OLE.new('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
end
begin
# Move to the first record/row, if any exist
recordset.MoveFirst
# Grab all records
@data = recordset.GetRows
rescue
@data = []
end
recordset.Close
# 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
end

def close
@connection.Close
end
end

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

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

12 comments:

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: http://www.sapphiresteel.com/Using-SQL-Server-With-A-Rails

best wishes
Huw

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 http://snippets.dzone.com/posts/show/3906

Regards

Miro
http://thinkwrap.wordpress.com/

David Mullet said...

Thanks, Miro!

David

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.

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 ..as 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:
(snip)
connection_string << "Data Source=localhost\\sqlexpress;"
(snip)
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. http://tblurb.com/0IkSqB

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 WIN32OLE.new('ADODB.Connection') object.

WIN32OLE.codepage = WIN32OLE::CP_UTF8