Link to home
Create AccountLog in
Avatar of new_perl_user
new_perl_user

asked on

Deleting files based on the data in the datadase

Hi,
 
 According to my logic I need to accomplish in this script(Logic:  My perl script should check for a certain column(column name STATUS) in a table(table name INFO). If the status column is equal to ' D' it has to look at the data in the" ID column","number column", remember that ID and number and then go to the location "c:\Documents and Settings\user\Files and delete the files that are named by this ID.).

I was able to connect to oracle and select the required  columns and print them, but need help from here. How to  remember the data in the "ID column","number column and go to the location " c:\Documents and Settings\user\Files" and delete them.

Current Script:
#!/usr/perl/5.8.8/bin/perl -w
use strict;
use DBI;


my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
my $SEL = "SELECT  number,id,status FROM info where status in ('I,D')";
my $sth = $db->prepare($SEL);
$sth->execute();

while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
   print "\n";
}

END {
    $db->disconnect if defined($db);
}

current output:

number     Id                status
HDF0008    89456R       I
HDF0009    34567R      D


Files:  c:\Documents and Settings\user\Files

HDF0009 -> 34567R -> folder1 , folder2 ....
HDF0008 -> 89456R -> folder1 , folder2....

  DIR  ->  Subdirectory ->  folder1 , folder2....

Thanks,
Avatar of Steve Tempest
Steve Tempest
Flag of Australia image

Hi,

I've altered the way your select statement is read in using fetchrow_hashref instead of fetchrow_array as I find that easier.

I'm not sure exactly what you need deleted but this should get you started with the location.
I've removed the I type from your select as it seems from your description that you are only looking for D status.

If you provide some more info regarding how the deletions should work I can provide the rest of the code.
#!/usr/perl/5.8.8/bin/perl  -w
use strict;
use DBI;


my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $dbh->prepare("SELECT  number,id FROM info where status = 'D'");
$SEL->execute();
while($subref = $SEL->fetchrow_hashref()) {
    my $number=$subref->{'number'};
    my $id=$subref->{'id'};
    print "Location: c:/Documents and Settings/user/Files/$number/$id/";
    last;
    }
$SEL->finish();
$db->disconnect if defined($db);

Open in new window

Avatar of new_perl_user
new_perl_user

ASKER

Hi,
 Thank you so much for the code. I tried to execute the above script and it is showing errors.

Use of uninitialized value in concatenation (.) or string at ./dbo.pl line 20.
Use of uninitialized value in concatenation (.) or string at ./dbo.pl line 20.
Location: c:/Documents and Settings/user/Files///

And I am trying to delete the "$id directory from the location.
ok sorry... here is a slighly modified version of above code.

 
#!/usr/perl/5.8.8/bin/perl  -w
use strict;
use DBI;


my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $db->prepare("SELECT  number,id FROM info where status = 'D'");
$SEL->execute();
while( my $subref = $SEL->fetchrow_hashref()) {
    my $number=$subref->{'number'};
    my $id=$subref->{'id'};
    print "Location: c:/Documents and Settings/user/Files/$number/$id/\n";
    last;
    }
$SEL->finish();
$db->disconnect if defined($db);

Open in new window


From my testing it prints the following to the screen

Location: c:/Documents and Settings/user/Files/HDF0009/34567R/

Can you please test and confirm?
Hi,
 
 I am not knowing where I am doing the mistake, still I am facing with the same problem. I tried to just change the column names according to my original column names which I did not mention here earlier because of some reasons.

Now I am finally posting the exact script I am trying to execute, please take a look and let me know what is wrong

#!/share_nfs/perl/5.8.8/bin/perl -w
use strict;
use DBI;


