LiveDocs Comments - coldfusion - 7 - htmldocs - 00000317.htm

coldfusion/7/htmldocs/00000317.htm

Jon Briccetti — 2007-05-30T08:20:16

i've noticed that when using bind variables against views, if we re-build the views in the database and then try to access the cfm code with the bind variable queries, it throws an error. if we switch to using a straight SQL stmt, somethign like ... where column = '#var1#' it works... and then after "a little while" i can switch back to using the cfqueryparam and it all works fine. what **i'm guessing** is that the cached optimization info on the oracle side is no longer valid once we re-build the view, yet it must be trying to use that optimization plan anyway. later, when the optimization chache clears it works again.

coldfusion/7/htmldocs/00000317.htm

The Fletch — 2007-01-04T14:23:23

CF_SQL_TINYINT does not seem to work properly. I used CF_SQL_INT (which is not even listed in the options) and it works just fine for a tinyint datatype in an MSSQL database.

coldfusion/7/htmldocs/00000317.htm

anthony_id — 2006-12-08T14:54:21

RE: LIKE and MS SQL with cfqueryparam

To get around datatype matching with non character types, it is possible to CAST the column to varchar in the WHERE clause. In this case, the type is CF_SQL_VARCHAR in the cfqueryparam statement.

Note also that MS SQL uses the following format when dates are CAST:
mmm d yyyy (NOTE THERE ARE 2 [TWO!] spaces after mmm)

coldfusion/7/htmldocs/00000317.htm

anthony_id — 2006-12-08T14:32:26

RE: MS SQL LIKE statement

This only works if the % signs fit the datatype. I tried using it with a date datatype and received a type-mismatch error. This makes running a LIKE query on non *CHAR types impossible, so I've reverted to plain old text. This isn't a security concern if type checking/validation occurs on the data before it hits the query (mine is in a CFC), but negates any performance gains from cfqueryparam.

coldfusion/7/htmldocs/00000317.htm

2006-11-27T12:24:29

For MSSQL, the money data type should use CF_SQL_MONEY, not CF_SQL_DECIMAL.

coldfusion/7/htmldocs/00000317.htm

boughtonp — 2005-11-29T08:20:39

Something that I can't see mentioned above, but that is worth noting:
If you enter an invalid or mis-spelt cfsqltype it will not throw an error, it will simply switch to using CF_SQL_CHAR.

coldfusion/7/htmldocs/00000317.htm

Oblio — 2005-11-18T09:57:19

My tests show that Justin Fidler's article no longer applies when issuing nulls. I'm able to use the 'null' attribute to override the 'value' as the documentation describes. Further, the documentation doesn't note that any boolean value can be substituted for the 'yes|no' assignment to 'null'; for example, to only insert form fields that have been filled in, I used this:

coldfusion/7/htmldocs/00000317.htm

ASandstrom — 2005-09-27T07:16:16

For information about using the null attribute, see the article by Justin Fidler at http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html

Quoting from the article:
"There are a few things to keep in mind when using null variables. The VALUE parameter must be an empty string. By empty string, I mean two sets of double quotes with nothing in between. If you have anything else in there, even just a blank space, it can throw an error.

Also, make sure to set NULL="YES" or it won't set it to NULL properly. Finally, make sure to use the proper CFSQLTYPE to match the field you're setting to NULL. This may seem a bit counter-intuitive because NULL has no data type, but you may run into problems if you don't"

coldfusion/7/htmldocs/00000317.htm

edgriffiths — 2005-09-20T09:05:01

To perform an MS SQL 'LIKE' comparison using CFQUERYPARAM place the percent signs within the 'value' attribute:

SELECT moreStuff
FROM myTable
WHERE stuff LIKE

coldfusion/7/htmldocs/00000317.htm

ASandstrom — 2005-08-01T12:58:45

In response to switchbox: A documentation bug (#60837) has been entered so that this will be corrected in the next release of ColdFusion.

coldfusion/7/htmldocs/00000317.htm

switchbox — 2005-07-14T19:43:41

The MSSQL data type uniqueidentifier is a single token, not 2 words. The same applies to smalldatetime and smallmoney.

coldfusion/7/htmldocs/00000317.htm

bizarrojack — 2005-07-12T07:54:55

ColdFusion does not require the "null" attribute, but if you have a numeric field that can be null, it is required to avoid a situational crash - "" (blank) will not be translated to NULL if you use only the "value" attribute even if your DBMS would accept SQL syntax that way; you have to consider the variable twice per cfqueryparam, like

This may depend on your DBMS.

coldfusion/7/htmldocs/00000317.htm

bizarrojack — 2005-04-21T08:36:38

So I am told, the speed bonus comes in when a RDBMS' query optimizer can be circumvented, i.e. when the optimization plan is cached already. When non-cfqueryparam-ed query's optimization step is trivial, you wont see an improvement, and if it is repeated exactly (including arguments) you would also not see an improvement by using cfqueryparam. Without cfqueryparam (bind variables), queries with different arguments won't appear to be a match in the query optimizer cache lookup, and what is effectively the same query will have to go through the optimization process, basically needlessly.

You can see in the cfdebug output that the SQL without cfqueryparam is like this:
select x,y,z from table where foo = 'bar' . . .
select x,y,z from table where foo = 'baz' . . .
which is two entries in the optimization plan cache, whereas with cfqueryparam it will be
select x,y,z from table where foo = ? . . .
both times, with the variables printed afterwards. These are somewhat bad examples, because the optimization is trivial, and you wont be able to tell from the elapsed time whether that step was repeated or not.