Err, I don't know what I was thinking.
You can simply do a select into dumpfile like:
select data from test where id=1 into dumpfile "/tmp/test2.gif";
That is designed for writing blobs to a file.
Harrison
Harrison C. Fisk wrote:
> 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
>
>
>
>
---------------------------------------------------------------------
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
