inserting new date in datatbase when different from system directory

From: A L (perlcgi828_at_yahoo.com)
Date: 10/22/03 

Date: Wed, 22 Oct 2003 13:19:09 -0700 (PDT)
To: dbi-users@perl.org

Hi,
I'm trying to compare files from a system directory to that of a database. There are folders under subdirectories under Top diretory. Names of folder is idnum in the database. Following code checks if there is an idnum that is same as the folder name. If it is same, it checks the date to see whether or not it is the same. But, before doing that, it has to convert the date of system directory to that of database date format. If the date is different, it replaces with the new one from the systems directory. Otherwise, it doesn't do anything. Then, it goes on to the next folder until all the folders in the system directory has been checked. I have written the following; using perl on the command line, no bugs or errors are reported, but it's not doing anything in the database. Can you give me a clue as to what is wrong with it? Thanks.
Angela
 
#!/usr/bin/perl
use DBI;
my $dbh = DBI->connect("dbi:mysql:", "usrname", "password") || die $DBI::errstr;
my $database= DBI->connect("dbi:mysql:database", "usrname", "password") || die $DBI::errstr;
my $insert_database=$database->prepare(qq{INSERT INTO table1 values (?,?,?,?,?,?,?)});
my $dbh1 = DBI->connect("dbi:mysql:database", "usrname", "password") || die $DBI::errstr;
my $lookup_access=$dbh1->prepare(qq{select id,name, update FROM table1
  WHERE idnum=?});
$Top="/home/alee/Top";
chdir $Top;
my $count=0;
while (my $subdir=<*>){
 chdir $subdir;
 while (my $file=<*.txt>){#check each file ending in .txt under the folder
   $file=~/^(\w*)./;#grasp the idnum
   my $idn=$1;
   $lookup_idn->execute($idn);
   my @result=$lookup_idn->fetchrow();
 ### get a date from each file; need to do conversion
 my @file=stat("$idn.txt");
 my $mtime=$file[9];
 my @testime=gmtime($mtime) ;
 my $year=$testime[5]+1900;
 my $mon=$testime[4]+1;
 my $mday=$testime[3];
 my $mon=sprintf("%02d",$mon);
 my $mday=sprintf("%02d",$mday);
 my $date= "$year-$mon-$mday";
 if (@result){#if a record with the same idnum already in the db
  my $id=$result[0];
  my $name=$result[1];
  my $update=$result[2];
  if ($date ne $update){
   #update the table1
   my $update_table1=$dbh1->prepare(qq{UPDATE table1
                                SET name=?,desc=?,type=?,update=?,count=?
                                 WHERE idnum=?});
   $update_table1->execute($name,$desc,$type,$update,$count,$idn);
   }
  }
 }
}

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Relevant Pages

  • Re: Performance issue
    ... Put the SQL statements into a hash, ... use DBI qw; ... SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN =? ... Not only would this allow me to have an easier to maintain group of prepare statements, I also would be doing the prepare only once in the program instead of for each line of input file. ...
    (perl.dbi.users)
  • Performance issue
    ... I was trying to improve the performance of a Perl DBI query. ... process I was also trying to improve the look and maintenance of my code. ... SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN =? ... program instead of for each line of input file. ...
    (perl.dbi.users)