How to submit blob data into MySQL from ruby without base64 encoding

Question

I have searched without much success how to best submit binary data into a MySQL field of type BLOB, without doing a base64 encoding which increases the size of the data.

So far my Ruby code looks something like this:

require 'zlib'
require 'base64'
require 'mysql'

#Initialization of connection function
  db = Mysql.init
  db.options(Mysql::OPT_COMPRESS, true)
  db.options(Mysql::SET_CHARSET_NAME, 'utf8')
  dbh = db.real_connect('hostname','username','password','database') #replace with appropriate connection details

#Saving the data function
  values=someVeryBigHash
  values=JSON.dump(values)
  values=Zlib::Deflate.deflate(values, Zlib::BEST_COMPRESSION)
  values=Base64.encode64(values)
  dbh.query("update `SomeTable` set Data='#{values}' where id=1")
  dbh.close if dbh

#Retrieving the data function
  res=dbh.query("select * from `SomeTable` where id=1")
  data=res['Data']
  data=Base64.decode64(data)
  data=Zlib::inflate(data)
  data=JSON.parse(data)

The issue is that using Base64 encoding/decoding is not very efficient and I was hopping for something a bit cleaner.

I also tried an alternative using Marhsal (which does not allow me to send the data without a base64 encoding, but is a bit more compact)

 #In the saving function use Marshal.dump() instead of JSON.dump()
 values=Marshal.dump(values)

 #In Retrieve function use Marshal.load() (or Marshal.restore()) instead of JSON.parse(data)
 data=Marshal.load(data)

However, I get some errors (perhaps someone spots what I do wrong, or has some ideas why this occurs):

incompatible marshal file format (can't be read) version 4.8 required; 34.92 given

I tried different flavor of this with/without Base64 encoding or decoding or with/without ZLib compression. But I seem to consistently get an error.

How would it be possible to send binary data using Ruby and mysql gem, without base64 encoding. Or is it simply a requirement to use base64 encoding for sending the data?

mcfinnigan · Accepted Answer · 2014-10-21 11:05:43Z

2

The issue is that using Base64 encoding/decoding is not very efficient and I was hopping for something a bit cleaner.

You're using JSON to convert a large hash to a string, then using ZLib compression on binary data, then Base64 encoding the resulting binary data, and you're worried about efficiency... I'm going to assume you mean spatial efficiency rather than temporal efficiency.

I guess I'm most curious about why you're Base64 encoding in the first place - a BLOB is a binary data format, and provided you pass an array of bytes to ZLib it should inflate it correctly regardless.

Have you tried writing binary data directly to the database? What issues did you experience.

Edit:

update SomeTable set Data='xڍ�]o�0��K$�k;�H��Z�*XATb�U,where id=1' resulted in an error... Obviously this has to do with the binary nature of the data. This captures the essence of my question. Hope you shine some light on this issue.

You can't just pass the binary string as a query value as you have here - I think you need to use a query with a bind variable.

I'm unsure whether the mysql gem you're using supports query bind parameters, but the format of query you'd use is something along the lines of:

@db.execute('update SomeTable set Data=? where id = 1', <binary data value>)

this will permit the mysql to properly escape or encapsulate the binary data that is to be inserted into the database table.

edited Oct 21, 2014 at 11:05

answered Oct 21, 2014 at 10:32

mcfinnigan

11.7k38 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

GMCB Over a year ago

Thanks for the prompt answer. Yes I am mostly worried about the spacial information and database overhead (ie. code is run on a cluster with thousands of cores). I would love to be able to submit the data directly to the database in binary format, but when I do this I get some errors.

mcfinnigan Over a year ago

Post the errors here - it's better to solve the root problem rather than work around it, and as you say Base64 encoding here is very much a workaround.

GMCB Over a year ago

update SomeTable set Data='xڍ�]o�0��K$�k;�H��Z�*XATb�U,where id=1' resulted in an error... Obviously this has to do with the binary nature of the data. This captures the essence of my question. Hope you shine some light on this issue.

GMCB Over a year ago

'Error#:1064' message:'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'g��#�>�"0��dx��b�Tq�^�#��(K��>�d)�' where id=1' at line 1'

GMCB Over a year ago

It seems that perhaps the issue comes from the fact that some of the binary code converts to "'" (quotes), and this breaks the SQL syntax. Anyways, I have no clue how I could use the mysql ruby gem to send the binary content of my text variable.

|

GMCB · Accepted Answer · 2014-10-22 04:46:34Z

To sumarize mcfinningan answer. Transmitting the binary data is done via binding a parameter. In ruby this can be done with 'mysql' gem this can be done using prepared statments (cf. MySQL Ruby tutorial)

The code now looks like:

require 'zlib'
require 'mysql'

#Initialization of connection function
  db = Mysql.init
  db.options(Mysql::OPT_COMPRESS, true)
  db.options(Mysql::SET_CHARSET_NAME, 'utf8')
  dbh = db.real_connect('hostname','username','password','database') #replace with appropriate connection details

#Saving the data function (can skip Zlib compression if needed)
  values=someVeryBigHash
  values=Marshal.dump(values)
  values=Zlib::Deflate.deflate(values, Zlib::BEST_COMPRESSION)

  #Here is how to load the binary data into MySQL (assumes your schema has some tale with a Column Data of type BLOB
  dbh.prepare("update `SomeTable` set Data=? where id=1")
  dbh.execute(data)
  #End of Data loading

  dbh.close if dbh


#Retrieving the data function (can skip Zlib decompression if data is not compressed)
  res=dbh.query("select * from `SomeTable` where id=1")
  data=res['Data']
  data=Zlib::inflate(data)
  data=Marshal.restore(data)

Collectives™ on Stack Overflow

How to submit blob data into MySQL from ruby without base64 encoding

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related