storing binary data in mysql

Question

I have a PDF file on a local machine. I want to upload this file into a BINARY BLOB on a SQL database. Other approaches mentioned here [http://stackoverflow.com/questions/17/binary-data-in-mysql] all use PHP. I want a simple clean way to upload this PDF file on the Linux command line. Unfortunately, I do not have access to the remote filesystem so cannot just store links to the file as mentioned elsewhere... I sort of need to use this MySQL database as a virtual filesystem for these PDF files..

From the PhP example, it seems all that is required is to escape the slashes before using the INSERT command? Is there a simple way to achieve that on a Linux command-line?

Answer 1

You could use the curl browser to submit the same POST that your GUI browser does. Sniff the request that your GUI browser sends, then replicate that with curl.

Answer 2

You could use the mysql function LOAD_FILE in conjunction with a small shellscript to do this I guess.

Untested code follows:

#!/bin/bash

if [ -z $1 ]
then 
    echo "usage: insert.sh <filename>"
else
    SQL="INSERT INTO file_table (blob_column, filename) VALUES(LOAD_FILE('$1'), '$1')"

    echo "$SQL" > /tmp/insert.sql
   cat /tmp/insert.sql | mysql -u user -p -h localhost db
fi

And you could use it like this:

<prompt>./insert.sh /full/path/to/file

Better implementation with error checking, proper tempfile creation, escaping and other niceties is left as an exercise to the reader. Note that use of LOAD_FILE() requires the FILE privilege in MySQL and a full path the file.

Answer 3

Not sure if this completely solves my problem, but I found a filesystem layer implemented on top of MySQL. I guess I can use that to store my PDF files as BLOBs automatically... still need to figure out how to store keys to the PDF file in this filesystem for structured access to the PDF file based on something more meaningful than < inode,seq >.

http://sourceforge.net/projects/mysqlfs/ and
http://www.linux.com/archive/feature/127055