Generating MySQL UPDATE statements containing BLOB image data

Question

I'm trying to write an SQL statement that will generate an SQL script that will update a BLOB field with an IMAGE being selected from the database.

This is what I have:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               QUOTE( THUMBNAIL ), 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

In the above, THUMBNAIL is a BLOB field containing raw image data. When I run the resulting script I get the following error:

ERROR at line 2: Unknown command '\\'.

I first tried this without the QUOTE() function, like so:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = \'', 
               THUMBNAIL, 
               '\' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

Running the resulting script produces this error:

ERROR at line 2: Unknown command '\0'.

What is the proper function to apply to this BLOB field in the select, so the UPDATE statements will work?

If context is required, I'm looking to migrate thumbnails generated on one server to another server for certain image IDs only. I would use mysqldump, but I don't want to clobber the entire table.

Any help is greatly appreciated!

Answer 1

You must work with hexadecimal format blob. It looks like this:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               0xC9CBBBCCCEB9C8CABCCCCEB9C9CBBB...., 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

Or you might find it handy to use the Openrowset command