This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
<dbi-users@isc.org>
From: "Andrey Bronfin" <bronfin@VisualTop.com>
To: <dbi-users@isc.org>, <perl-win32-database@listserv1.ActiveState.com>
Sent: August 13, 2000 8:15:19 PM GMT
Subject: BLOBs & DBD::Oracle
Would U please share any code snippets to insert pictures
into a BLOB column of an Oracle table using Perl DBI &
DBD::ORACLE ? The perl program is running on a machine
different than the DB server . I wouldn't like to use
SQL*Loader , since the BLOB insert is a part of a long
transaction and i want to be able to rollback or to commit
at the end of the transaction .
===
From: "Ilya Sterin" <ideas_pc@usa.com>
Sent: Monday, August 14, 2000 4:32 AM
Subject: RE: BLOBs & DBD::Oracle
Just follow the regular prepare and execute procedure, but use
placeholders for the blob column. Everything else is the same. When
selecting the blob set the LongReadLen.
See the docs for using placeholders.
Are you having any existing problems?
Also make sure to use binmode function, depending on your platform. See
docs.
If perl is compiled with OCI8 then I don't believe you have to
use blob_read if not see README.longs in the DBD::Oracle package.
===
From: "Andrey Bronfin" <bronfin@VisualTop.com>
Sent: August 14, 2000 8:43:49 AM GMT
Subject: Re: BLOBs & DBD::Oracle
Thanks , Ilya , it's wonderful.
Do U have an EXAMPLE or a code snippet to INSERT pictures
into a BLOB column ?
===
Subject: Re: BLOBs & DBD::Oracle
From: Ilya Sterin <ideas_pc@usa.com>
Date: Mon, 14 Aug 2000 10:24:57 -0400 (EDT)
usr/local/bin/perl5.005
$| = 1;
binmode STDIN;
binmode STDOUT;
use DBI;
use CGI;
$query = new CGI;
$fh = $query->param('file');
($one, $two, $three, $four, $five) = split(/\\/, $fh);
($file_name, $extension) = split(/\./,$five);
my $picture;
my $buffer = "";
while ( read ($fh, $buffer, 300000)) {
$picture .= $buffer;
}
my $dbh = DBI->connect(dbi:Oracle:dsn, "user", "pass", {LongReadLen =>
65535, AutoCommit => 1, RaiseError => 1, PrintError => 1}) or die "could not
connect: DBI::errstr";
my $sth = $dbh->prepare("insert into fame.images (image_name, image1_code,
image1_ext, image_name_prefix) values (?, ?, ?, ?)") or die "Could not
prepare: DBI::errstr";
$sth->bind_param(1, $file_name);
$sth->bind_param(2, $picture, { ora_type => 113 });
#use of ora_type is good practice in this case but is not
#required. See Oracle.xs file for ora_types in the DBD::Oracle
#package.
$sth->bind_param(3, $extension);
$sth->bind_param(4, 'A');
$rows = $sth->execute();
$sth->finish();
$dbh->disconnect();
print $query->header(-type => 'text/html');
if ($rows > 0) {
print "Update successful";
} else {
print "Update unsuccessful";
}
1;
This is fully functional on my system. If you have problems you might want
to use trace() to debug.
===
Subject: Re: Oracle LOBs do not AutoCommit
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Sat, 9 Sep 2000 15:31:15 +0100
On Fri, Sep 08, 2000 at 06:31:59PM -0600, John Williams wrote:
> Attached is a program which demonstrates an apparent bug in DBD::Oracle.
>
> Inserts and Updates of BLOB/CLOB columns do not get committed when
> {AutoCommit} is on. Instead the row remains locked, and any other session
> attempting to update the row hangs indefinitely.
Known bug. Read the archives. Sorry, no time to help further.
===
Subject: DBD::Oracle, select CLOB data problem
From: "Lund, Kevin" <Kevin.Lund@westgroup.com>
Date: Thu, 14 Sep 2000 15:38:48 -0500
All,
I'm having a problem selecting CLOB data from an Oracle database. I searched
the archives and found people having this problem, but couldn't find any
solutions. So, here goes.
I'm trying to select data from a table that has five columns - two number
fields, two varchar fields, and the CLOB field. I'm using DBD::Oracle 1.03,
I have ActiveState Perl build 522, I have Oracle 8.0.6, and I'm on an NT
machine.
Here's my code:
### Start Code ###
use strict;
use DBI;
use DBD::Oracle;
my $dbh = DBI->connect( "orad09", "wld_su", "wld_su", 'Oracle' )
|| die $DBI::errstr;
my $sth = $dbh->prepare(
"SELECT ANSWER_TEXT FROM wld.faq where FAQ_SEQ_NUM = 300" );
$sth->execute || die $sth->errstr;
$sth->finish;
$dbh->disconnect;
### End Code ###
The die is called at the $sth->execute statement, with this message:
ORA-00932: inconsistent datatypes (DBD: oexfet error) at test_oracle.pl
line 9. Database handle destroyed without explicit disconnect.
Selecting data from other columns works fine. I've played with LongTruncOK
and LongReadLen, but nothing made a difference.
Am I missing something obvious here? Any thoughts?
===
Subject: RE: DBD::Oracle, select CLOB data problem
From: Ilya Sterin <ideas_pc@usa.com>
Date: Thu, 14 Sep 2000 23:51:45 -0400 (EDT)
I would recommend upgrading your Oracle build and DBI and DBD::Oracle.
LongReadLen should be set to the length of selected data, but wasn't
implemented until the later driver build not sure of the version. There is
a way around it. blob_read or later renamed readblob will fetch in chunks.
Download DBD::Oracle from CPAN and in the package there is a file called
README.longs that gives examples of how to do it.
$dbh->{RaiseError} = 1;
$dbh->{LongTruncOk} = 1;
$sth = $dbh->prepare("SELECT key, long_field FROM table_name");
$sth->execute;
while ( ($key) = $sth->fetchrow_array) {
my $offset = 0;
my $lump = 4096;
my @frags;
while (1) {
my $frag = $sth->blob_read(1, $offset, $lump);
last unless defined $frag;
my $len = length $frag;
last unless $len;
push @frags, $frag;
$offset += $len;
}
my $blob = join "", @frags;
print "$key: $blob\n";
}
===