使用cfqueryparam创建准备语句的条件？ [英] condition for creating a prepared statement using cfqueryparam?

问题描述

cfquery 成为预备语句，只要有 cfqueryparam ORDER BY 子句或 FROM / code>子句是动态的吗？

当我们使用 cfloop 时会发生什么？ INSERT ，每个值cfqueryparam'ed，并调用具有不同迭代次数的cfquery？

准备报表？

数据库处理准备语句如何？它们会被转换成类似于存储过程的东西吗？

在什么情况下，我们不应该使用预备语句？

谢谢！

解决方案

我可以回答你的问题的某些部分：

一个查询将成为一个preparedStatement因为有一个< queryparam 。我以前在没有任何动态参数的查询中添加了
，其中1 =< cfqueryparam value =1它们作为preparedStatements运行

大多数DB处理preparedStarements类似于Stored Procedures，暂时暂停，而不是长期，但是细节可能是特定于DB的。 / p>

假设您正在使用ColdFusion提供的驱动程序，如果您在DataSource设置的高级面板中打开日志活动复选框，那么您将获得非常详细关于CF如何与DB交互以及创建新的preparedStatement以及何时重新使用它们的信息。我建议自己尝试一下，因为涉及到很多因素（DB设置，驱动程序，CF版本等）。如果你使用DB日志，在运行你的测试代码之前重新启动CF，所以你可以看到它创建预准备的语句，否则你会看到它重新使用语句的ID，而不看这些语句。 / p>

Does cfquery becomes a prepared statement as long as there's 1 cfqueryparam? Or are there other conditions?

What happen when the ORDER BY clause or FROM clause is dynamic? Would every unique combination becomes a prepared statement?

And what happen when we're doing cfloop with INSERT, with every value cfqueryparam'ed, and invoke the cfquery with different number of iterations?

Any potential problems with too many prepared statements?

How does DB handle prepared statement? Will they be converted into something similar to store procedure?

Under what circumstances should we Not use prepared statement?

Thank you!

解决方案

I can answer some parts of your question:

a query will become a preparedStatement as long as there is one <queryparam. I have in the past added a where 1 = <cfqueryparam value="1" to queries which didn't have any dynamic parameters, in order to get them run as preparedStatements

Most DBs handle preparedStarements similarly to Stored Procedures, just held temporarily, rather than long-term, however the details are likely to be DB-specific.

Assuming you are using the drivers supplied with ColdFusion, if you turn on the 'Log Activity' checkbox in the advanced panel of the DataSource setup, then you'll get very detailed information about how CF is interacting with he DB and when it is creating a new preparedStatement and when it is re-using them. I'd recommend trying this out for yourself, as so many factors are involved (DB setup, Driver, CF version etc). If you do use the DB logging, re-start CF before running your test code, so you can see it creating the prepared statements, otherwise you'll just see it re-using statements by ID, without seeing what those statements are.

这篇关于使用cfqueryparam创建准备语句的条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！