Improve company productivity with a Business Account.Sign Up

x
?
Solved

how to insert blob from command line

Posted on 2006-06-22
6
Medium Priority
?
9,924 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 1000 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 1000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

608 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