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 [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?

Capture

Answer 1

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

Capture

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.

Universalist

My understanding is LOAD_FILE requires the file to be present on the remote machine hosting the MySQL server. I do not have such access. – Capture

you're right. I missed the part about the mysql being remote. I would guess you'll have to code this up in your favorite programming language. This should be doable in under 100 lines in any proper language. – Universalist

Answer 3

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.

Chamomile

Maybe I wasn't clear about this in the post... I am not using any GUI. I just want to upload a PDF file to the SQL database from the command-line. INSERT INTO table SET pdfBlob=<BINARY-PDF-FILE>, name='stupid.pdf'; My question is how to generate <BINARY-PDF-FILE> from 'stupid.pdf' – Capture

Sorry, I thought you were currently using a PHP-based client to upload files. That would have given you a clear command-line approach using curl. – Chamomile

Answer 4

See this article in the MySQL cookbook which includes a sample script as well:

http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-CHP-17-SECT-7.html

It has some restrictions though, most notably that the file must be stored on the same host as the mysql server.

Serin

Answer 5

Her a version without LOAD_FILE

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create a connection to the database
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Read the file content into a variable
$file_content = file_get_contents('/path/to/file.txt');

// Escape the file content for use in SQL
$file_content_escaped = $conn->real_escape_string($file_content);

// SQL query
$sql = "INSERT INTO table_name (column_name) VALUES ('$file_content_escaped')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close the connection
$conn->close();
?>

MariaDB Manual - LOAD_FILE()

Note: Statements using the LOAD_FILE() function are not safe for statement based replication. This is because the slave will execute the LOAD_FILE() command itself. If the file doesn't exist on the slave, the function will return NULL.

Bastia