Mass Unload Blob Data

[Steve Fabac]

I am looking for a tool to unload blob data (pictures) stored in a Mysql database

as individual images in a selected directory with each image named with the

value stored in “name_of” field in the Mysql database. Can SQLYog accomplish this?

The following mysql command tool command strings should work but does not:

mysql -u root -D products -e'select imagefile from snf_products where PRODUCT_NUMBER = “10 21020″;' > c:tmp”p308.gif”

mysql -u root -D products -e'select imagefile from snf_products where PRODUCT_NUMBER = “10 21040″;' > c:tmp”p309.gif”

mysql -u root -D products -e'select imagefile from snf_products where PRODUCT_NUMBER = “100a”;' > c:tmp”p310.gif”

mysql -u root -D products -e'select imagefile from snf_products where PRODUCT_NUMBER = “110054”;' > c:tmp”p1.gif”

mysql -u root -D products -e'select imagefile from snf_products where PRODUCT_NUMBER = “110055”;' > c:tmp”p2.gif”

As the created files are truncated (possibly by the shell redirection operator “>”) and unusable.

Using SQLYog and accessing the blob image field and using the “save to file” button works but is

not workable for 1000+ images.

[peterlaursen]

Did you try SQLyog/SJA Notifications Service?

You should be able to execute

select imagefile from snf_products where PRODUCT_NUMBER = “10 21020″;' > c:tmp”p308.gif”;

select imagefile from snf_products where PRODUCT_NUMBER = “10 21040″;' > c:tmp”p309.gif”;

etc..

from a Notifications job.

More smart: call Notifications Services recursively from an application that also replaces the PRODUCT_NUMBER and the file name in the jobfile for each 'run'.  I cananot detail it more as I do not know your situation in details and coding skills.

[Steve Fabac]

peterlaursen wrote on Jan 24 2007, 01:12 AM:

Did you try SQLyog/SJA Notifications Service?

You should be able to execute

select imagefile from snf_products where PRODUCT_NUMBER = “10 21020″;' > c:tmp”p308.gif”;

select imagefile from snf_products where PRODUCT_NUMBER = “10 21040″;' > c:tmp”p309.gif”;

etc..

from a Notifications job.

More smart: call Notifications Services recursively from an application that also replaces the PRODUCT_NUMBER and the file name in the jobfile for each 'run'.  I cananot detail it more as I do not know your situation in details and coding skills.

Peter,

When I tried the above commands in the Notification services wizard for SQLyog 3.71

I get the following:

Error No. 1064

You have an error in your SQL syntax. Check the manual that coresponds to your Mysql version

for the right syntax to use. near ” > tmpp310.gif “

The command as entered:

select imagefile from snf_products where PRODUCT_NUMBER = “100a” ; > tmpp310.gif

select imagefile from snf_products where PRODUCT_NUMBER = “110054” ; > tmpp1.gif

select imagefile from snf_products where PRODUCT_NUMBER = “110055” ; > tmpp2.gif

Does this work with 5.22 version of SQLyog? Should it work with 3.71?

[peterlaursen]

my mistake!

You cannot use the “>” character for redirecting the output from SJA.  SJA is not a console.

You must “SELECT … INTO OUTFILE … “.  This will save on the client machine!  But if you SJA is running on the same machine as the server it is the same thing.

If you use windows filenames do not forget to escape the backslash character like:

Quote:

select … into outfile 'c:\pic.gif' from `thetable` where …. ;

On Linux it would be like

Quote:

select … into outfile '/home/me/mygifs/pic.gif' from `thetable` where …. ;

And I think you should upgrade SJA, not for this simple query but because a lot of bugfixes in between the versions.

[Steve Fabac]

peterlaursen wrote on Feb 1 2007, 05:09 AM:

my mistake!

You cannot use the “>” character for redirecting the output from SJA.  SJA is not a console.

You must “SELECT … INTO OUTFILE … “.  This will save on the client machine!  But if you SJA is running on the same machine as the server it is the same thing.

If you use windows filenames do not forget to escape the backslash character like:On Linux it would be like

And I think you should upgrade SJA, not for this simple query but because a lot of bugfixes in between the versions.

Peter,

Thanks for the tip on select into outfile. It did not work but this did:

select imagefile into dumpfile “/itmp/p308.gif” from snf_products where PRODUCT_NUMBER = “10 21020” ;

select imagefile into dumpfile “/itmp/p309.gif” from snf_products where PRODUCT_NUMBER = “10 21040”;

select imagefile into dumpfile “/itmp/p310.gif” from snf_products where PRODUCT_NUMBER = “100a” ;

select imagefile into dumpfile “/itmp/p1.gif” from snf_products where PRODUCT_NUMBER = “110054” ;

select imagefile into dumpfile “/itmp/p2.gif” from snf_products where PRODUCT_NUMBER = “110055” ;

Create the above in file idump.sql. Use: File -> Open browse to idump.sql.

Then: Edit -> Execute Query -> Execute all Queries (Shift – F5).

And the blob data is unloaded as desired.

The script to assemble the above is idump.awk:

# Awk script to assemble image dump.sql

#”PRODUCT_NUMBER”|”image_file_name”

BEGIN{ FS = “|” }

{

gsub(“””,””)

printf”select imagefile into dumpfile “/itmp/%s” from snf_products where PRODUCT_NUMBER = “%s” ;n”, $2, $1

}

With the source file dump_file.csv:

PRODUCT_NUMBER|IMAGE_FILE_NAME

“10 21020″|”p308.gif”

“10 21040″|”p309.gif”

“100a”|”p310.gif”

“110054”|”p1.gif”

“110055”|”p2.gif”

“110060”|”p3.gif”

“110090”|”p4.gif”

“110091”|”p5.gif”

“110095”|”p6.gif”

And run as: awk -f idump.awk dump_file.csv > dump.sql

Again, thank you. I had posted this problem to http://forums.mysql.com/

and never received any suggestions.

[peterlaursen]

You can then post in your own thread in the MySQL Forums that you found the solution after asking here!

[Author]

Posts