Thursday, March 24, 2011

IBM_DB ActiveRecord Limit Issue

I use ibm-db-2.5.6 and activerecord-3.0.5. I encountered the same problem described in this lighthouse tick https://rails.lighthouseapp.com/projects/8994/tickets/6012-incorrect-sql-generation-for-db2-in-rails-303

It is very simple to fix that. I put the following code snippet into my config/application.rb after your application module. And everything works correctly.

Arel 2.0.9 doesn't have an ToSql visitor for DB2. The interesting thing is some versions of DB2 server actually support LIMIT clause. For example:

this windows version supports LIMIT

Database server = DB2/NT64 9.7.2

but this linux version doesn't

Database server = DB2/LINUXX8664 9.7.2


# this is a patch for ibm_db
if defined?(IBM_DB) && !Arel::Visitors::VISITORS.has_key?('ibm_db')
module Arel
module Visitors
class DB2 < Arel::Visitors::ToSql
private
def visit_Arel_Nodes_Limit o
"FETCH FIRST #{visit o.expr} ROWS ONLY"
end
end

VISITORS['ibm_db'] = Arel::Visitors::DB2
end
end
end