> 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.
Cheers
John Scoles
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
_________________________________________________________________
Look 'em in the eye: FREE Messenger video chat
http://go.microsoft.com/?linkid=9734386
