One of my ongoing projects is a rich client application written in Java.  A recent request from the users was the ability to add handwritten notes or drawings to some of their records.  It's simple enough to give the users a simple canvas on which they can draw and store that data as an image.  However, we want these notes to be available to the companion web application that runs on our server.  The clients synchronize their databases with the server fairly regularly using a database sync tool called Pervasync, so it should be possible to store the images as a BLOB in the MySQL database on the client and have them sent to the server when the client and server databases are synchronized.  As with any vendor claim, I wanted to test whether or not Pervasync could synchronize BLOB data properly.  

To test this, I needed to import a binary file of an image into a test client database, synchronize it to the server, export the BLOB to a file and make sure the image was intact.  After a little digging around, it turns out that the SQL to accomplish this is fairly simple.  I created a table called "image_test", with two fields.  The first is a simple numeric ID field, the second a MEDIUMBLOB.  Inserting an image from a file on disk is just this simple:

1

INSERT INTO image_test (id, image) VALUES (1, LOAD_FILE('c:/test/test.png'));

After running the data synchronization, I needed to get the BLOB data back out and into a file.  MySQL makes this easy.  The output from any query can be dumped to a file by using the INTO OUTFILE/DUMPFILE syntax in a select statement.  This is very handy for scheduled jobs that take data from a MySQL database and make it available to other systems.  For example:

1

SELECT * FROM table_name INTO OUTFILE 'c:/test/outfile.txt'

The SQL statement above will take all of the data in the table_name table and dump it out to a text file.  This statement can also take the same export_options parameters used while importing a file to set things such as the field and line delimiters.  But wait, aren't we dealing with binary data?  Wouldn't field and line delimiters just muck things up?  Yep, and that's why MySQL provides the DUMPFILE option instead.  Using INTO DUMPFILE instead of INTO OUTFILE, all of the returned data is dumped to a file as a single line.  This is just the ticket for fetching my image data and storing it in a local file for verification.  The SQL looks like this:

1
2
3
4

SELECT image 
INTO DUMPFILE 'C:/test/test2.png'
FROM image_test 
WHERE id = 1;

When I first tried to run this under the MySQL workbench it kept choking with an error code 1064, complaining about the syntax.  By default, the MySQL workbench inserts a LIMIT 0, 1000 statement into queries before they run.  This causes the statement above to fail.  To turn off the limit, go to the "edit" menu in MySQL workbench and select "preferences".  In the SQL Editor tab, uncheck the "limit rows" option and the query should run, outputting the BLOB data to a file.

Happy Coding!