perl dbi 回滚不起作用

发布于 2024-09-18 13:47:26 字数 614 浏览 11 评论 0原文

我正在使用这种方法。如果 sql 中有错误，则仅对 asset_group 的第一个 id 进行回滚。其余 id 将被忽略。我的做法正确吗？

my $sql = "sql batch that update and insert depending on the condition";  
$dbh->{RaiseError} = 1;  
$dbh->{PrintError} = 0;  
$dbh->{AutoCommit} = 0;  

my $sth = $dbh->prepare($sql);  
my @error = ();  
my $num = 0;  
foreach my $id (@asset_group) {  
 next if ($id eq '');  
 eval {  
  $sth->bind_param(1, $id);  
  $sth->bind_param(2, $vars{'other_id'});  
  $sth->execute();  

 };  
 if ($@) {  
  $dbh->rollback();  
  push @error, $@  
 } else {  
  $dbh->commit();  
 }  
}

原文

i am using this approach. If there is an error in the sql, rollback only happens for the first id of the asset_group. Rest of the ids are ignored. Am i doing it the right way?

my $sql = "sql batch that update and insert depending on the condition";  
$dbh->{RaiseError} = 1;  
$dbh->{PrintError} = 0;  
$dbh->{AutoCommit} = 0;  

my $sth = $dbh->prepare($sql);  
my @error = ();  
my $num = 0;  
foreach my $id (@asset_group) {  
 next if ($id eq '');  
 eval {  
  $sth->bind_param(1, $id);  
  $sth->bind_param(2, $vars{'other_id'});  
  $sth->execute();  

 };  
 if ($@) {  
  $dbh->rollback();  
  push @error, $@  
 } else {  
  $dbh->commit();  
 }  
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尘世孤行 2024-09-25 13:47:26

根据数据库的不同，您可能需要在开始更改之前发出开始工作。我似乎记得 Informix 需要一个。

另外，看起来您在每次执行后都会发出提交或回滚。一旦提交，就无法回滚。之类的内容

$dbh->begin_work;
eval {
    for my $id (@asset_group) {  
        next if ($id eq '');  
        $sth->execute($id, $vars{other_id});  
    }
    1; #if it doesn't die then this will force it to return true
} or do {
    my $error = DBI->errstr;
    $dbh->rollback();
    die "could not insert rows: $error\n"
};
$dbh->commit();

通常，人们会说“注意我如何不使用 $@” 。 $@ 是不值得信任。

Depending on the database, you may need to issue a begin work before you start changing things. I seem to remember Informix requiring one.

Also, it looks like you are issuing a commit or a rollback after each execute. Once you commit, you can't rollback. Normally one says something like

$dbh->begin_work;
eval {
    for my $id (@asset_group) {  
        next if ($id eq '');  
        $sth->execute($id, $vars{other_id});  
    }
    1; #if it doesn't die then this will force it to return true
} or do {
    my $error = DBI->errstr;
    $dbh->rollback();
    die "could not insert rows: $error\n"
};
$dbh->commit();

Note how I don't use $@. $@ is untrustworthy.

回复收藏 0 原文

~没有更多了~