Announcement

Announcement Module
Collapse
No announcement yet.

PERL MY SQL SCRIPT

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • PERL MY SQL SCRIPT

    Good Day all,

    I'm a newbie to both sql and perl. I'm forcing my self to learn by creating programs that i would like to use.

    I'm a little stuck on my first program. I've created a db that has a file path, file date, and date printed.

    weekly i want to run a script that will look in a particular directory. Check all files added, print them, and add them to my db.

    #!/usr/bin/perl

    # PERL MODULES WE WILL BE USING
    use Mysql;
    use DBD::mysql;
    use DBI;

    # CONFIG VARIABLES
    my $platform = "mysql";
    my $database = "timecards";
    my $host = "localhosts";
    my $port = "3306";
    my $tablename = "ttimecards";
    my $user = "perluser";
    my $pw = "password";

    my $startdir = '/home/mendezj/WA/TC';
    my ($n, $fn, @dirlist, @words);

    # DATA SOURCE NAME
    $dsn = "dbi:mysql:$database:localhost:3306";

    # PERL DBI CONNECT
    $connect = DBI->connect($dsn, $user, $pw) or die "Unable to connect: $DBI::errstr\n";

    chdir($startdir);
    opendir( DIRECTORY, '.' );
    my @allDirs = readdir( DIRECTORY );
    closedir( DIRECTORY );

    for( my $i = 0; $i <= $#allDirs; $i++ ) {
    if( -d $allDirs[ $i ] ) {
    next if $allDirs[$i] =~ /[.]$/;
    push @dirlist, "$startdir\/$allDirs[ $i ]";
    }
    }

    foreach (@dirlist) {
    chdir($_);
    opendir( DIRECTORY, '.' );
    my @subdirs = readdir( DIRECTORY );
    closedir( DIRECTORY );
    foreach my $tmp (@subdirs) {
    next if $tmp =~ /[.]$/;
    my $fpath = "$_\/$tmp";
    print "$fpath\n";
    my @stat = stat( "$_\/$tmp" );

    ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isd st) = localtime($stat[10]);
    my $dt = sprintf("%4.4ld-%2.2ld-%2.2ld", $year+1900, $mon+1, $mday);
    my $dtc = "2007-06-05";
    #create a sql statement to see if $_\/$tmp is in the database
    $query = "SELECT * FROM $tablename WHERE path = '$fpath'";
    $query_handle = $connect->prepare($query);
    $query_handle->execute() || die "Could not execute SQL statement ... maybe invalid?";
    # if yes do nothing if no print and add to the database
    if ($query_handle = 1) {
    print "need to add $fpath to the db\n";
    }

    Since all files in my database are already printed. what i have done is create a new list of the items in my directory, create a query to see if the file path is already in the database, if the file path is in the database than the file was already printed so do nothing...if on the other hand the file is not in the db print the file and add the file, file date, and date printed to the data base.

    what i was hoping to see from the following statement was
    if ($query_handle = 1)
    if my query is true that that should mean that the file is already in my database so do nothing. ELSE print and add to database.

    any help would be much appreciated.
Working...
X