Perl DBI：如何查看带有绑定值的失败查询？
这是来自 DBI 手册的标准插入示例：
my $query = q{
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
};
my $sth = $dbh->prepare($query) or die $dbh->errstr;
while (<>) {
chomp;
my ($product_code, $qty, $price) = split /,/;
$sth->execute($product_code, $qty, $price) or die ($query . " " . $dbh->errstr);
}
$dbh->commit or die $dbh->errstr;
我对其进行了一些修改，因此我可以在哪个查询失败了（die ($query . " ". $dbh->errstr)）。我仍然希望看到查询具有绑定值（因为它被执行）。如何获得？
编辑
顺便说一句，我也发现了一种尴尬的方式来查看带有绑定值的查询：您必须在查询中犯语法错误。例如，如果我像这样更改上面的查询：
my $query = q{
xINSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
};
我按照我想要的方式得到了它：
DBD::mysql::st 执行失败：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'xINSERT INTO Sample (product_code, qty, Price) VALUES ('1', '2', '3')' 附近使用的正确语法在第 1 行
有时它真的很有帮助。至少对我来说是这样。
This is a standard inserting example from DBI manual:
my $query = q{
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
};
my $sth = $dbh->prepare($query) or die $dbh->errstr;
while (<>) {
chomp;
my ($product_code, $qty, $price) = split /,/;
$sth->execute($product_code, $qty, $price) or die ($query . " " . $dbh->errstr);
}
$dbh->commit or die $dbh->errstr;
I modified it a bit, so I can see on die which query failed (die ($query . " " . $dbh->errstr)). Still I'd liked to see the query with bound values (as it was executed). How to get it?
Edit
Btw, i found a awkward way to see query with bound values too: you have to make syntax error in query. For example, if i change query above like that:
my $query = q{
xINSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
};
I got it back as i wanted:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xINSERT INTO sample (product_code, qty, price) VALUES ('1', '2', '3')' at line 1
Sometimes it really helps. At least it did to me.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用 DBI 的 ParamValues 来获取参数值，但是您不太可能在 DBD 中找到任何方法来获取 SQL 中实际的参数，因为它们大多是在 SQL 解析后发送到数据库的。您可以查看 DBIx::Log4perl查看错误处理程序中如何使用 ParamValues。您可能还会发现 DBIx::Log4perl 的某些部分很有用。
You can use DBI's ParamValues to obtain the parameter values but you are unlikely to find any method in a DBD to obtain the parameters actually in the SQL because they are mostly sent to the database after the SQL is parsed. You can look at DBIx::Log4perl to see how ParamValues is used in the error handler. You might also find some parts of DBIx::Log4perl useful.
没有标准的方法可以做到这一点。最接近的近似值是将每个占位符替换为（可能引用的）值。一般来说，可靠地解析 SQL 语句来查找作为占位符的问号而不是分隔标识符或字符串或注释的一部分是相当困难的。在示例中，您可以简单地查找问号；但这并不总是有效：
请注意，对于大多数（但不一定是全部）DBMS（以及大多数 DBD 驱动程序），语句在准备好后会发送到 DBMS；执行语句时仅发送值。永远不会创建一条将所有值替换到 VALUES 列表中的语句，因此 DBI 和 DBMS 都不应创建这样的语句。
There isn't a standard way to do that. The nearest approximation would be to substitute each placeholder by the (probably quoted) value. In general, it is fairly hard to reliably parse an SQL statement to find the question marks that are placeholders rather than parts of delimited identifiers or strings or comments. In the example, you can simply look for question marks; that won't always work, though:
Note that with most, but not necessarily all, DBMS (and hence most DBD drivers), the statement is sent to the DBMS when it is prepared; only the values are sent when the statement is executed. There never is a statement created with all the values substituted into the VALUES list, so neither DBI nor the DBMS should be expected to create one.
我认为，没有任何通用支持的 DBI 方法可以做到这一点，但个别数据库驱动程序可能允许这样做。你使用什么数据库？
There isn't any generically supported DBI way to do this, I think, but individual database drivers might allow it. What database are you using?