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

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. :slight_smile:

  • donald

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)” donald.ball@nashville.gov 02/22/2007 3:50 PM >>>

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. :slight_smile:

  • donald

could you elaborate on your work around?

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 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 tag to hit the “…/show/id” URL for the record to which the asset is attached.)

Here’s my _edit_asset.rhtml partial:

Enter Username: Enter Password:
File:
Comments:

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.moyer@gmail.com 02/23/2007 10:34 AM >>>
could you elaborate on your work around?

signals.zip (20.4 KB)

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