config.cache_classes = true affecting type coercion with RubyDBI

Question

Environment:
Rails 2.3.2
DBI 0.4.1
DBD/ODBC 0.2.4

Scenario:
I have a Rails app that imports most of it's data from external SQL DBs into the Rails SQL DB via regular batch jobs. Those batch jobs start by loading the Rails environment, then proceed to make direct database connections via RubyDBI. Once I connect, I run select statements to extract the data, massage it, and build ActiveRecord objects.

I've just come across a strange issue where the behavior differs in development then in production. It seems that when config.cache_classes is true, DBI stops properly coercing the returned SQL DATETIME type into a Ruby datetime. Here's an extracted code example:

## config.cache_classes = true
query = "select TOP 1 [EPOLeafNode].[Lastupdate] AS last_update from [EPOLeafNode]"
conn = DBI.connect('dbi:ODBC:DRIVER=FreeTDS;TDS_Version=8.0;SERVER=sql;DATABASE=EOP;uid=uid;pwd=pwd;')
conn.select_one(query)
=> ["2008-11-05 20:53:26.000"]

## config.cache_classes = false
query = "select TOP 1 [EPOLeafNode].[Lastupdate] AS last_update from [EPOLeafNode]"
conn = DBI.connect('dbi:ODBC:DRIVER=FreeTDS;TDS_Version=8.0;SERVER=sql;DATABASE=EOP;uid=uid;pwd=pwd;')
conn.select_one(query)
=> [[Wed, 05 Nov 2008 20:53:26 +0000]]

Is this a bug, or expected behavior that I don't understand? Can I override it, preferably to always coerce the data? If not, any thoughts on the best way to massage the data so it gives the same result in development and production?

Answer 1

No, this behavior is not an expected interaction.

As a quick and dirty workaround, you can explicitly CAST the Lastupdate field into a string, which ought to work in both environments.rb.

Conversion to a simple numeric type, like a UNIX timestamp, might work, too.

I can't say what the root problem is, and I can't reproduce on the same versions of Rails/AR and DBI, though admittedly with a different backend. There's a fair bit of dynamism in both packages, however, and AR partially loading, or modifying, a supporting class might not do what the DBI expects. (Some time ago, for example, the AR adapter for the old postgres driver monkey patched the base driver, breaking it for older versions of the associated DBD.)