By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Capital letters causing problems =[

P: 5
Hi guys just wandering if I could get a little help! , I have wrote a Perl script to sort out some data in my database, I am saving into an sql file but having a few problems executing the sql because of capital letters.

I have been given a list of 'From' and 'To' names, as there are multiple entries in my DB eg. Fred Bloggs, Bloggs Fred, F Bloggs are all the same person but I have organised the list like so.

Bloggs Fred, Fred Bloggs
F Bloggs, Fred Bloggs
Fred Bloggs, Fred Bloggs

So values on the left are 'From' and values on the right are 'To'.

heres my code:

Expand|Select|Wrap|Line Numbers
  1. #!perl.exe
  2. # script to CLEAN the AX4GANGS table
  3. # If values of FROM and TO are the same then ignore!
  4. # If values of FROM and TO are different INSERT 'To Names' to AX4GANGS table.
  5. # Delete values of FROM, from AX4GANGS table
  6. #
  7. # Jonny Carter
  8. #
  9. #
  10.  
  11. BEGIN
  12. {
  13.     ( $SCRIPTNAME=$0 )=~s/^.*\\//g;
  14.     $SCRIPTNAME=~s/^.*\\//g;
  15.  
  16.     #get the pm libfiles
  17.     push( @INC, "../pm" );
  18. }
  19.  
  20. use CGI;
  21. use DBI;
  22. use DBNAME;
  23. use LOG;
  24. use Time::Local;
  25.  
  26. $dbh=DBI->connect("dbi:mysql:host=$DB_host:dbname=$database",$DB_user,$DB_pass)
  27.     or die "could not conect to database $database";
  28.  
  29. %allaudited=( getsqllist( "select distinct AUDITED, '1' from AX4GANGS " ) );
  30. $n=keys(%allaudited);
  31.  
  32.  
  33. open FILE, "ax4audit.csv" or die  $!;
  34.  
  35. while( $line=<FILE> )
  36. {
  37.     chomp $line;
  38.   ($count,$company,$from,$to) = split( ',', $line );
  39.  
  40.   # AX4GANGS INSERTS and DELETES
  41.  
  42.  
  43.       ( $from eq $to ) && next;
  44.  
  45.     if ( ! $allaudited{$to} )
  46.     {
  47.         print "INSERT INTO AX4GANGS VALUES (NULL,'$company','$to',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'jonnyc',NULL);\n";
  48.         $allaudited{$to}="1";
  49.  
  50.     }
  51.     print "DELETE from AX4GANGS where AUDITED='$from';\n";
  52.  
  53.  
  54.   # AUDITS Table UPDATES
  55.  
  56.     print "UPDATE AUDITS set AUDITED ='$to' where AUDITED='$from';\n";
  57. }
  58.  
  59. close ( FILE );
  60. exit;
  61.  

Heres an example of what is happening:

I have a value of JONny Carter and a value of Jonny Carter.

for some reason when I then run the sql file I get both inserted into the table.

I hope I have explained this well enough ... Any help is really appreciated.
Nov 20 '08 #1
Share this Question
Share on Google+
1 Reply


P: 5
Ah I sorted it now !! but thanks any way!

It was my error, I put JONny into my .csv file in the 'To' column ...All is working fine now!
Nov 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.