# Re: Error writing blob to sqlserver **URL:** \ **Category:** Rails\ **Created:** [February 22, 2007, 9:51pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516 "2007-02-22T21:51:48Z")\ **Posts on this page:** 5\ **Page:** 1
**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 22, 2007, 9:51pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516/1 "2007-02-22T21:51:48Z")
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](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](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 ---
**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, 2:05pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516/2 "2007-02-23T14:05:37Z")
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)” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > > > 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](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](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 * * * This email has been scanned by the MessageLabs Email Security System. For more information please visit [Email Security](http://www.messagelabs.com/email) * * * ---
**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, 4:36pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516/3 "2007-02-23T16:36:05Z")
could you elaborate on your work around? ---
**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, 11:25pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516/4 "2007-02-23T23:25:14Z")
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 On 2/22/07, Ball, Donald A Jr (Library) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > a work task and I have yet to be bored enough outside of work to do anything > about it. 🙂 > > - 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.” ---
**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, 7:40pm UTC](https://www.ruby-forum.com/t/re-error-writing-blob-to-sqlserver/86516/5 "2007-02-23T19:40:32Z")
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](http://example.com/doc_services/examples/deliver.cfm?id=23)” or “[example.com/doc\_services/examples/download.cfm?id=23](http://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](http://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 M.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 02/23/2007 10:34 AM \>\>\> > > > could you elaborate on your work around? On 2/23/07, Ron P. \ wrote: 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)” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > > > 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](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](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 * * * This email has been scanned by the MessageLabs Email Security System. For more information please visit [Email Security](http://www.messagelabs.com/email) * * * * * * This email has been scanned by the MessageLabs Email Security System. For more information please visit [Email Security](http://www.messagelabs.com/email) * * *