On Wed, 2004-01-07 at 12:37, [EMAIL PROTECTED] wrote:
> The following block was identified by the error message:
>
> foreach my $exp_id (values %experiments) {
> eval {
> my $q_delete_old_data = qq{DELETE DATA_TM
> WHERE EXP_ID = $exp_id};
> my $sth_delete_old_data = $dbh->prepare_cached($q_delete_old_data);
> $sth_delete_old_data->execute();
> $sth_delete_old_data->finish();
> undef $sth_delete_old_data;
> };
> }
(taking this back on-list, as there are some common DBI mistakes here)
Try this instead:
my $q_delete_old_data = q{ DELETE DATA_TM WHERE EXP_ID = ? };
my $sth_delete_old_data = $dbh->prepare($q_delete_old_data);
foreach my $exp_id (values %experiments) {
eval {
$sth_delete_old_data->execute($exp_id);
};
}
This code uses the placeholder functionality of DBI to let you prepare
the statement once, and then bind in different values each time you
execute it.
Your code was preparing a new statement for each value from
%experiments, which is very inefficient compared to using placeholders.
In addition, since you used prepare_cached, each of those prepared
statements was cached permanently. Each prepared statement eats up a
cursor! And one minor point: the eval is not necessary unless you want
to trap DBI errors when executing the statement, and even then, setting
$dbh->{RaiseError} = 0 might be a better option.
So, in summary:
1) Use placeholders whenever possible to represent changing data
2) Don't use prepare_cached unless you're sure you need it
3) Make errors non-fatal with $dbh->{RaiseError} = 0
-- Jeremy
_______________________________________________
Boston-pm mailing list
[EMAIL PROTECTED]
http://mail.pm.org/mailman/listinfo/boston-pm
