# How to save Ruby object as a blob in MySQL **URL:** \ **Category:** Ruby\ **Created:** [September 4, 2008, 10:57pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745 "2008-09-04T22:57:04Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 4, 2008, 10:57pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/1 "2008-09-04T22:57:04Z")
Hello all, I’m using Ruby and Sequel on Windows to talk to a database on a Linux machine. The Sequel-to-MySQL connection is working fine – I can save and query for data no problems. However, I’m trying to save a Ruby object (an Array at this point) as a blob in the MySQL database and when I do I get some crazy stuff going on. The code I am running is below: require ‘rubygems’ require ‘sequel’ DB = Sequel.connect ‘mysql://test:test@172.16.234.1/sequel’ DB.create\_table :items do column :name, :text column :stuff, :blob end test = Array.new test \<\< 145.0 test \<\< 231.56 b = Marshal.dump(test) items = DB[:items] items \<\< { :name =\> ‘Test’, :stuff =\> b } When I run this I get no errors at all. However, when I move over to my Linux machine where the MySQL server is running and do a ‘select \* from items’ query it looks like the blob data is overflowing out of the table and my command line gets all jacked up (see the attachment). The command line stays jacked up even after I exit out of the mysql client console. Any ideas on why this is happening and more importantly how I can successfully marshal a Ruby object to my database?! – Thanks! Bryan ---
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 4, 2008, 11:51pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/2 "2008-09-04T23:51:57Z")
On Thu, Sep 4, 2008 at 3:51 PM, Bryan R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > console. > > Any ideas on why this is happening and more importantly how I can > successfully marshal a Ruby object to my database?! Are you sure it’s not marshaling correctly? What happens when you select that data back out and do Marshal::load ? The problem is more likely your Linux terminal software following instructions you didn’t intend to give it. If you examine the contents of b in your test code, you’ll see some pretty interesting values in there, and one of them is probably messing up your display when you do your select statement in the mysql client. This sort of thing happens all the time when dumping raw binary data to a terminal. And while Marshal::dump returns a string, it’s really binary data. irb(main):001:0\> test = [145.0, 231.56] =\> [145.0, 231.56] irb(main):002:0\> b = Marshal.dump(test) =\> “\004\b[\af\b145f\016231.56\000\270R” In most Linux terminals, you can use the ‘reset’ command to recover when it gets garbled like this. -Michael ---
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 5, 2008, 9:02pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/3 "2008-09-05T21:02:34Z")
On Sep 4, 2008, at 3:01 PM, Bryan R. wrote: > Thanks Michael… you are correct on both accounts. I actually made a > mistake in my syntax that made me think it wasn’t reading back in > correctly. > > – > Thanks! > Bryan In general when you want to store Marshal’d data in mysql you should Base64.encode the data after you Marshal.dump it so that you do not get those weird chars and mysql is better able to handle the data. -Ezra ---
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 5, 2008, 11:00pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/4 "2008-09-05T23:00:16Z")
Ah… interesting. Thanks! I assume I have to Base64.decode the data before using Marshal to load it back into an object… correct? ---
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 5, 2008, 11:06pm UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/5 "2008-09-05T23:06:43Z")
On Sep 5, 2008, at 1:53 PM, Bryan R. wrote: > Ah… interesting. Thanks! I assume I have to Base64.decode the data > before using Marshal to load it back into an object… correct? Yeah exactly, use helper methods like this: ``` def marshal(string) [Marshal.dump(string)].pack('m*') end def unmarshal(str) Marshal.load(str.unpack("m")[0]) end ``` Cheers- -Ezra ---
**Author:** ![roxah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/54ee81/32.png) [@roxah](https://www.ruby-forum.com/u/roxah)\ **Post date:** [September 5, 2008, 12:07am UTC](https://www.ruby-forum.com/t/how-to-save-ruby-object-as-a-blob-in-mysql/147745/6 "2008-09-05T00:07:16Z")
Thanks Michael… you are correct on both accounts. I actually made a mistake in my syntax that made me think it wasn’t reading back in correctly. – Thanks! Bryan Michael L. wrote: > Are you sure it’s not marshaling correctly? What happens when you > select that data back out and do Marshal::load ? The problem is more > likely your Linux terminal software following instructions you didn’t > intend to give it. > > In most Linux terminals, you can use the ‘reset’ command to recover > when it gets garbled like this. > > -Michael