Making The Leap

Rails and Microsoft SQLServer don’t seem to be the best of friends, particularly when consider blob images. I’ve outlined the steps that I had to cruft support for better binary support in MS SQLServer. Now, not all of this is my doing. A lot of the code was lifted from a sqlserver thread in the Ruby on Rails mailing list.

First off, download the SQL Native Client from Microsoft. This new provider has better support for statements longer than 8k. (About Time!)

Next, modify ADO.rb so that the execute method reads like so:

  def execute
    # TODO: use Command and Parameter
    # TODO: substitute all ? by the parametes
    # if there are no params, avoid the expensive scan operation of bind
    if @params.length > 0
        sql = bind(self, @statement, @params)
    else
        sql = @statement
    end

    @res_handle = @handle.Execute(sql) 
    # TODO: SELECT and AutoCommit finishes the result-set
    #       what to do?
    if @db['AutoCommit'] == true and not SQL.query?(@statement) then
      @db.commit
    end

  rescue RuntimeError => err
    raise DBI::DatabaseError.new(err.message)
  end

There’s a relatively expensive scan operation in the bind operation that really chokes when binary content is sent to it.

In the sqlserver_adapter.rb file, modify the the following functions:

  def self.string_to_binary(value)
    '0x'+value.unpack('H*').to_s
  end

  def self.binary_to_string(value)
    if value.kind_of? Array
      value.map {|c| c.chr}.join
    else
      value
    end
  end

Also, modify the Base class inside sqlserver adapter like so:

module ActiveRecord
  class Base
    def self.sqlserver_connection(config) #:nodoc:
      require_library_or_gem 'dbi' unless self.class.const_defined?(:DBI)

      config = config.symbolize_keys

      mode        = config[:mode] ? config[:mode].to_s.upcase : 'ADO'
      username    = config[:username] ? config[:username].to_s : 'sa'
      password    = config[:password] ? config[:password].to_s : ''
      provider      = config[:provider] ? config[:provider].to_s : 'SQLOLEDB'
      autocommit  = config.key?(:autocommit) ? config[:autocommit] : true
      if mode == "ODBC" 
        raise ArgumentError, "Missing DSN. Argument ':dsn' must be set in order for this adapter to work." unless config.has_key?(:dsn)
        dsn       = config[:dsn]
        driver_url = "DBI:ODBC:#{dsn}" 
      else
        raise ArgumentError, "Missing Database. Argument ':database' must be set in order for this adapter to work." unless config.has_key?(:database)
        database  = config[:database]
        host      = config[:host] ? config[:host].to_s : 'localhost'
        driver_url = "DBI:ADO:Provider=#{provider};Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};" 
      end
      conn      = DBI.connect(driver_url, username, password)
      conn["AutoCommit"] = autocommit
      ConnectionAdapters::SQLServerAdapter.new(conn, logger, [driver_url, username, password])
    end
  end # class Base

  def type_cast(value)
    return nil if value.nil? || value =~ /^\s*null\s*$/i
    case type
    when :datetime  then cast_to_datetime(value)
    when :timestamp then cast_to_time(value)
    when :time      then cast_to_time(value)
    when :date      then cast_to_datetime(value)
    when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1'
    else super
    end
  end

  def quote(value, column = nil)
    return value.quoted_id if value.respond_to?(:quoted_id)

    case value
      when String
        if column && column.type == :binary
            column.class.string_to_binary(value)
        else
            super
        end
      when TrueClass             then '1'
      when FalseClass            then '0'
      when Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'" 
      when Date                  then "'#{value.strftime("%Y%m%d")}'" 
      else                       super
    end
  end

Make sure to use SQLNCLI as your provider in your database.yml and you should be golden! I’ve tested this with files up to 12 meg. 50 meg files caused the interpreter to crash.