vivekvp has asked for the wisdom of the Perl Monks concerning the following question:

Hmmm... I did something bad... I loaded a "|" (pipe) delimted file then did an SQL update using the data from my delimted file. Problem is - it inserted escape characters on each update! This messes up the database. But I am not sure where these escape character come from.
Basically the program switches 1 fields data to the one beside it. Here is part of the file: 
ccchg.txt

TOADDE|EQANNB|
TOADDF|EQANNB|
CAABBB|FUAAAF|
OFAAAA|OFAA|  
OFAAAB|OFAA|  
OFAAAC|OFAA|  
OFAAAD|OFAA|

[download]
Here is the code: 
open (CC,"ccchg.txt");
        while ($_=<CC>) {
        push (@CC,$_);
        ($old,$new)=split(/\|/,$_);
        push @old,$old;
        push @new,$new;
        }
close (CC);
$i=@old ## get number of codes
### Attributes to pass to DBI->connect() to disable automatic
### error checking
my %attr = (
    PrintError => 0,
    RaiseError => 0,
);
### Perform the connection using the Informix driver
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_dev",
                        "user","password",\%attr )
  or die "Can't connect to database: ", $DBI::errstr, "\n";

$sth=$dbh->prepare("SELECT req_id, ccode
        FROM rtsdet a
        WHERE req_id IN ('48111','15313','45813','15226','48426','7270
+3')
        ");                                                 
$sth->execute();
while ( ($po, $ccode)  = $sth->fetchrow_array() ){
        push (@po,$po);
}
$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;
<code>
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|

[download]
Notice the \ and |. Any idea what is causing these?
apologizes for the bad formatting - trying to delete the spaces deletes all my code when I try - I have to rewrite it all again

Thanks, V
He who laughs last, doesn't get the joke.

2002-03-26 Edit by Corion: Fixed long wrapping line, replaced login information

Replies are listed 'Best First'.
Re: VVP:DBI and weird chars
by Zaxo (Archbishop) on Mar 26, 2002 at 20:18 UTC

    Your database will be more forthcoming if you set $attr{RaiseError=>1}.

    It's not clear to me what you are trying to do. You say you want to replace field values according to the pairs in your flat file. There seems to be a lot of other things going on. You appear to be reading ccodes from 'a', but then updating them in 'rtsdet'.

    A few pointss:

    1. What are the magic numbers in your SELECT?
    2. Do you plan to change your password now?
    3. Please improve your source code format. You can paste it from your editor if the browser textarea is contrary.

    I have no idea where your escape-newline-pipe sequence is coming from, but I suspect it would help to chomp as you read the file. (Though I don't see how the pipe survives the split.). Some well-placed print statements should resolve that.

    Update: Also PrintError=>1 would help. I should have recommended use strict; use warnings; while I was at it. Your fragment has variables popping up all over, and those will help untangle them.

    After Compline,
    Zaxo

[reply]
      Thanks for the tips. The password changes every week - and is only local or our building (9 users). I tried using a tab (^I) and it gave me the same errors. But let me try the RaiseErrror... Again - thanks! V XML Fundamentals
      He who laughs last, doesn't get the joke.
[reply]
      Hello again, 1. The numbers in the selected (REQ_ID) are unique records - I chose 6 that exists in the database. The are the database records that have the old code that is being replaced with the new code. 
      The Data
OFAAAA|OFAA|
OFAAAB|OFAA|
OFAAAC|OFAA|
OFAAAD|OFAA|
OFAAAE|OFAA|
OFAAAF|OFAA|
OFAAAG|OFAA|

      [download]

      The code (hopefully making more sense) 
      #!/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]
      Again - the problem is the changed codes do the switch - but leave a \ after it 
      output
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|

      [download]
      So the code does switch OFAAAA to OFAA - but leave a trailing /. I tried using tabs - same problem . Any ideas why? I hope I have been a bit more clear. Thanks for your time!
      V
      He who laughs last, doesn't get the joke.
[reply]
[d/l]
[select]
        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.

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

        rdfield

[reply]
[d/l]

Back to Seekers of Perl Wisdom