DELETE statement with DBI/PERL

[jkuruvil]

I am trying to delete some records that satisfy a conditional clause, but the delete statement deletes all records instead. Can someone please help?
Here is the snippet of my code:

use strict;

use warnings;

use DBI;

my $dsn = "DBI:mysql:database=netbackup;host=localhost";

my $dbh = DBI->connect($dsn, 'root', 'password’, {'RaiseError' => 1});

# Database Statement Handles are defined below.

my $sth240 = $dbh->prepare("SELECT * FROM tbl_fsinclude WHERE PolicyID=(SELECT PolicyID FROM tbl_policyattribute WHERE PolicyName LIKE BINARY ?) AND filesystem LIKE BINARY ?");

my $sth250 = $dbh->prepare("INSERT INTO tbl_fsinclude (Filesystem, PolicyID) VALUES (?,(SELECT PolicyID FROM tbl_policyattribute WHERE PolicyName LIKE BINARY ?))");

my $sth280 = $dbh->prepare("DELETE FROM tbl_fsinclude WHERE PolicyID=(SELECT PolicyID FROM tbl_policyattribute WHERE PolicyName LIKE BINARY ?) AND Filesystem NOT IN (?)");

************Part of the main subroutine***********************************

# Insert Filesystems to the Database

if (/^Include:\s+\//) {

printf "Filesystem %s\n", $fields[1] if ($fields[1] =~ /\//);

if ($fields[1] =~ /\//) {

$includefiles[$k++] = $fields[1];

$sth240->execute($policyname, $fields[1]);

if ($sth240->rows == 0) {

$sth250->execute($fields[1], $policyname);

}

}

}

# Call subroutine to delete Filesystems no longer in the policy

if (/^\s+Saturday/ && $done == 0) {

&prunefsinclude();

&prunefsexclude();

$#includefiles = -1;

$#excludefiles = -1;

$newrecord = 0;

$sqlclause100 = "";

$done = 1;

}

****************subroutines***************************************************

sub prunefsinclude {

print "Filesystems in this policy are @includefiles\n";

print "array count for includefiles is $#includefiles.\n";

for $index (0 .. $#includefiles) {

$sqlclause280 .= "\'$includefiles[$index]\'";

$sqlclause280 .= ', ' unless $index == $#includefiles;

}

printf "Deleting filesystems not in include list %s\n",$sqlclause280 if ($sqlclause280);

$sth280->execute($policyname,$sqlclause280);

$sqlclause280="";

}

*****************Snippet of the Database***************************************

mysql> desc tbl_fsinclude;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| Filesystem | varchar(60) | YES | | NULL | |

| PolicyID | int(11) | NO | MUL | | |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> select * from tbl_fsinclude where PolicyID=158;

+------------+----------+

| Filesystem | PolicyID |

+------------+----------+

| /WS | 158 |

| /hwserver | 158 |

+------------+----------+

2 rows in set (0.00 sec)

mysql> insert into tbl_fsinclude values ('/nas',158);

Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_fsinclude where PolicyID=158;

+------------+----------+

| Filesystem | PolicyID |

+------------+----------+

| /WS | 158 |

| /hwserver | 158 |

| /nas | 158 |

+------------+----------+

 

[Kirsle]

Before complicating things by getting Perl involved, try just running those queries yourself at a mysql prompt. My guess is that the sub-select in your delete is returning all rows in the table, and therefore all rows get deleted. If it works the way you want it at a mysql prompt, but Perl is deleting all the rows instead, then do more debugging in your Perl code... i.e. make it print out how the final SQL query will look before it actually executes it, so you can compare that with what you would've typed yourself at a mysql prompt.

Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[jkuruvil]

When I run this query in MySQL it deletes records it deletes only /nas which is wanted done. I also hardcoded the values for NOT IN sub-select statement and deletes only /nas. But, when I use the place holder in my code, for some reason it deletes all records.

I guess, I will try debugging with your line as you suggested and see how it turns out.
I am sorry, can you explain what your code does please?

thanks much for your feedback

 

[travs69]

I gotta say running that code with out using strict would make me nervous

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[Annihilannic]

jkuruvil, Kirsle didn't actually give any code... I think the code you are seeing is just part of his signature (i.e. everything under the dotted line).

Annihilannic.

 

[jkuruvil]

thanks annihilannic! I tried to decipher that for a few minutes, I couldn't figure it out.

 

[Kirsle]

If you run the code in my signature, it just makes a little animated Footguy in your terminal.

$|= $i = 1; # turn off output buffering, also set $i = 1 too
print " oo\n"
    . "<|>\n"
    . "_|_"; # print initial footguy
x: # label
sleep $|; # sleep 1 second, abusing the fact $| = 1
print "\b", $i++ % 2 ? "/" : "_" ; # print "\b" (backspace), then "/" or "_" depending on if $i is divisible by 2
goto x; # go back to x

Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[jkuruvil]

that's nice of you to explain. thank you.