List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 20 1999 1:06am
Subject:Re: retrieving a BLOB
View as plain text  


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


retrieving a BLOBGerrit Hiddink19 Oct
  • Re: retrieving a BLOBsinisa19 Oct
  • Re: retrieving a BLOBGerrit Hiddink19 Oct
    • Re: retrieving a BLOBMichael Widenius20 Oct
      • Re: retrieving a BLOBdesenvolvimento5 Apr
    • Re: retrieving a BLOBsinisa20 Oct
  • Re: retrieving a BLOBGerrit Hiddink20 Oct
    • Re: retrieving a BLOBMichael Widenius20 Oct