10

When coding a cfqueryparam or cfprocparam, cfsqltype is optional. However, I've usually seen it coded. Are there any benefits to specifying cfsqltype?

Jeromy French
  • 11,812
  • 19
  • 76
  • 129
froadie
  • 79,995
  • 75
  • 166
  • 235

2 Answers2

16

The main benefit is an additional level of sanity checking for your query inputs, prior to passing it into your query. Also, in the case of date time values, I believe CF will properly translate datetime strings into the proper database format, if the cfsqltype="CF_SQL_DATE" or ="CF_SQL_TIMESTAMP" is specified.

In addition, I think it makes it more clear for future developers to see the types excepted when they read your code.

Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • CF will also translate any truey/falsey values into CF_SQL_BIT (1 or 0) and any numeric value into CF_SQL_INT (rounding). – Henry Mar 19 '12 at 22:40
  • Thank you for a well-expressed answer! I figured these were some of the reasons, but couldn't find documentation and this is very clear and succinct – froadie Mar 20 '12 at 07:58
13

I would add to Jake's comment. In most RDBMS the database will need to run your variable through a type lookup to insure it's the proper type or can be cast to the proper type implicitly. A DB doesn't just throw a variable of "type Any" at a table or view. It has to build the proper typing into the execution plan. So if you don't provide a type you are asking the DB to "figure it out".

Whereas, when you specify the type you are pre-empting or pre-qualifying the data type. The engine knows the driver is presenting a variable of a certain type and can then use it directly or derive it directly.

Remember that, while security is a good reason to use cfqueryparam, it's only one reason. The other reason is to create correctly prepared statements that can executed efficiently - and ideally "pop" the execution plan cache on the DB server.

Mark A Kruger
  • 7,183
  • 20
  • 21
  • 2
    +1. Re: *.. you are asking the DB to "figure it out".* .. and the results are not always what you intended. Better to avoid implicit conversion. – Leigh Mar 20 '12 at 00:32
  • Thank you, good points. Just out of interest - does it cost extra overhead for the database to match up the data types? (As in, will it take slightly longer to run without cfsqltype because the db has to spend time "figuring it out?) – froadie Mar 20 '12 at 07:59
  • Froadie - there is some overhead yes. It would be pretty hard to measure I'm sure. In most cases you would not see it. But there's no reason I can think of to NOT use cfsqltype :) – Mark A Kruger Mar 20 '12 at 13:23
  • @froadie - Agreed, differences are probably not measurable unless it causes an implicit conversion of the column you are comparing against. Like `varchar vs int` That could be slower because it involves multiple values and could affect the database's utilization of indexes. – Leigh Mar 20 '12 at 17:53