Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to insert blob from command line

Posted on 2006-06-22
6
Medium Priority
?
9,811 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
[X]
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
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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 …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…
Suggested Courses

730 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