[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.

[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?

[Author]

Posts