> > I've got a database (MySQL 3.23.4) in which I would like to store
> > multimedia data (gifs, jpegs, mpeg, mp3, .ram etc). I can store blobs
> > into
> > tables using the new load_file() function, but how do I get blobs out
> > of the database unescaped? When I use "select... into outfile" the
> > binary
> > data is escaped so that it isn't valid anymore. I don't know where data
> > is mangled, but the outfile() just is bigger than the file read using
> > load_file().
> On retrieving, binary data are sent to client UNESCAPED, i.e. in their
> original, binary form. They are only escaped if written to a text
> You shold use your client API, though, to retreive a length of each
> particular field.
I'm using MyODBC as client, but if I understand correctly, the file
doesn't go throught the client if I just use load_file() and outfile().
What I'm doing (in mysql) is the following:
I create a table to store blobs.
>> mysql> create table blobs (id integer, a_blob mediumblob);
>> Query OK, 0 rows affected (0.12 sec)
Then I have an mpeg file of 296268 bytes
>> 718:)demeter:/tmp/> ls -al anempeg.mpg
>> -rw-r--r-- 1 hiddinkg ctit 296268 Oct 19 15:39 anempeg.mpg
I then insert this mpg file into the database using load_file:
>> mysql> insert into blobs values (1, load_file("/tmp/anempeg.mpg"));
>> Query OK, 1 row affected (0.46 sec)
I verify that it's in there:
>> mysql> select length(a_blob) from blobs where id=1;
>> | length(a_blob) |
>> | 296268 |
>> 1 row in set (0.04 sec)
It's there, and it has the size one would expect.
Then I retrieve it from the database into a file:
>> mysql> select a_blob from blobs where id=1 into outfile '/tmp/theempeg.mpg';
>> Query OK, 1 row affected (0.04 sec)
and I check to see if it did end up in the directory:
>> 719:)demeter:/tmp/> ls -al theempeg.mpg
>> -rw-rw-rw- 1 hiddinkg ctit 309469 Oct 19 15:43 theempeg.mpg
Surprise! One would expect that "theempeg.mpg" would again be 296268
But instead, it is slightly larger, and no longer a valid MPG file.
I hope this example illustrates my problem. How can I make sure that
whatever I put into the database, comes out again EXACTLY the same?
If they're not escaped when writing in an "outfile", then apparently
the blobs get modified (escaped?) when loading them using load_file().
But the manual says load_file can be used to load blobs....
The fact that the file after writing to an outfile is larger than
the octect_length, suggests that outfile() modifies the blob.
When I send the queries using MyODBC, the results are identical:
after storing and retrieving the blobs, they are no longer of the
correct size, and have become invalid.
What is the problem here?
Thanks in advance, Gerrit
University of Twente, The Netherlands
Centre for Telematics and Information Technology