The data isn't actually being corrupted. The problem here is that
SELECT INTO OUTFILE wants to escape all of the line breaks as to make it
easy to reimport the data using LOAD DATA INFILE. You could work
around that by setting the escape character using: FIELDS ESCAPED BY '';
The total query would be:
SELECT data FROM test WHERE id=1 INTO OUTFILE "/tmp/test2.gif" LINES
ESCAPED BY '';
That seems to work pretty well for me.. However it seems to be adding
an extra line break at the end of the file for some odd reason, not sure
why.
Most people usually will just select the data and then do whatever needs
to be done in the language you are writing your application in. It
would be like a 5 line script in either perl or php. Hope this helps
you out some.
Harrison
Miles Martin wrote:
>Hello there,
>
>I have a problem that I need some help with. I am trying to insert binary
>data into a mysql database and simply retrieve it. each time I retrieve the
>data, it is corrupt. What am I doing wrong?
>
>I am running mysql 3.23.41 on Red Hat Linux release 7.2 (Enigma) Kernel
>2.4.7-10 on an i686.
>
>My table is defined as:
>
>CREATE TABLE TEST(ID TINYINT(3),DATA LONGBLOB);
>
>To insert data into the table I am using:
>
>INSERT INTO TEST VALUES(1,LOAD_FILE('/tmp/test.gif'));
>
>The data has been added. I then retrieve the data like so:
>
>select data from test where id=1 into outfile "/tmp/test2.gif";
>
>The returned file is no longer recognised as an image file by a web browser
>(Netscape). I have tried the same exercise with .mp3 files and have had the
>same problem. The files have the same size and do contain data but they are
>corrupt and cannot be read by the program that would read them.
>
>I also tried the same using a text file so that I could see what the
>difference was between the "before" and "after" inserting into mysql. See
>links below for "before" and "after" examples of text file and gif image.
>
>Gif image...
>http://www.toffeedog.com/personal/mysqlprob/before.gif
>http://www.toffeedog.com/personal/mysqlprob/after.gif
>
>Text file...
>http://www.toffeedog.com/personal/mysqlprob/before.txt
>http://www.toffeedog.com/personal/mysqlprob/after.txt
>
>I look forward to your reply,
>
>Miles
>
>mailto:[EMAIL PROTECTED]
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
