List:General Discussion« Previous MessageNext Message »
From:Gerrit Hiddink Date:October 19 1999 1:56pm
Subject:Re: retrieving a BLOB
View as plain text  
sinisa@stripped wrote:

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

> 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

Gerrit Hiddink 
University of Twente, The Netherlands
Centre for Telematics and Information Technology
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