hi,
This script works fine with dataype of bytea type but
fails for lo ,can u help in this
Regards
omkar
Originally posted by hachiman
hi all there:
I was working a little around this subject and want to share my experiences with blobs and postgresql. This explanation is for PostgreSQL + Perl + DBI + DBD::Pg under Linux.
The version i'm using are:
postgres: 7.1.3
perl 5.6.0
DBI and DBD::Pg 1.30
Linux Red Hat 7.1 kernel 2.4.9
First, postgresql use a special data type to store blob named BYTEA
then, for the experiment, create the following table:
create table images (
id integer not null, -- or what ever you want
picture bytea
);
First step: inserting an image into the table: Create a test script,
named as you like:
#!/usr/bin/perl
use DBI qw/:sql_types/;
$dbh = DBI->connect("DBI:Pg:dbname=mybase");
DBI-> trace(3,'/tmp/dbierr'); ## use to trace all db activity
### copy an image in the same directory you use to create the script
## of course you can put it in every place you like, just point to it
###
my $imagen = "hotNtot-436d.gif";
### reading the file
##
open A, "<$imagen";
$img = "";
while(read(A, $data, 1024)) {
$img .= $data;
}
close A;
### now, inserting the image in the table:
#
## determining the max size we allow to store
##
$dbh->{LongReadLen} = 512 * 1024; ## 512 KB
$dbh->{LongTruncOk} = 1;
my $ID = 1; ### just for test
my $salvaimg = "INSERT INTO images VALUES ($ID, ?)";
my $sth = $dbh->prepare($salvaimg);
### this is the key to insert the blob
## here we are binding the blob, note that SQL_BINARY is
## defined in DBI qw/:sql_types/;
## and "$img" is the size in bytes of the image
## we got in the read loop:
$sth->bind_param(1, "$img", SQL_BINARY) || die $dbh->errstr;
$sth->execute($img); ## that it's !!!!
$dbh -> disconnect();
1;
Now, take a look in the /tmp/dbierr file and see very interesting stuff
Second Step: fetching the image.
In this step, i create a directory visible in the web, and grant write
permission to the web server user, in my case "nobody":
$ mkdir /var/www/html/images
$ chmod 775 /var/www/html/images
$ chgrp nobody /var/www/html/images
Now, the second script:
#!/usr/bin/perl
use DBI qw/:sql_types/;
$dbh = DBI->connect("DBI:Pg:dbname=mybase");
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
### use again to trace all db activity and for this you may erase
## the previous content of the /tmp/dbierr file
DBI-> trace(3,'/tmp/dbierr');
$sth = $dbh -> prepare("SELECT picture from images");
$sth -> execute();
$SALIDA = $sth -> fetchrow_array;
### recontructing the image
##
$IMA = "/var/www/html/images/recovered_image.gif";
open A, ">$IMA";
print A $SALIDA;
close A;
$dbh -> disconnect();
1;
Finally, open your browse and point to:
http://yourserver/images/recoverd_image.gif
With a litte patient, you can with easy transport this scripts into
the language you use and of course, apply them in more complex
situations.
I hoppe you find this helpfull.
hachiman