deNZity
asked on
how to insert blob from command line
I have a mysql table that I want to insert Blobs(pdf's) into from the commandline.
The pdf's are on workstation with network connection to mysql server.
D
The pdf's are on workstation with network connection to mysql server.
D
ASKER
thanks for the reply.
So basically I cant store pdf files just their data?
what about word documents would the same apply?
or would I have to use .txt files?
if I have to escape all the data in the files It will take forever :(
shame I can't just ftp them straight into the database lol.
So basically I cant store pdf files just their data?
what about word documents would the same apply?
or would I have to use .txt files?
if I have to escape all the data in the files It will take forever :(
shame I can't just ftp them straight into the database lol.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Never heard of LOAD_FILE before. It seems much better to use that than to stick the data in a big sql statement like I suggested.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the replies. what I want is for the user to click a link and with php behind the button have the pdf retrieved
from the database and opened; which would be easier to do by having the pdf's in a folder, but I want to learn about
Mysql so decided to do it this way.
D
from the database and opened; which would be easier to do by having the pdf's in a folder, but I want to learn about
Mysql so decided to do it this way.
D
INSERT INTO mytable (id, pdfblob) VALUES (<id number goes here>, <escaped pdf data goes here>);
Then run "mysql databasename < ins.sql", possibly with extra command-line options for user and password, and you're set.
The tricky bit is creating the escaped pdf data, since it is potentially binary data that likes to contain syntax-confusing characters.
One way to do it is with single-quotes and backslashes.
'<backslash-escaped data goes here>'
That will look like:
'asdfajw\\dfas\'\"....lots
To backslash-escape data, make these 4 replacements:
replace ASCII NUL character (not the zero-digit, but the NUL byte) with the two characters backslash and zero ( \0 )
replace the backslash character with two backslash characters ( \\ )
replace the single-quote character with the two characters backslash and single-quote ( \' )
replace the double-quote character with the two characters backslash and double-quote ( \" )
This will make the escaped data a percent or so bigger than the original pdf.
Another way is with hex encoded data, and no single-quote delimiters.
0x<hex-encoded-data goes here>
That will look like:
0x1234ac37e9a01....lots and lots more....0215faedc0a2e
You just replace each ascii character with its two-digit hex equivalent. This will make the escaped data twice as large as the original pdf.
Either way I think you'll need a simple little piece of code that filters the pdf file into an acceptable escaped format.
When the blob gets stored in the database, it will have the same size as the original data since mysqld unescapes the data before it writes it to disk.