RE: [Rails] Re: Error writing blob to sqlserver

It’s entirely possible I spoke too soon. I’ve patched sqlserver adapter to apparently write blobs properly without needing ADODB.Stream mediation, thanks to some instructions kindly provided by Patrick Spence. The relevant changes to the file are:

module ActiveRecord

class Base

def self.binary_to_string(value)

  value

end

def self.string_to_binary(value)

  value

end

end

class SQLServerAdapter < AbstractAdapter

  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

end

end

now the blobs appear to be stored properly, even those over 7k in length, although I can’t retrieve the data using AR as expected, I get an array of integers, bytes presumably, that I haven’t yet figured out how to reconstitute into a byte “string”, at least of the form that FlexImage would appreciate.

Patrick’s code is:

require ‘win32ole’

#-- SQL Server database connection string, change as you see fit
$connString = “Provider=SQLOLEDB;Data Source=xxxx;Initial Catalog=xxxx;Integrated Security=SSPI;”

def storeImage()

begin

#-- change this!
pdfFile = "<fully qualified file name for the .pdf file>"

oCn = WIN32OLE.new("adodb.connection")
oCn.connectionString = $connString
oCn.open()

#-- open file in readonly/binary mode
file = File.open(pdfFile, "rb")
pdfImage = ""

print("\nReading file... ")

file.each_byte {|byte|
  pdfImage << sprintf("%02X", byte)
}

pdfImage.insert(0, "0x")
puts("OK")

sqlInsert = "INSERT INTO pdffiles (pdffile, pdfimage) VALUES ('#{File.basename(pdfFile)}', #{pdfImage})"
oCn.execute(sqlInsert)

rescue Exception => ex

puts(ex.message())

ensure

pdfImage = ""
file.close() unless file.nil?
oCn.close() unless oCn.nil?

end

end

It’s possible some other clever person can stitch this all together into something that works. I may give it a real try over the weekend.

  • donald

hmmm, I'm still not having luck storing large files with this method.

To decode the binary string, I've used this:

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

Well, it took a while, but here's one way: Net::HTTP in Ruby can send
form data as though it came from an HTML form. However, it can't send
multipart form data without some modification. I cribbed most of this
code from http://www.pivotalblabs.com/articles/tag/ruby, who borrowed
it elsewhere.

I put multipart.rb in my ROR /lib: