Solved

how to insert blob from command line

Posted on 2006-06-22
6
9,613 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:deNZity
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 16964574
The basics are simple.  Create a file ins.sql that contains this line:
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 and lots more......'
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.  
0
 

Author Comment

by:deNZity
ID: 16964737
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.



0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 250 total points
ID: 16965682
Or just use the LOAD_FILE() function:

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

As of MySQL 5.0.19, the character_set_filesystem system variable controls interpretation of filenames that are given as literal strings.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;


http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 22

Expert Comment

by:NovaDenizen
ID: 16968171
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.
0
 
LVL 22

Assisted Solution

by:NovaDenizen
NovaDenizen earned 250 total points
ID: 16968311
> So basically I cant store pdf files just their data?
I don't understand what you mean.  A pdf file is data.

> what about word documents would the same apply?
Storing a pdf file would be the same as storing a .doc file.

> or would I have to use .txt files?

> if I have to escape all the data in the files It will take forever :(
It wouldn't take that long.

> shame I can't just ftp them straight into the database lol.
Many others have done similar things.  You don't necessarily have to store the pdf or doc inside your database.  You could always just store a pathname or URL of the file in your database, keep the pdf or doc in an archive directory, and let the user grab the file via http or some other method not directly involving the database.

0
 

Author Comment

by:deNZity
ID: 16972381
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now