多个准备好的语句使用 DBD::Sybase 中断事务

发布于 09-26 04:37 字数 1254 浏览 8 评论 0原文

在我的 Perl 脚本中,我使用 DBD::Sybase(通过 DBI 模块)连接到 SQL Server 2008。下面的基本程序运行没有问题:

use DBI;

# assign values to $host, $usr, $pwd
my $dbh = DBI->connect("dbi:Sybase:$host", $usr, $pwd);
$dbh->do("BEGIN TRAN tr1");
my $update = $dbh->prepare("UPDATE mytable SET qty = ? where name = ?");
$update->execute(100, 'apple');
$dbh->do("END TRAN tr1");

但是,如果我再插入一个 prepare 语句,则正确在现有的 prepare 语句之前,让程序看起来像:

...
my $insert = $dbh->prepare("INSERT INTO mytable (name, qty) VALUES (?, ?)");
my $update = $dbh->prepare("UPDATE mytable SET qty = ? where name = ?");
...

其余的都是一样的,然后当我运行它时,我得到:

DBD::Sybase::db do failed: Server message number=3902 severity=16 state=1 line=1 server=xxx text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

所以看起来像附加的 prepare声明以某种方式扰乱了整个交易流程。我一直通过 DBD::ODBC 驱动程序运行相同的代码,在 SQL SERVER 2005 上没有任何问题。(但我的公司升级到 2008,我必须使用 DBD::Sybase 来解决其他一些问题。)

任何有关如何解决此问题的帮助/建议将不胜感激。特别是,为另一个准备使用不同的数据库句柄并不是理想的解决方案,因为这将违背将它们放在单个事务中的目的。

更新:事实证明,如果我在附加插入上至少执行一次,那么程序将再次正常运行。所以看起来每个准备好的语句都需要在 Sybase 下运行。但这不是 ODBC 的要求,而且一般来说也不是合理的要求。无论如何要绕过它吗?

In my Perl script, I use DBD::Sybase (via DBI module) to connect to a SQL Server 2008. The base program as below runs without problem:

use DBI;

# assign values to $host, $usr, $pwd
my $dbh = DBI->connect("dbi:Sybase:$host", $usr, $pwd);
$dbh->do("BEGIN TRAN tr1");
my $update = $dbh->prepare("UPDATE mytable SET qty = ? where name = ?");
$update->execute(100, 'apple');
$dbh->do("END TRAN tr1");

however, if I insert one more prepare statement right before the existing prepare statement, to have the program look like:

...
my $insert = $dbh->prepare("INSERT INTO mytable (name, qty) VALUES (?, ?)");
my $update = $dbh->prepare("UPDATE mytable SET qty = ? where name = ?");
...

and the rest is all the same, then when I run it, I got:

DBD::Sybase::db do failed: Server message number=3902 severity=16 state=1 line=1 server=xxx text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So looks like the additional prepare statement somehow disrupted the entire transaction flow. I had been running the same code via the DBD::ODBC driver with no problem against a SQL SERVER 2005. (But my firm upgraded to 2008 and I had to use the DBD::Sybase to get around some other problems.)

Any help / suggestion on how to resolve this issue would be much appreciated. In particular, using a different db handle for the other prepare is not a desired solution since that will beat the purpose of having them in a single transaction.

UPDATE: Turns out if I execute at least once on the additional insert, then the program is again run fine. So looks like every prepared statement needs to be run under Sybase. But that isn't a requirement with ODBC and isn't a reasonable requirement in general. Anyway to get around it?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

同尘2024-10-03 04:37:33

不要动态创建SQL,这是危险的(sql注入)。

您应该能够准备多个插入/更新,并且您到 DBI 文档的链接并没有说您不能,它说某些驱动程序可能无法告诉您有关仅准备好的语句的更多信息。

我会将一个错误的失败示例发布到 dbi-users 列表以供评论,因为 DBD::Sybase 维护者在那里闲逛(请参阅 dbi.perl.org)。

Don't dynamically create SQL, it is dangerous (sql injection).

You should be able to prepare multiple inserts/updates and your link to the DBI documentation does not say you cannot, it says some drivers may not be able to tell you much about a statement which is ONLY prepared.

I'd post a failing example with error to the dbi-users list for comment as the DBD::Sybase maintainer hangs out there (see dbi.perl.org).

愛放△進行李2024-10-03 04:37:33

事实证明,DBI 的 prepare 方法在各种数据库驱动程序之间不太可移植,如 此处。对于 Sybase 驱动程序,prepare 很可能未按预期工作。一种判断方法是,运行 prepare 后,变量 $insert->{NUM_OF_FIELDS} 未定义。

要解决此问题,请执行以下操作之一:

1) 不准备任何东西。只需在文本字符串中动态构造语句并运行 $dbh->do($stmt),或

