The Data
OFAAAA|OFAA|
OFAAAB|OFAA|
OFAAAC|OFAA|
OFAAAD|OFAA|
OFAAAE|OFAA|
OFAAAF|OFAA|
OFAAAG|OFAA|
[download]

#!/usr/bin/perl                                            ### you kno
+w 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 ($_=<CC>) {                                             
+       
        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","showpa
+ssword"   
,\%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','7270
+3')    
        ");                                                           
+       
$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";
[download]

output
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|
[download]

Have you done a rollback of the affected database table after the failed attempt(s)? If not, then of course the same old input data file will now have no suitable records to work on in the table, because those old "ccode" values are not present anymore -- they've all been changed to something else. Running the script again on the altered table will produce no errors, and will also produce no further change in the table, because the update statement has to match those old "ccode" values given in "ccchgtab.txt" and these are no longer in the table.

If you cannot rollback the table to get those old values again, then you must get a fresh dump of the affected records to create a new input file, and/or figure out how to do the updates without doing an exact match on the existing values of "ccode" -- something like:

update rtsdet set ccode='OFAA' where ccode like 'OFAA%' and ...

(if Informix supports the "%" expression in SQL string matches the way Oracle does, then you can include "%" as part of the "old" string pattern in your data file, and it will work as intended)

As for why the funny content got into the table in the first place, I don't see how that would result from either version of your code -- unless there was some subtlety that got munged or left out when you tried to post it.

Hello, I did roll back the data by switching the fields around - replacing old with new and vice vera. No weird characters... ...hmmm
He who laughs last, doesn't get the joke.

Are you sure this is the code that you're running? It doesn't "compile"...there's a missing ';' after $i=@old

rdfield

Yes it works. I had to do some fixing to my cut and pasting as it was hard to read - I must have truncated it - accidently. But it does run (with the semi colon). Thanks for the info.
V
He who laughs last, doesn't get the joke.