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.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
