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

问题描述

只要有 1 个 cfqueryparam，cfquery 是否会成为准备好的语句?还是有其他条件?

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

当 ORDER BY 子句或 FROM 子句是动态的时会发生什么?每个独特的组合都会成为一个准备好的语句吗?

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

当我们使用 INSERT 执行 cfloop 时，会发生什么情况?每个值 cfqueryparam'ed，并以不同的迭代次数调用 cfquery?

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?

DB如何处理prepared statement?它们会被转换成类似于存储过程的东西吗?

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

什么情况下不应该使用prepared statement?

Under what circumstances should we Not use prepared statement?

谢谢！

推荐答案

我可以回答你的部分问题:

I can answer some parts of your question:

只要有一个<queryparam，查询就会变成preparedStatement.我过去添加了一个where 1 = <cfqueryparam value="1" 到没有任何动态参数的查询，以便让它们作为preparedStatements运行

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

大多数 DB 处理preparedStarements 的方式与存储过程类似，只是暂时保留，而不是长期保留，但细节可能是特定于 DB 的.

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

假设您使用的是 ColdFusion 提供的驱动程序，如果您在 DataSource 设置的高级面板中打开Log Activity"复选框，那么您将获得有关 CF 如何与他的 DB 交互以及何时交互的非常详细的信息它正在创建一个新的preparedStatement 以及何时重用它们.我建议您自己尝试一下，因为涉及很多因素(数据库设置、驱动程序、CF 版本等).如果您确实使用数据库日志记录，请在运行测试代码之前重新启动 CF，这样您就可以看到它创建了准备好的语句，否则您只会看到它通过 ID 重用语句，而看不到这些语句是什么.

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屋！