BLOBs and the Web

Manuel,

This isn't really a Metabase question, but I'm hoping that with your experience you can give me some direction.

I've used Metabase to put a BLOB into a MySQL database and to retrieve it. Worked like a charm! Thanks for an easy-to-use tool.

However I wonder if putting my church photos in a database is worthwhile. Since I apparently have to retrieve a BLOB to a disk file before doing anything with it, why not leave it on the hard disk and just store its file name? Or is there some way to go directly from the hard disk to a Web page without first writing the photo to a disk?

Thanks for any insight.

Harvey

There are reasons in favour or against putting files in database BLOBs fields. Those are the same for using SQL/relational databases versus using flat file databases.

Storing and retrieving BLOBs is not a fast operation, so it may slow down your application, unless you use some sort of cache that avoids retrieving the BLOB fields repeatedly.

The same can be said about generating pages with content retrieved from the database using many or slow queries. To avoid the overhead of generating pages that require a slow database access, you can used a caching technique.

The PHPClasses uses a generic file cache class for that purpose. The benefits have been tremendous in terms of performance. most of the accesses to pages with database driven content no longer requires any database accesses until the content is changed.

phpclasses.org/filecache

Keeping content on disk, may solve the problem for single server Web sites. For sites served by clusters that becomes problematic.

The solution that guarantees data consistency is to concentrate the caching in a single server and all clustered servers retrieve the cached content from there.

That would be slower but it would preserve scalability. To reduce the networked content access overhead for busy sites, you may also consider local server caching for short periods (say a few seconds or minutes).

Other than that, storing BLOBs in a database can be assured to be consistent using transactions for updates that require more than one query.

Storing BLOBs in a database also simplifies the backup of your application data. A single procedure can backup and restore all your database tables, including those with BLOB fields.

Other than that, keep in mind that not all databases are suited for storing and retrieving large BLOBs. The problem is that they require to load the whole BLOB data in memory when you are storing or retrieving it from a database.

Metabase provides an API with prepared queries that lets you stream data that is being stored or retrieved from a BLOB field. However, the internal implementation of some drivers requires loading the whole BLOB data in strings to execute INSERT, UPDATE and SELECT queries.

This means that if you accessing BLOBs with more than a few megabytes, it may exceed the default PHP memory limit which is 8MB.

Only the databases that support native prepared queries that stream BLOB data instead of sending whole BLOBs at once.

Metabase drivers for Oracle and PostgreSQL for instance, support native prepared queries, thus streaming BLOB data.

The Metabase MySQL driver that uses the old MySQL API do not support native prepared queries.

I have not yet used the MySQLi API that supports native prepared queries. So, I am not sure if it provides all the necessary means to stream BLOB data.

Dear sir.

i have a problem that i have database stored on my internet server in mysql.

I am trying to copy that database to another site. I have control over both the sites.

i am getting problem to copy data between diff servers. i.e. i want to copy data from one server and transfer it to another server.

when i copy it gets possible for general character fields but it is not getting physible for BLOB fields.

please help to solve out this problem

Warm Regards

Tarun Agarwal

You should not copy the database files directly. Use mysqldump to do it safely and without data loss.

Dear Sir,

I tried to write the scipt for mysqldump but i am not able to get the files from that.

I wrote a script regarding the databack as suggested in mysql manual.

Sir can u please help me in what script i should write to get database backup.


Kind Regards

Tarun

You do not need to write any script. mysqldump is a program that comes with MySQL . It is ready to generate a backup file of any database.

The backup file is just a list of SQL statements. You just need to call the mysql client program passing the backup file as input and it will restore the database in the new server.

If you cannot use mysqldump, you can try several classes from here that can perform exactly the same operation using only PHP code:

phpclasses.org/browse/class/24.html

Hello Sir,

This all scripts are working for on our computer. but are not workable on the internet server.

as they require mysqldump.exe file location which cannot be located :)

Sir if any more idea. please help me out.

Warm Regards

Tarun

Please read again my previous replies as I have explained that there are solutions that only require PHP but not the mysqldump program.