Using RubyODBC in Rails on Linux to connect to MS SQL Server

(I really need to work on shortening my titles.)

I spent the day trying to figure out one little problem. I setup a brand new server for the Intranet at work. I set it up with Fedora 7 and started configuring it for my rails apps. Since our corporation has decided to use Microsoft SQL for all databases, I turned to the instructions on the Rails wiki to setup ODBC.

Most of it went pretty smoothly. When I was done, I launched mongrel and checked the site using http://ip_address:3000. It all worked great. So, then I setup mongrel_cluster and fired it up. No workey. I kept getting database errors saying “No data found.”

The very misleading error was hard to solve. I finally found a post which explains that there is a bug in RubyODBC which does not play well with “text” columns that are blank. So, the answer is to set all your blank fields to NULL. Well, I couldn’t figure out how to do that. (MSSQL is not my favorite.) I finally figured out to use a whole bunch of these queries in SQL query analyzer:

UPDATE tablename SET description=" " WHERE description LIKE ""

(There’s a space between the first set of quotes, but not the second.)

So, that fixed everything for now, but I also had to make sure it doesn’t happen again. I couldn’t get the default value in SQL to do what I needed, so I added a bunch of these in the model code for each problematic model:

def before_save
if self.description.blank?
self.description = ' ' #(two spaces)
end
end

Hopefully this will help somebody else.