# Re: Error writing blob to sqlserver

**URL:** <https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625>\
**Category:** Rails\
**Created:** [February 23, 2007, 9:24pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625 "2007-02-23T21:24:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [February 23, 2007, 9:24pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625/1 "2007-02-23T21:24:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [February 23, 2007, 10:17pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625/2 "2007-02-23T22:17:04Z")

</div>

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

On 2/23/07, Ball, Donald A Jr (Library) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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.”

---

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [June 25, 2007, 5:00pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625/3 "2007-06-25T17:00:26Z")

</div>

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) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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”

---

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [July 6, 2007, 3:59pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86625/4 "2007-07-06T15:59:41Z")

</div>

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](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.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> could you elaborate on your work around?