Re: inserting BLOB data

Kiran,

May I recommend that you not store blobs such as files in the database? 
The database will handle it, but it you would get much better 
performance by having the files in a directory structure and a fast 
lookup table to store the file's location on disk. Also, it'll make 
backups much faster as well as use less memory - when you retrieve/store 
a blob, it requires that much memory in the MySQL server.

Alas, If you absolutely must put the file in the database, you'll want 
to at least have two tables, both 1:1. The first table will be a fast 
lookup table with all columns except the blob, including "file_id". The 
second table will contain the blob and "file_id" as the first table. The 
idea being that the first table is for lookups, second for retrieving 
the data if you need it.

You want to avoid performing queries on a table will blobs as much as 
possible.

As far as storing the blob (if you must ;) just store it as you would 
any other data. Gone are the days when you had to pre-calculate the size 
of the blob prior to insert.

--Patrick

Kiran Annaiah wrote:

>I am trying to store my file contents in a table. I have never worked with BLOB data types before.
>I realised that inserting the file contents into the BLOB column is not the same as inserting into a regular column.
>
>The file size is about 2.2M. I have set the system variable max_allowed_packet to 3M.
>
>Can anyone please give me some ideas about how the file contents can be inserted?
>
>Table structure is as follows.
>
>+-----------+-----------------------+------+-----+---------+----------------+
>| Field     | Type                  | Null | Key | Default | Extra          |
>+-----------+-----------------------+------+-----+---------+----------------+
>| file_id   | smallint(5) unsigned  | NO   | PRI | NULL    | auto_increment |
>| file_name | varchar(25)           | NO   |     |         |                |
>| file_size | mediumint(8) unsigned | NO   |     |         |                |
>| file      | mediumblob            | NO   |     |         |                |
>+-----------+-----------------------+------+-----+---------+----------------+
>
>
>_________________________________________________________________
>i’m is proud to present Cause Effect, a series about real people making a difference.
>http://im.live.com/Messenger/IM/MTV/?source=text_Cause_Effect
>  
>


-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]