Subject Re: [firebird-support] Insert blob in PERL
Author Jacqui Caren
Max Wolfring wrote:

> I need to insert an image into firebird using PERL.
> The application receive the image from browser, it's ok, I wan to know,
> if possible, how can I move the "image string" to the data base.

Using DBD::Interbase bind a value to a 'BLOB sub_type binary'

I.e. create table l_image (
image_id integer,
image_data BLOB sub_type binary

then in perl

$dbh->do('insert into l_image ( image_id, image_data) values (?,?)'
{}, $next_id, $image_data);

See perldoc CGI for an example of how to consume the image data
into a perl scalar - remeber to use binmode() when required.

If just getting it working is your only interest, stop reading :-)

However due to performance[0] and other reasons, images are often
stored in an external filesystem and a reference to the image
held in the database.

This means that you can store the image_id and image_path
in the database or even better store image_id and
use the id to determine image_path.

One other word of warning do NOT store all images in a single
directory - esp.y under unixen - dir access tends to slow
down a lot as diropen/read/... is linear and therefore *slow*
if you have more than 1000 then a tree is a good idea.

A common trick is to chop a numeric id into digit pairs

i.e. 1234 = /var/html/images/00/12/34/image.gif

sub image_path { return '/var/html/images/'
print image_path(1234),"\n";

A benchmark will tell you if accessing a varchar field
is quicker than the above code stub - for your application.

Another neat trick is to add image.gif (png and jpg)
as DirectoryIndex file names. This allows you to
be able to (internally) redirect to /var/html/images/12/34/
and Apache does the rest :-)

In the real world everything is a trade off. With a bit
of experience you can sit down and crunch numbers to get
a measure of which of the many options is a best long term


[0] Even under mod-perl the difference of mod_perl vs direct file
access is ~20 to 300ms against 2 to 5ms for direct file access.
If you then add the costs of reading a blob field from the
database and you get something like a 300 vs 3 ms (i.e. 100 to 1
or worse performance difference.