On Thu, 30 Oct 2008 09:06:52 -0400, John Scoles <[EMAIL PROTECTED]>
wrote:
> Ok looks like you need to use bind_param with a Blob and also tell
> DBD::Oracle is is a Blob
>
> ie
>
> $stu->bind_param(":p1","foo\nbar",{ ora_type => ORA_BLOB });
This is a disaster to all portability!
My scripts *ALL* are portable across all databases, and ORA_BLOB is
only exported by DBD::Oracle, so in portable scripts I need to hardcode
those constants. Not nice.
My final working code now looks like:
--8<--- (real life code)
{ my ($c_aant, $aant, %attr);
DB_Type () eq "O" and %attr = ( ora_type => 113 ); # ORA_BLOB
my $sti = prepar ("insert into aant values (?, 1, ' ', ?)");
my $stu = prepar ("update aant set w_aant = ? where c_aant = ? and v_aant =
1");
foreach $c_aant (sort { $a <=> $b } keys %aant) {
my @aant = @{$aant{$c_aant}}; shift @aant; # value 0 not used
$aant = join "\n", map { $_ // "" } @aant;
$aant =~ s/[\s\n]+\Z/\n/;
$aant =~ s/\A[\s\n]+//;
if (defined $aant[0]) {
$stu->bind_param (1, $aant, { %attr });
$stu->bind_param (2, $c_aant);
$stu->execute;
}
else {
$sti->bind_param (1, $c_aant);
$sti->bind_param (2, $aant, { %attr });
$sti->execute;
}
}
$dbh->commit;
}
-->8---
which will be a hell to maintain!
Compare that to the original:
--8<---
{ my $sti = prepar ("insert into aant values (?, 1, ' ', $convert)");
my $stu = prepar ("update aant set w_aant = $convert where c_aant = ? and
v_aant = 1");
foreach my $c_aant (sort { $a <=> $b } keys %aant) {
my @aant = @{$aant{$c_aant}}; shift @aant; # value 0 not used
my $aant = join "\n", map { $_ // "" } @aant;
$aant =~ s/[\s\n]+\Z/\n/;
$aant =~ s/\A[\s\n]+//;
if (defined $aant[0]) {
$stu->update ($aant, $c_aant);
}
else {
$sti->insert ($c_aant, $aant);
}
}
$dbh->commit;
}
-->8---
notes
1. prepar () is just a wrapper around $dbh->prepare ()
2. ->update () and ->insert () are wrappers around ->execute () that
change all '' to ' ' for varchar fields, so I don't get those dreaded
NULL values in the database
--
H.Merijn Brand Amsterdam Perl Mongers http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, SuSE 10.1, 10.2, and 10.3, AIX 5.2, and Cygwin.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
