##
#!/usr/bin/perl ### you know you can probably do this with temp tables in SQL....VVP
### Description: change old ccodes with new ones provided
### Will affect rtsdetail table - ccode field
### WILL FIND ALL REQS THEN MAKE CHANGES INSTEAD OF ALL IN ONE LOOP.
### 2002 03 20
use DBI; # Load the DBI module
use CGI; # HTML
use strict; # keep it usable
use CGI qw(:standard);
use CGI::Carp qw( fatalsToBrowser );
my ($sth);
my ($ccode,@ccode);
my(@data,$data);
my ($p_str,@p_str);
my $i=0; # counter
my ($CC,@CC);
my ($po,@po);
my ($old,$new);
my (@old,@new);
my $count;
my $total;
my ($head_date,@head_date);
open (CC,"ccchgtab.txt");
while ($_=) {
push (@CC,$_);
($old,$new)=split(/\|/,$_);
push @old,$old;
push @new,$new;
}
close (CC);
$i=@old
### Attributes to pass to DBI->connect() to disable automatic
### error checking
my %attr = (
PrintError => 1,
RaiseError => 1,
);
### Perform the connection using the Informix driver
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_dev","donot","showpassword"
,\%attr )or die "Can't connect to database: ", $DBI::errstr, "\n";
$sth=$dbh->prepare("SELECT req_id, ccode
FROM rtsdet
WHERE req_id IN ('48111','15313','45813','15226','48426','72703')
");
$sth->execute();
#print "old cc: $old \n";
# will dump out all fields $data=$sth->dump_results();
#$data=$sth->dump_results();
while ( ($po, $ccode) = $sth->fetchrow_array() ){
#print " $po $ccode, $head_date \n";
push (@po,$po);
push (@ccode,$ccode);
push (@head_date,$head_date);
}
$sth->finish;
$sth=$dbh->prepare("UPDATE rtsdet SET ccode=? WHERE req_id=? AND ccode=?");
foreach $po(@po) {
for ($count=0; $count<$i;$count++) {
$sth->execute($new[$count],$po,$old[$count]);
}
}
$sth->finish;
$p_str=@po;
print "# of POs: $p_str \n";
## ##
output
OFAA\
|
OFAA\
|
OFAA\
|
OFAA\
|
OFAA\
|