Storing files in a BLOB field via SQL

[Irmen de Jong]

Juergen Gerner wrote:

>>Is there a special reason why you can't store the whole file in a
>>single BLOB? That's what it's a BLOB for, after all... L=Large :-)
> 
> 
> Yes, there's a special reason. After reading a lot of documentation I
> think it's better splitting large files in little blobs. It doesn't
> matter if the SQL server is on the same machine as the application,
> but if both parts are on different machines, large files have to be
> transmitted over the network. During this transfer the application
> isn't responding, I guess. So splitting would be much more flexible.

How would splitting the file in chunks improve the responsiveness
of the application? This would only work if your app needs only
a specific chunk of the larger file to work on. If you need to read
the full file, reading 10 chunks will take even longer than reading
one big BLOB.
You may decide to do it 'in the background' using a thread, but
then again, you could just as well load the single big BLOB inside
that separate thread.

> Additionally I think, splitting files makes the database more scalable
> and the space on the harddrive better used.

In my humble opinion these kind of assumptions are generally false.
Let the database decide what the most efficient storage method is
for your 100 Mb BLOB. I don't want to make these kind of assumptions
about the inner workings of my database server, and I certainly don't
want to wire them into my application code... what happens when you
switch platforms/DBMS? Is your code still 'the most efficient' then?
Just my €0.02

> But the splitting isn't my main problem. It's the way I transmit the
> binary data to the database via an SQL syntax.

Sorry can't help you with this. I would expect the database driver module
to do the 'right' escaping.


--Irmen