Solved

how to insert blob from command line

Posted on 2006-06-22
6
9,635 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Closing database connection after prepared statement 1 44
html input clean up 3 47
Amazon Redshift 2 26
PHP - AJAX and MySQL it works only if the value is a number 12 36
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

13 Experts available now in Live!

Get 1:1 Help Now