2) 对所有未完成的语句句柄(在该数据库句柄下)运行 finish )在运行COMMIT TRAN之前。我个人更喜欢这种方式。

Turns out that DBI's prepare method is not quite portable across various database drivers as noted here. For the Sybase driver, it is most likely that prepare is not working as intended. One way to tell is that after running prepare, the variable $insert->{NUM_OF_FIELDS} is undefined.

To get around the problem, do one of the following:

1) do not prepare anything. Just dynamically construct the statement in text string and run $dbh->do($stmt), or

2) run finish on all outstanding statement handles (under that database handle) before running COMMIT TRAN. I personally prefer this way much better.

撩人痒2024-10-03 04:37:32

您正在学习 perl 和 Sybase 基础知识并得出一些错误的结论。

暂时忘记它在 ODBC 下的作用。 ODBC 很可能打开了 AUTOCOMMIT,因此您没有任何事务控制。 (当 DBD:: 支持 DB-Lib 和 CT-Lib 时,为什么有人会使用 ODBC,这是我无法理解的,但这是一个单独的故事。)

回复:“所以看起来每个准备好的语句都需要在 Sybase 下运行。” Rawheiser

是正确的。您究竟希望通过准备一批但执行 Do 来实现什么?除了在 Sybase 下之外,您还希望在哪里执行在 Sybase 下准备的批处理?

执行与准备/执行有很大不同。 Sybase 的准备/执行在数百万个程序中运行良好。你只需要了解它的作用,而不是你认为它应该做什么。准备让您加载一批,即在正常 Sybase 意义上由 GO 终止的命令块。 Execute 执行准备好的批处理(提供 GO 并将批处理发送到服务器),并捕获返回的任何内容(根据您设置的任何数组/变量)。

执行是立即的、单一的命令,无需准备。准备+执行相结合。

仅执行单语句 do's,并且仅执行动态 SQL,因为这就是您可以开始工作的全部内容,这是非常有限的,而且完全没有必要。

您当前有:

准备:

UPDATE
Execute (100)
ExecuteImmediate(Do):
COMMIT TRAN

当然,没有 BEGIN TRAN。 (执行的第一个“do”,BEGIN TRAN消失了)

我认为你想要的(最初的意图)是这样的。忘记“do”:

准备:

BEGIN TRAN
UPDATE
COMMIT TRAN

执行(100)

然后将其更改为:

BEGIN TRAN
INSERT
UPDATE
COMMIT TRAN

执行(100)

您的 $update 和 $insert 会让您感到困惑(您正在执行多语句批处理,对吧?不是准备批次的中间)。如果您摆脱它们,并根据 $execute [无论您在批处理中准备的内容] 进行思考,它可能会帮助您更好地理解问题。

在上述所有工作均按预期进行之前,请勿下结论。

并阅读 BEGIN/COMMIT TRAN。

最后,“END TRAN”到底是什么?我认为您发布的代码块不是真实的。

You are learning perl AND Sybase basics and making several incorrect conclusions.

Forget about what it does under ODBC for a moment. ODBC most probably has AUTOCOMMIT turned on, and thus you have no transaction control whatsoever. (Why anyone would use ODBC when the DBD:: supports DB-Lib and CT-Lib is beyond me, but that's a separate story.)

Re: "So looks like every prepared statement needs to be run under Sybase."

Rawheiser is correct. What exactly do you expect to achieve by preparing a batch but performing a Do instead ? Where else do you expect to execute the batch prepared under Sybase, other than under Sybase?

Do vs prepare/execute are quite different. prepare/execute for Sybase works just fine in millions of programs. you just have to learn what it does, not what you think it should do. prepare let's you load a batch, a block of commands terminated by GO in the normal Sybase sense. Execute executes the prepared batch (supplies the GO and sends the batch to the server), and captures whatever is returned (according to whatever array/variables you have set).

Do is immediate, single command, with no prepare. A prepare+execute combined.

Performing only single-statement do's, and only dynamic SQL, simply because that's all that you could get to work, is very limiting and quite unnecessary.

You currently have:

Prepare:

UPDATE
Execute (100)
ExecuteImmediate(Do):
COMMIT TRAN

So of course, there is no BEGIN TRAN. (The first "do" executed, the BEGIN TRAN is gone)

I think what you want (intended originally) is this. Forget the 'do':

Prepare:

BEGIN TRAN
UPDATE
COMMIT TRAN

Execute (100)

Then change it to:

BEGIN TRAN
INSERT
UPDATE
COMMIT TRAN

Execute (100)

Your $update and $insert will confuse you (you're executing a multi-statement batch, right ?not a isolated single command in the middle of a prepare batch). If you get rid of them, and think in terms of $execute [whatever you have prepared in the batch], it might help you to understand the problem better.

Do not form conclusions until you have all the above working as intended.

And read up on BEGIN/COMMIT TRAN.

Last, What exactly is a "END TRAN" ? I do not think the code block you have posted is real.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文