John Scoles wrote:
>
>
>> Date: Wed, 30 Jun 2010 10:24:24 +0100
>> From: martin.ev...@easysoft.com
>> To: lvir...@gmail.com
>> CC: dbi-users@perl.org
>> Subject: Re: What is the technique to supply a list of values to a
> where clause?
>>
>> Larry W. Virden wrote:
>> > I am working on a quick tool to update some records in an oracle
>> > database.
>> >
>> > The code is similar to some other tools I've cobbled together, but in
>> > this case, the where clause has a "column in list" clause. The
>> > relevant pieces of code are:
>> >
>> > my $aenlist = "(1127, 1557, 1661, 3030, 3982, 5205, 453, 8838,
>> > 9338)";
>> > my $tmpsuper1 = 406;
>> >
>> >
>> > $updateSuper = $oraProdDBH->prepare
>> > (
>> > "UPDATE HR_INFO SET " .
>> > "SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE " .
>> > "WHERE " .
>> > "ALT_EMP_NO IN ? AND SUPERVISOR_EMP_NO = 0"
>> > ) or die "Failed to prepare - $DBI::errstr\n";
>> > $urc = $updateSuper->execute($tmpsuper1, $aenlist) or die "Failed
>> > to execute - $DBI::errstr\n";
>> >
>> > When I try to run the code, DBI returns an error saying that the value
>> > supplied by $aenlist isn't a number. And that's true - it is a string.
>> >
>> > So, I tried setting $aenlist to (1127, 1557, 1661, 3030, 3982, 5205,
>> > 453, 8838, 9338); and perl tells me there's a useless constant. That's
>> > when I thought that what I really need to do was use @aenlist. Alas,
>> > when I changed the execute to say
>> > my @aenlist = (1127, 1557, 1661, 3030, 3982, 5205, 453, 8838, 9338);
>> > :
>> > :
>> > my $urc = $updateSuper->execute($tmpsuper1, @aenlist) or die "Failed
>> > to execute - $DBI::errstr\n"
>> >
>> > ; now I get a DBD::Oracle::st execute failed error that I called
>> > execute with 10 variables when 2 were needed.
>> >
>> > So, I am hoping someone might be able to point me to some information
>> > about how to encode the execute so that I pass a list to the where
>> > clause.
>> >
>> > Thank you so much for your help.
>> >
>> >
>>
>> I'm presuming you have one SQL statement to do updates and a list of
>> values for your where clause. You cannot do what you've tried above
>> because DBI sees 2 parameters in your SQL not the 10 you've passed. You
>> need to do:
>>
>> 1. Change your SQL to construct a where clause using "in"
>>
>> e.g.,
>> $sql = q/update HR_INFO SET SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE
>> WHERE SUPERVISOR_EMP_NO = 0 AND ALT_EMP_NO IN (/ . join(',', @aenlist) .
>> ')';
>>
>> Personally I don't like this and some database engines have a limit on
>> how many items they can handle in the "in" clause.
>>
>> 2.
>>
>> prepare(q/update HR_INFO SET SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE
>> WHERE ALT_EMP_NO IN ? AND SUPERVISOR_EMP_NO = 0/);
>>
>> foreach (@aenlist) {
>> execute($tmpsuper1, $_);
>> }
>>
>> 3. use execute_array - see the DBI docs. This would probably be my
>> preferred method but it is a little more complicated to use than 2 above
>> especially if you need to know exactly which updates had an effect.
>>
>
> I would have to disagree with Martin on this it :). The Execute_array
> is very easy to use
>
> you have
>
> my @aenlist = "(1127, 1557, 1661, 3030, 3982, 5205, 453, 8838, 9338)";
> my $tmpsuper1 = (406,406,406,406,406,406,406,406,406);
>
>
> $sth = $dbh->prepare("UPDATE HR_INFO
> SET SUPERVISOR_EMP_NO=?,
> REC_MOD_DATE=SYSDATE "
> WHERE ALT_EMP_NO = ? AND
> SUPERVISOR_EMP_NO = 0");
>
> $sth->
> my $tuples = $sth->execute_array(
> { ArrayTupleStatus => \my @tuple_status },
> \...@tmpsuper1 ,
> \...@aenlist ,
> );
>
> which is not that hard and it is also standard DBI so it should work on
> every version of DBI/DBD. As a bonus some DBD will use thier own
> processing for the command. For examples DBD::Oracle has a native array
> execute that will save you alot of processing cycles if you have large
> (1000+) updates to do.
John, you will note I did say "This would probably be my preferred
method" for execute_array.
However by "a little more complicated to use" I was referring to the
point that if a single insert/update fails you need to check each
element in the ArrayTupleStatus and in some cases it does not help
anyway as some DBDs do not follow the DBI spec. Here is an example
illustrating the trouble with some execute_array implementations (this
one is DBD::Oracle):
use DBI;
use strict;
use Data::Dumper;
my $h = DBI->connect('dbi:Oracle:host=xxx.easysoft.local;sid=xxx',
'xxx', 'xxx', {PrintError => 0});
eval {
local $h->{RaiseError} = 0;
$h->do(q/drop table mje/);
};
$h->do(<<'EOT');
create table mje (a integer unique)
EOT
my $s = $h->prepare(q/insert into mje values(?)/);
my ($executed, $affected) =
$s->execute_array({ArrayTupleStatus => \my @sts}, [1,2,3,4,5]);
print "executed: $executed, affected: $affected\n";
# shows executed: 5, affected: 5
# (but see ArrayTupleStatus below which shows unknowns)
print Dumper(\...@sts);
# shows 5 * -1 (driver does not indicate affected rows)
my $fetched = $h->selectall_arrayref(q/select * from mje/);
print Data::Dumper->Dump([$fetched], [qw(fetched)]);
# shows 5 rows 1..5 so 5 rows correctly inserted as execute_array
# returned but ArrayTupleStatus indicates it cannot tell us
# anything about each row.
# the equivalent done without execute_array
foreach (6,7,8,9,10) {
$affected = $s->execute($_);
print "affected: $affected\n";
}
# shows 5 * 1 i.e, driver now knows 5 inserts occurred
# now deliberately fail by violating the pk
($executed, $affected) =
$s->execute_array({ArrayTupleStatus => \...@sts}, [11,12,1,13,14]);
print "executed: $executed, affected: $affected\n";
# shows executed: , affected:
# so something failed
# which one failed - we need to look in ArrayTupleStatus:
print Dumper(\...@sts);
# shows -1, -1, [1, 'ORA-00001: unique constraint (BET.SYS_C0070784)
# violated (DBD SUCCESS_WITH_INFO)'],-1,-1
# so we know the 3rd one failed BUT
# we don't know from the ArrayTupleStatus returned from execute_array
# how many succeeded as they all show -1 and one shows an error
# - did the other 4 get inserted?
#
# From the DBI docs:
# "Some databases may not continue executing tuples beyond the first
# failure. In this case the status array will either hold fewer
# elements, or the elements beyond the failure will be undef."
#
# so what do we have in the table now:
$fetched = $h->selectall_arrayref(q/select * from mje/);
print Data::Dumper->Dump([$fetched], [qw(fetched)]);
# shows 10 rows 1..10 so none of this last set got inserted but there is
# nothing in the ArrayTupleStatus to indicate all of them failed.
# now repeat with execute
foreach (11,12,1,13,14) {
$affected = $s->execute($_);
print "affected: $affected\n";
}
# shows 1 1 undef 1 1 so we know the 3rd failed but did the other
# 4 succeed - yes they did
$fetched = $h->selectall_arrayref(q/select * from mje/);
print Data::Dumper->Dump([$fetched], [qw(fetched)]);
# shows 1..14
I might argue this is a bug in DBD::Oracle but I think we've covered
this ground before and I think the answer was that Oracle cannot tell
you the execute status of each independent row in a batch. For that
reason I stated "preferred method" but "a little more complicated to use".
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
