Rows fail to delete without error

jerrygarciuh has asked for the wisdom of the Perl Monks concerning the following question:

The sub below is part of the calendar app I am developing. Everything works except the elsif. The elsif conditional fires off appropriately (checked it with a print statement). But the rows are not deleted. When the Add branch above it executes, the rows are deleted following an eval{} of their being moved. Even if a dozen are ok'ed they all get moved and then old row deleted but the identical code in the straight delete sequence does not throw errors or delete the rows. Anyone know why?
TIA
jg

sub approve_posts_now {
    my @key;
    foreach $key (%form_data) {
        if ($key =~ /^radio/ && $form_data{$key} eq "Add") {
            @key = split("," , $key);
            $sth = $dbh->prepare("INSERT INTO $calendar_table SELECT *
+ FROM $holding_table WHERE year=? AND
             month=? AND day=? AND time_start=? AND time_end=? AND tit
+le=?");
            $sth->bind_param(1, "$key[1]");
            $sth->bind_param(2, "$key[2]");
            $sth->bind_param(3, "$key[3]");
            $sth->bind_param(4, "$key[4]");
            $sth->bind_param(5, "$key[5]");
            $sth->bind_param(6, "$key[6]");
            eval {$sth->execute();};
            if (!$@) {
                $sth = $dbh->prepare("DELETE FROM $holding_table WHERE
+ year=? AND
                 month=? AND day=? AND time_start=? AND time_end=? AND
+ title=?");
                $sth->bind_param(1, "$key[1]");
                $sth->bind_param(2, "$key[2]");
                $sth->bind_param(3, "$key[3]");
                $sth->bind_param(4, "$key[4]");
                $sth->bind_param(5, "$key[5]");
                $sth->bind_param(6, "$key[6]");
                $sth->execute();
            }
        } elsif ($key =~ /^radio/ && $form_data{$key} eq "Delete") {
                $sth = $dbh->prepare("DELETE FROM $holding_table WHERE
+ year=? AND
                 month=? AND day=? AND time_start=? AND time_end=? AND
+ title=?");
                $sth->bind_param(1, "$key[1]");
                $sth->bind_param(2, "$key[2]");
                $sth->bind_param(3, "$key[3]");
                $sth->bind_param(4, "$key[4]");
                $sth->bind_param(5, "$key[5]");
                $sth->bind_param(6, "$key[6]");
                $sth->execute();
        }
    }
    @success_message = h2({-align=>'center'},"Database(s) updated. Tha
+nks!");
    success_message();
}
[download]

_____________________________________________________
Think a race on a horse on a ball with a fish! TG

Comment on Rows fail to delete without error Download Code

Replies are listed 'Best First'.
Re: Rows fail to delete without error by Ovid (Cardinal) on Apr 08, 2002 at 21:14 UTC
Well, there's the usual part about use strict. If you are using strict, then you're declaring these variables outside of your subroutine. From the way you have the last two lines of the subroutine, it appears to me that you are setting global variables and having other subs rely on them. This is difficult because it can be a problem telling where a variable is initialized, changed, or reused. I can't tell from your code where the problem is (am I missing something simple?), but you can simplify some stuff: `$sth = $dbh->prepare("DELETE FROM $holding_table WHERE year=? AND month=? AND day=? AND time_start=? AND time_end=? AND title=?"); $sth->bind_param(1, "$key[1]"); $sth->bind_param(2, "$key[2]"); $sth->bind_param(3, "$key[3]"); $sth->bind_param(4, "$key[4]"); $sth->bind_param(5, "$key[5]"); $sth->bind_param(6, "$key[6]"); $sth->execute();` [download] That can become: `$sth = $dbh->prepare("DELETE FROM $holding_table WHERE year=? AND month=? AND day=? AND time_start=? AND time_end=? AND title=?"); $sth->execute( @key );` [download] Questions: Do you set the `RaiseError` flag when you instantiate the DBI object? Are you sure that your values from the split are correct? You're splitting on a comma and an extra comma could make life difficult. Have you tried printing out all of the `@key` values and creating the SQL by hand? If even one of your values does not match a value in the database, the SQL will fail. Cheers, Ovid Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.	[reply] [d/l] [select]
Re: Re: Rows fail to delete without error by extremely (Priest) on Apr 08, 2002 at 21:31 UTC
Shouldn't that `$sth->execute( @key );` actually be `$sth->execute( @key[1..6] );`? -- $you = new YOU; honk() if $you->love(perl)	[reply] [d/l] [select]
Re: Re: Rows fail to delete without error by jerrygarciuh (Curate) on Apr 08, 2002 at 21:34 UTC
Thx for the reply Ovid. The DBI object is instantiated like so: `my $dbh = DBI->connect($dsn, $user, $password,{'RaiseError' => 1});` I can't $sth->execute( @key ); as I am using elements 1 - 6 and not 0 or >6 . Element zero is the word radio to distinguish the radio values on which I am acting from the other params. The real puzzler here is that the data is the same for each branch and if we are Adding we move the row then delete if we are just Deeting we execute code identical to that which deletes the rows successfully if Add was chosen. Anyway I can't see that the split is at fault in one branch and not the other. Oh and yes I am running under strict and -w. Maybe I should create a trash table and just move the rows there and then delete the from $holding_table thereby replicating the success I have when Adding. lol..sigh. Thx for looking at it! jg	[reply] [d/l]
Re: Rows fail to delete without error by JayBonci (Curate) on Apr 08, 2002 at 21:09 UTC
That's the way the SQL delete statment works. You can execute any delete statement with parameters, and it comes back without any errors. `mysql> DELETE * FROM table WHERE something ='FOOBATBAR WAS HERE'; Query OK, 0 rows affected (0.00 sec)` [download] It's not going to error, it's just doing to return 0 rows, similar to a SELECT statment gone bad. You might want to check to see how many items where returned when you deleted them. It's hard to give specifics here (I'm not looking at your input or form objects), but here are a few debugging suggestions: Have it print out the entire composite SQL query and put it at a mySQL/SQL Server/whateverSQL prompt or debugger, and see where you are going wrong. Also, remember always to quote() your SQL injections if you're pulling stuff from form data. The DBI object gives you a quotation method for safe escaping of data. Otherwise, it's a major security risk, even if it doesn't inherently appear to be. Good luck. Debugging database stuff can be frustrating and tricky. --jb	[reply] [d/l]
Re: Rows fail to delete without error by RMGir (Prior) on Apr 08, 2002 at 21:29 UTC
Your elsif block is missing `@key = split("," , $key);` [download] I don't know if that explains the problem you're having, though, since I'm fairly clueless about DBI... -- Mike	[reply] [d/l]
Re: Re: Rows fail to delete without error by jerrygarciuh (Curate) on Apr 08, 2002 at 21:35 UTC
Thank you!!! jg _____________________________________________________ Think a race on a horse on a ball with a fish! TG	[reply] [d/l]
Re: Rows fail to delete without error by trs80 (Priest) on Apr 08, 2002 at 21:34 UTC
I looked back at some of your past posts before I made this post. I am not sure what your immediate problem is, but you have some serious scoping issues in your scripts. I am also not sure why you are so verbose with your execute statements. The delete could be rewritten as: `$sth = $dbh->prepare(qq!DELETE FROM $holding_table WHERE year=? AND month=? AND day=? AND time_start=? AND time_end=? AND title=?"); $sth->execute(@key);` [download] If there is a reason to use bind_param vs. this syntax someone please enlighten me, this is the way I have been doing it and it works for me, but TIMTOWTDI What are you doing at the bottom with @success_message? You assign a single value to it, but then does it get magically used by success_message? I say magicly because that is dangerous. You want to pass as much information into a sub as new each time. I would rewrite this as: `sucess_message( h2({-align=>'center'},"Database(s) updated. Thanks!") +);` [download] or at the very least: `my @success_message = h2({-align=>'center'},"Database(s) updated. Than +ks!"); sucess_message( @success_message );` [download] Where is $sth getting scoped at? What is your DBI connect string? (does it have RaiseError?) Please don't misread my curiosity as critizism. UPDATE: This was funny, Ovid and I were talking about the similarity of another post with his last meditation and now I have posted what sounds similar to Ovid's post on this node. Very odd day.	[reply] [d/l] [select]
Re: Re: Rows fail to delete without error by jerrygarciuh (Curate) on Apr 08, 2002 at 21:42 UTC
Thx for the reply, as RMGir pointed out my split was in the if block and elsif wasn't getting it. Regarding your question re bind_param() I was having a problem with failing to match know rows with simply using placeholders and someone suggested bind_param() and it worked so now I am cargo-cult programming and doing waht has worked without asking if it was a mere coincidence in the first place. Re using the array whole, I don't use the first element. Thx for the reply!! jg _____________________________________________________ Think a race on a horse on a ball with a fish! TG	[reply] [d/l]
Re: Re: Re: Rows fail to delete without error by trs80 (Priest) on Apr 08, 2002 at 21:53 UTC
Now you got me curious again. How do you not use the first element? You use @blah = h2("TEXT"); and then call blah, how does that not use the first element? `my @success = h2({},'TEST'); success(@success); sub success { print shift , "\n"; }` [download] There is only one element in the list that I can see in that syntax, what I am missing?	[reply] [d/l]
Re: Rows fail to delete without error by mattriff (Chaplain) on Apr 08, 2002 at 21:10 UTC
Have you tried checking the output of $sth->errstr right after the execute() that doesn't seem to be working? Update: I guess since you mention it's not giving answers then the answer to my question is probably yes. ;) All of JayBonci's suggestions make good starting points. - Matt Riffle	[reply]
Re: Re: Rows fail to delete without error by jerrygarciuh (Curate) on Apr 08, 2002 at 21:22 UTC
Yeah I checked it and it was empty. Just can't figure why the Add then Delete sequence always succeeds but the just Delete it sequence fails. Thx jg _____________________________________________________ Think a race on a horse on a ball with a fish! TG	[reply] [d/l]

Thank you!!!