Hi.
----- Original Message -----
From: "ZHU Jia" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 07, 2002 6:32 PM
Subject: How to read/write BLOB in the Postgres DB from Perl DBI?
> Hello,
>
> I'm porting a DBI-Mysql application to Postgres, but I've big problems
> with the BLOBs.
> I have beening searching the internet for a while now, but unfortunately
> couldn't find much information about how to read/write BLOB data from
> Perl DBI in the Postgres DB.
> Is there anyone who has dealt with this problem? If yes could you please
> show me how to do this? (A little example would be great)
If your PostgreSQL has BYTEA type (maybe 7.1.3 or later),
you can use it.
If you say about large object of PostgreSQL, you should read
about "func" method of DBD::Pg. (with perldoc DBD::Pg)
[Ex. Using BYTEA]
CREATE TABLE ALBUM(
TITLE VARCHAR(80),
CREDT DATE,
PICIMG BYTEA,
PICTYP VARCHAR(80),
PRIMARY KEY (TITLE)
);
------
sub insData($$$) {
my ($sTitle, $sDate, $sPict) = @_;
my $hDb = DBI->connect(@aConn,
{RaiseError=>1, AutoCommit=>0}) or
die "CONNECT ERROR $DBI::errstr";
eval {
my $hSt = $hDb->prepare(
q/INSERT INTO ALBUM (TITLE, CREDT, PICIMG)
VALUES (?, ?, ?)/);
$hSt->bind_param(3, undef, DBI::SQL_BINARY);
$hSt->execute($sTitle, $sDate, $sPict);
};
my $sRes;
if($@) {
$hDb->rollback;
$sRes = "NG: $@";
}
else {
$hDb->commit;
$sRes = 'OK:';
}
$hDb->disconnect();
return $sRes;
}
---------------
sub getImg($) {
my ($sParam) = @_;
my $hDb = DBI->connect(@aConn,
{RaiseError=>1, AutoCommit=>0}) or
die "CONNECT ERROR $DBI::errstr";
$hDb->{LongReadLen}=2_000_000;
$hDb->{LongTruncOk}=1;
my $hSt = $hDb->prepare(
q/SELECT PICIMG FROM ALBUM WHERE TITLE = ?/ );
$hSt->execute($sParam);
my ($sRes) = $hSt->fetchrow_array();
$hSt->finish();
$hDb->disconnect();
return $sRes;
}
[Ex. lo_creat, lo_write]
use strict;
use DBI;
my $hDb = DBI->connect('dbi:Pg:host=lins;dbname=test',
'scott', 'tiger', {RaiseError=>1, AutoCommit=>0}) or
die "CONNECT ERROR $DBI::errstr";
$hDb->commit;
my $oId = $hDb->func($hDb->{pg_INV_WRITE}, 'lo_creat');
my $oFd = $hDb->func($oId, $hDb->{pg_INV_WRITE}, 'lo_open');
my $hSt = $hDb->prepare('INSERT INTO STIMG VALUES (?);');
open(IN, '<test.png');
my($sBuff, $iLen);
while($iLen = read(IN, $sBuff, 2048)){
$hDb->func($oFd, $sBuff, $iLen, 'lo_write');
}
close(IN);
$hDb->func($oFd, 'lo_close');
$hSt->execute($oId);
$hDb->commit;
$hDb->disconnect;
==============================================
Kawai, Takanori(Hippo2000)
Mail: [EMAIL PROTECTED] [EMAIL PROTECTED]
http://member.nifty.ne.jp/hippo2000
==============================================
