Hi!
<cut>
mysql> create table blobs (id integer, a_blob mediumblob);
>>> Query OK, 0 rows affected (0.12 sec)
Gerrit> 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
Gerrit> 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)
Gerrit> 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)
Gerrit> It's there, and it has the size one would expect.
Gerrit> 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)
This is wrong; You should retrieve it with 'SELECT a_blob from blobs'
and handle the file creation in your client.
Gerrit> 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
Gerrit> Surprise! One would expect that "theempeg.mpg" would again be 296268
Gerrit> bytes.
Gerrit> But instead, it is slightly larger, and no longer a valid MPG file.
This is because SELECT into outfile is designed to handle multiple row
files. To do this, it uses spaces to pad all lines to the same length.
Gerrit> I hope this example illustrates my problem. How can I make sure that
Gerrit> whatever I put into the database, comes out again EXACTLY the same?
Gerrit> If they're not escaped when writing in an "outfile", then apparently
Gerrit> the blobs get modified (escaped?) when loading them using load_file().
Gerrit> But the manual says load_file can be used to load blobs....
Gerrit> The fact that the file after writing to an outfile is larger than
Gerrit> the octect_length, suggests that outfile() modifies the blob.
See above.
Gerrit> When I send the queries using MyODBC, the results are identical:
Gerrit> after storing and retrieving the blobs, they are no longer of the
Gerrit> correct size, and have become invalid.
This is something completely different. MyODBC should give exactly
back what you stored. Any change you can produce an example of this?
Anyway, Try MySQL 3.23.5; I added a patch that in the case you use a
blob, mediumblob or longblob in the result, MySQL will not anymore try
to pad it with space...
Regards,
Monty
