Error writing blob to sqlserver

[Marlon Moyer]

I've got a rails app that retrieve tif files from the filesystem,
creates multi-page pdfs and stores them in an image field in the
database. It works great, except for a couple of files. The files it
chokes on are anything really different from the others, they're not
the largest, nor the smallest. Everytime I try to run the app on
these files, I get this error.

DBI::DatabaseError: Not enough SQL parameters: INSERT INTO invoices
([account], [invoice_date], [id], [pdf]) VALUES('BY0905001', '20060912
00:00:00', 13,
<binary data has been removed from the email>')

I can see the EOF marker of the pdf file at the very end of the binary
data, so I know its sending the whole shebang.

I've tried using the DBD:ADO connection and the DBD:ODBC connection,
but both fail in the same way.

Any hints on where to start with this? I don't know if it matters or
not, but on the error output there are a lot of single quote
characters. I'm assuming they're automatically escaped.

--
Marlon

[Ron Phillips]

Marlon Moyer <marlon.moyer@...> writes:

Look at "Skeeterbug's" comment (#15) at
http://weblog.rubyonrails.org/2007/1/5/rails-1-2-release-candidate-2 and at
the bug report at http://dev.rubyonrails.org/ticket/6436

I don't think these are corrected; perhaps that's the problem. I know I couldn't
get acts_as_attachment storage :db_system working with SQL Server at all, but it
worked just fine with MySQL.

Ron

[Marlon Moyer]

dang, that's not the answer I wanted to hear!  :)

--

[Ball, Donald A Jr (Library)]

I did some research into this a little while back as I'm using sqlserver with FlexImage and wanted to store blobs in the database instead of on the filesystem. Alas, the rails sqlserver adapter does not support this. For what it's worth, it would require using an ADODB.Stream object to mediate the blob access instead of an escaped string, at least for blobs over 3k. I can't find my notes offhand, but here are a couple of links for more info:

 

http://support.microsoft.com/kb/258038

 

and some ruby code which may or may not work:

 

http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/208960

 

Unfortunately, modifying the sqlserver adapter to use this code would seem to be a large undertaking. It might require a considerable rethinking of the database adapter architecture, perhaps modelling sql statements as objects instead of strings. It's probably worthwhile, I bet the oracle adapter would benefit from this as well since, as I recall, working with its blobs requires a similar effort, but it's beyond what I can personally justify as a work task and I have yet to be bored enough outside of work to do anything about it. :)

 

- donald

[Ron Phillips]

Well, the workaround I am using might be some help, then. It's not a ROR answer, but Coldfusion handles BLOBs in SQL Server just fine. There's a free "code-clone" called BlueDragon that I've heard good things about, too.

 

It's a kludge, but the app is running and I'm on to the next problem.

 

Ron

>>> "Ball, Donald A Jr (Library)" <donal...@nashville.gov> 02/22/2007 3:50 PM >>>

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

[Marlon Moyer]

could you elaborate on your work around?

[Ron Phillips]

I wrote three scripts in CF: one to upload, one to download, and one to "deliver", by which I mean make available in a web page as an image or such. There's not a lot of difference between the download and deliver scripts -- just the "Content-disposition" header. Coldfusion works just fine with SQL Server BLOBs, as long as you use a <cfqueryparam cfsqltype="cf_sql_blob"> for the actual binary content.

 

When my ROR page needs a file from the db, it hits "example.com/doc_services/examples/deliver.cfm?id=23" or "example.com/doc_services/examples/download.cfm?id=23" . It's invisible to the user, pretty much. To upload, I just made a form in ROR with the action set to "example.com/doc_services/examples/upload.cfm" and it works -- no one's the wiser. (The upload.cfm script includes a <cflocation> tag to hit the "../show/id" URL for the record to which the asset is attached.)

 

Here's my _edit_asset.rhtml partial:

 

<form action="http://example/doc_service/examples/upload.cfm" method="post" name="upload_form" enctype="multipart/form-data" id="upload_form">Enter Username:
    <input type="text" name="username" id="username" />Enter Password:
    <input type="password" name="pwd" id = 'pwd'/>
    <input type="hidden" name="signal_id" id = 'signal_id' value='<%= @control_signal.id %>' />
    <br />
    File:
    <input type="file" name="ul_path" id="ul_path" size="50"/><br />
    Comments:
    <input type="text" name="comments" id="comments" size = "50" />
    <input type="submit" name="upload_now" value="submit" />
</form>

 

I hope that helps. It's kind of the long way around the barn, but it makes use of ROR's nicely-structured approach where it can, and uses CF just for handling the SQL Server BLOB type.

 

Ron

 

>>> "Marlon Moyer" <marlon...@gmail.com> 02/23/2007 10:34 AM >>>

[Ball, Donald A Jr (Library)]

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

[Marlon Moyer]

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

--
"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."

[Marlon Moyer]

Has anyone had any luck using adodb.stream. I'm trying to use it to
write the contents of an RMagick generated blob into the database.

It blows up when I get to this:

mstream.write(Invoice.pdf) #Invoice.pdf is a dynamically generated pdf
blob which registers as a string

The error I get is "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another"

I've tried, Invoice.pdf.to_a, Invoice.pdf.each_byte { |byte| byte }

each results in the same message. Here is what the adodb.stream requires

write (buffer)

buffer: Required. An array of bytes to be written to a binary Stream object

[Marlon Moyer]

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

--
"He's been fortunate, he cannot deny.
Redheads, blondes, brunettes, all fall for his lines"

[paron]

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 Moyer" <marlon.mo...@gmail.com> wrote:
> could you elaborate on your work around?
>