RE: What is the technique to supply a list of values to a where clause?

 

> Date: Wed, 30 Jun 2010 10:24:24 +0100
> From: [email protected]
> To: [email protected]
> CC: [email protected]
> 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