my $db = DBI->connect( "dbi:Oracle:******", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $db->prepare("SELECT  batch_number,nunique_id FROM batch_info_stage where book_status = 'D'");
$SEL->execute();
while( my $subref = $SEL->fetchrow_hashref()) {
    my $batch_number=$subref->{'batch_number'};
    my $unique_id=$subref->{'unique_id'};
    print "Location: /usr/Files/output/$batch_number/$unique_id/\n";
    last;
    }
$SEL->finish();
$db->disconnect if defined($db);


Error:

Use of uninitialized value in concatenation (.) or string at ./db5.pl line 18.
Use of uninitialized value in concatenation (.) or string at ./db5.pl line 18.
Location: /usr/Files/output///
Ok... here is the rest of the code.

Line #32 of the code has last; so that it will only process one record and then exit. When you are happy with the code remove this line and it will loop through your entire result set.

The code below will delete the 34567R folder and everything in it including subdirectories and files.

Example locaiton: c:/Documents and Settings/user/Files/HDF0009/34567R/

Please be careful to test the code using the print command before executing it over a large set of data.
#!/usr/perl/5.8.8/bin/perl  -w
use strict;
use DBI;
use File::Path 'rmtree';

my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $db->prepare("SELECT  number,id FROM info where status = 'D'");
$SEL->execute();
while( my $subref = $SEL->fetchrow_hashref()) {
    my $number=$subref->{'number'};
    my $id=$subref->{'id'};
    if((length($number) > 1)&&(length($id) > 1)){
        my $delpath="/asx/lost+found/$number/$id";
    }else{
        print "Number or ID have invalid length\n";
        $dbh->disconnect if defined($dbh);
        exit;
    }
    if (-d "$delpath"){
        print "Deleted: $delpath\n";
        rmtree($delpath);
    }else{
        print "Path not found $delpath\n";
    }
    print "Location: /asx/lost+found/$number/$id/\n";
    last;
    }
$SEL->finish();
$db->disconnect if defined($db);

Open in new window

You have a typo in your select statement.

nunique_id
I corrected the typo and it is showing the same error. Do my  above code conatin any error which I am not knowing. Because the same code is working for you but not  for me.
try this and let me know what it prints out.
#!/usr/perl/5.8.8/bin/perl  -w
use strict;
use DBI;
use File::Path 'rmtree';
use Data:Dumper;
my $delpath;

my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $db->prepare("SELECT batch_number,unique_id FROM batch_info_stage where book_status = 'D'");
$SEL->execute();
while(my $subref = $SEL->fetchrow_hashref()) {
		print Dumper($subref);
		last;
}
$SEL->finish();
$db->disconnect if defined($db);

Open in new window

Its printing the output:
$VAR1 = {
          'BATCH_NUMBER' => 'IMDBATCH0005',
          'UNIQUE_ID' => '60620010R'
        };
Hi,

The column names are case sensetive... so it was returning a NULL value using

my $number=$subref->{'number'};
    my $id=$subref->{'id'};

I've altered the code and tested on my server please see embedded code.

Also I have commented out the rmtree command for now.
#!/usr/perl/5.8.8/bin/perl  -w

use strict;
use DBI;
use File::Path 'rmtree';
my $delpath;

my $db = DBI->connect( "dbi:Oracle:*****", "*****", "******" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

my $SEL = $db->prepare("SELECT BATCH_NUMBER,UNIQUE_ID FROM batch_info_stage where book_status = 'D'");
$SEL->execute();
while(my $subref = $SEL->fetchrow_hashref()) {
    my $batch_number=$subref->{'BATCH_NUMBER'};
    my $unique_id=$subref->{'UNIQUE_ID'};
    if((length($batch_number) > 1)&&(length($unique_id) > 1)){
        $delpath="/asx/lost+found/$batch_number/$unique_id";
    }else{
        print "Number or ID have invalid length\n";
        last;
    }
    if (-d "$delpath"){
        print "Deleted: $delpath\n";
        #rmtree($delpath);
    }else{
        print "Path not found $delpath\n";
    }
    print "Location: /asx/lost+found/$batch_number/$unique_id/\n";
    last;
    }
$SEL->finish();
$db->disconnect if defined($db);

Open in new window

The above script is working fine and it is printing the output:


Path not found /asx/lost+found/IMDBATCH0005/60620010R
Location: /asx/lost+found/IMDBATCH0005/60620010R/
Can't locate object method "WindowSelect" via package "All" (perhaps you forgot to load "All"?) at ./test5.pl line 93.
ASKER CERTIFIED SOLUTION
Avatar of Steve Tempest
Steve Tempest
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

Hi,
 Thank you so much it is working now and I tried the rmtree and it removed exactly the folder I needed.
Even the error on the line 93 was gone. It has some weird statement there so I removed it and everthing is fine  now.

Really thanks a lot. Actually I was struggling with this for the past couple of days.

Thanks you.