Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how to insert blob from command line

Posted on 2006-06-22
6
9,685 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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