LiveDocs Comments - coldfusion - 6 - Developing_ColdFusion_MX_Applications_with_CFML - queryDB6.htm

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

bizarrojack — 2005-02-23T08:37:56

Qwicker - The cfqueryparam is obviously a little more work for the coldfusion engine, but do not let that fool you into thinking that using cfqueryparam is going to be slower. Indeed, it is possible, but is often just the opposite because of time saved by your RDBMS. I know this is true with oracle, but YMMV with another RDBMS, so I recommend investigating this: By using cfqueryparam, subsequent queries after the first one will be sped up significantly by eliminating time used by the query optimizer. cfqueryparam effectively puts "?" in the query, instead of submitting what is a unique query, in the eyes of the optimizer, so the optimizations are cached and this process can be skipped.

Clearly this depends on a number of factors, but I have seen, without question, a marked improvement in overall performance with cfqueryparam.

e.g.
select 'foo' from table etc. etc.
and
select 'bar' from table etc. etc. run through the query optimizer twice,
but wth cfqueryparam it will be
select '?' from table etc. etc., once with foo and once with bar, and the latter query will not have to be processed by the query optimizer. This is not the best example, because optimizing this query is trivial, but I hope it conveys the general idea.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

shawnse — 2004-10-28T11:13:26

Trusting that the cfsqltypes types are based on JDBC SQL types (from the above posting), you can also get general information on most of the cfsqltypes at:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

extdw_doc — 2004-10-05T10:39:17

You can find a link to a matrix that maps
cfsqltype values to DBMS-specific data types at:
http://www.mail-archive.com/cf-talk@houseoffusion.com/msg157220.html

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

sweetnevil — 2004-10-04T10:56:12

Is there a known map for DBMS-specific datatypes to cfSqlType values? Specifically, I'm wondering about date, time, and timestamp datatypes in the cfSqlType parameter against a datetime field in an MS SQL Server database. I don't suppose an answer will arrive here quickly, therefore I shall test... and post what I find. Thanks!

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

qwicker — 2004-09-21T09:49:22

i noticed that the initial query has single quotes around the value to be inserted (WHERE LastName='#Form.LastName#'). would this not prevent the sql injection from appearing as 2 queries like the value returned using the cfqueryparam tag?

WHERE LastName = 'Anwar DELETE FROM MyCustomerTable'.

i think i will test this theory. i am trying to decide if the cfqueryparam tag is necessary for us - i have heard that it is a significant hit on processing time.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

halL — 2004-08-10T13:03:21

Your error is in surrounding rpt_num in quotation marks.
The correct form is

The problem is that in your example the variable name is being assigned a string value, not a reference to the query object.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

2004-08-09T12:56:40

I tried this and it does not work

rpt_mem is a name of an existing query. But it denies it saying that coldfusion does not recognize the parameter.