Hash Tie's DESTROY & DBI

[Anonymous Monk]

I'm working on tieing a hash to a mySQL database using some modified code from Conway's "OOP". When the hash is tied and a special key is set, it loads the data from the database appropriately. It seems that DESTROY doesn't like saving my changes. I have the following:

package Test;
my @STRUCTURE = qw(name test);
my $TABLENAME = "test";
my $KEY = "id";
*DBH = *main::DBH;

.
.

sub DESTROY {
    my($impl) = @_;
    my $keyval = $impl->{$KEY}->{value};
    my @list;
    my $time = time;
    push(@list,"timestamp='$time'");
    foreach(@STRUCTURE){
        my $val = $impl->{$_}->{value};
        $val =~ s/'/\\'/g;
        push(@list,"$_='$val'");
    }
    my $string = join ",",@list;
    $DBH->do("
        UPDATE $TABLENAME
        SET $string
        WHERE $KEY=$keyval
        LIMIT 1
    ");
}

If you set it to print the code, it does so and displays valid mySQL syntax. I find it interesting that die doesn't work within DESTROY, but I guess I could understand that. My tied hash is going out of scope by default when the namespace is cleared at the end of the script's execution. Why is it not achieving the desired effect?

[perrin]

You can't tell what's going on because you aren't checking for errors. You should either set DBI's RaiseError flag or check every DBI call. You do() call might be failing for any number of reasons.

What makes you think die() doesn't work inside DESTROY{}?

[gav^]

Some error handling might be nice:

$DBI->do("blah");
if ($DBI->err) {
   print "Error: ", $DBI->errstr, "\n"; 
}

Also you might want to consider placeholders, it makes quoting and stuff a lot less error prone:

foreach (@STRUCTURE) {
    push @list, "$_ = ?";
    push @vals, $impl->{$_}->{value};
}
my $string = join ",",@list;
$DBH->do(qq{
    UPDATE $TABLENAME SET $string WHERE $KEY=? LIMIT 1
}, undef, @vals, $keyval);

Hope this is of some help.

[tretin]

so by saying that die does not work in your script, that means that you tried and die did not work or did not produce any error (or something of that nature) or you assumed it would not, or something else? I am interested to know (since I do not know much about DBI) why it would not work there.

I know many do not like to useeval because of security reasons but, if you used it (in correspondence w/ $@) would that catch the errors if die did/could not? or would it not work for the same reason die would not/could not?

thank you for your time

-tretin

[runrig]

I know many do not like to use eval because of security reasons

eval "$data_from_tainted_source" has both security and performance issues.
eval { block_of_code() } does not (at least not due to the eval itself), and die'ing in an eval then checking $@ is a common method of 'exception handling' in perl.

[Ryszard]

Many moons ago my uni lecturer told us we cant test for everything because we will never know all the possible combinations of run-time variables that could occur.

Given that she also said that we should attempt to trap everything in a generic method reporting back any errors via logs, emails, or whatever was appropriate for the system being built.

Having said all that, there are a few things that could go wrong, for example null values and most obviously your dbi calls.

As the others have mentioned, add some error handling to see what is going on in your code, and whatever you do make sure you are using -w and strict.

As an aside, if this is is going to be a part of an object, it will be called many times. To increase performance, have you considered using bind vars in your code?

I'm not sure if this specifically applies to mysql, however with oracle and postresql, if you make a call to the database the sql is cached in the database. What this means, is when the DB engine gets the a statement, it will compare it to what it has in memory and use the memory cached version, rather than the one being parsed, thus saving on expensive I/O.

If you dont use placeholders (bindvars) in your code, each statement is potentially different, and thus the db cache is filled with useless statements that are only ever executed once.

Using placeholders, means that all the statements are the same as the variables in them are the placeholders ("?") so the db can re-use it.

Its a smart, easy and efficient style to code in that will make your application more scalable. Its so simple to do, i dont know why people dont, when they can.

Yet again i digress from the topic at hand... <sigh>