Re: Error writing blob to sqlserver

Rails
1

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
2

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

On 2/23/07, Ball, Donald A Jr (Library) [email protected]
wrote:

  value

%H:%M:%S")}'"
integers, bytes presumably, that I haven’t yet figured out how to

pdfImage.insert(0, "0x")

ensure
something that works. I may give it a real try over the weekend.

  • donald

–
“We all had delusions in our heads,
we all had our minds made up for us,
we had to believe in something,
…so we did.”

3

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

On 2/23/07, Ball, Donald A Jr (Library) [email protected]
wrote:

  value

%H:%M:%S")}'"
integers, bytes presumably, that I haven’t yet figured out how to

pdfImage.insert(0, "0x")

ensure
something that works. I may give it a real try over the weekend.

  • donald

–
“He’s been fortunate, he cannot deny.
Redheads, blondes, brunettes, all fall for his lines”

4

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:
#########################
require ‘net/https’
require “mime/types”

class Net::HTTP::Post
def multipart_params=(param_hash={})
boundary_token = [Array.new(8) {rand(256)}].join
self.content_type = “multipart/form-data;
boundary=#{boundary_token}”
boundary_marker = “–#{boundary_token}\r\n”
self.body = param_hash.map { |param_name, param_value|
boundary_marker + case param_value
when Array
file_to_multipart(param_name, param_value[0], param_value[1])
else
text_to_multipart(param_name, param_value.to_s)
end
}.join(‘’) + “–#{boundary_token}–\r\n”
end

protected
def file_to_multipart(key, file_content, filename)
mime_types = MIME::Types.of(filename)
mime_type = mime_types.empty? ? “application/octet-stream” :
mime_types.first.content_type
part = %Q|Content-Disposition: form-data; name=“#{key}”;
filename=“#{filename}”\r\n|
part += “Content-Transfer-Encoding: binary\r\n”
part += “Content-Type: #{mime_type}\r\n\r\n#{file_content}\r\n”
end

def text_to_multipart(key,value)
“Content-Disposition: form-data; name="#{key}"\r\n\r\n#{value}\r
\n”
end
end
###########################

Then in my controller:
##############################
def update
require ‘multipart’ # actually, this line’s at the top, just after
the class def.
asset = Asset.new(params[:asset])
request_params = asset.attributes
request_params[‘upload_now’]=‘nothingness’
if params[‘ul_path’].respond_to?(‘read’)
#a file was given; TODO: add code to
#sanitize the filename, set the size, and set the extension
request_params[‘ul_path’]= [params[‘ul_path’].read,
‘somename.jpg’]
else
request_params[‘ul_path’]= ‘’
end
request_params[‘username’]=params[:username]
request_params[‘pwd’]=params[:pwd]
conn = Net::HTTP.new(‘localhost’)# or whatever base URL
conn.start do |http|
request = Net::HTTP::Post.new(‘/uber/doc_service/landslides/
update.cfm’)
request.multipart_params=request_params
@response = http.request(request)
end
render :text=>@response.body
end
###############################

It works. It’s not especially pretty, but it works. Good old
Coldfusion has one big advantage: it can talk to anything MS makes, I
believe, including Access. We’re going to keep using it for that
reason (if no other) until someone gets the whole ROR/SQL Server mess
hashed out.

Ron

On Feb 23, 11:34 am, “Marlon M.” [email protected] wrote:

could you elaborate on your work around?