Thursday, April 14, 2005

Ruby Strikes Back

Don't you just hate when you spend forever trying to figure out how to do something easy? Trying to get Ruby and Microsoft SQL Server talking was kinda like that for me.

I never could get ODBC support to work, all the examples and newsgroup posts I could find only described using it with a DSN (which I didn't want to do). So I fell back on the ADO DatabaseDriver (DBD).

So what does it take? Download and install the Ruby DBI package. (Just Google for Ruby dbi and you'll find it.) Then config/setup/install. To get it to install I had to rename/delete a number of files in my Ruby installation, but I figure I'm just getting later versions with the dbi install, and so far haven't seen any harm.

Oh, btw, there's a newer version of FreeRIDE to try than what comes with the Ruby installer. Not quite as snazzy as ArachnoRuby, but it's simple and has Ruby Documentation integrated, so it's pretty cool, and is what I'm using for now.

Anyways, once you have DBI, it's actually really easy to use.

require 'dbi'

dbh = DBI.connect("DBI:ado:Driver={SQL Server};Server=;Database=Northwind;Uid=sa;Pwd=password")
sth = dbh.execute("select * from products")
rows = sth.fetch_all
rows do |row| puts row[:ProductName]

That's it!

Cool huh? Well, it gets mo' betta. Check out this code that basically serializes the Products table from Northwind to an Xml file:

dbh = DBI.connect("DBI:ado:Driver={SQL Server};Server=;Database=Northwind;Uid=sa;Pwd=password")
DBI::Utils::XMLFormatter.table(dbh.select_all("select * from products"), "products", "product","products.xml", "w+"))

You could actually do this all on one line if you were a little crazy since the .connect method of DBI returns a database handle that you could call .select_all on directly.

If you look at the above examples you can see, besides "DBI:ado:", the connectionStrings are just standard OLEDB ones you'd find at So once you know how, it's really simple.

I think this goes to show one of the advantages of a statically typed language though: The learning curve is much lower when you have tools like Intellisense and Type cast compilation errors to help you out.

Anyways, cool stuff. I'm taking my newfound Ruby knowledge and applying it to a report generator for work that will use Xml streams of queries as "Resources", and allow you to use Xslt embedded in the "Job" files to do things like send out an Excel report once a week for SalesPerson Commissions. I think I should be able to replace the existing system (written in c#) of about 500 lines of code, with a Ruby version of about half that, that provides more features to boot.
Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?