ColdFusion Parameterizing a Query

Solution 1:

I don't get any CFErrors on the screen but my CFChart is blank.

Ignoring the correct approach for a moment, the reason that happens is that you are using the incorrect cfsqltype for the parameters. So you are actually sending different values to the database (and consequently performing a different comparison) than you are thinking. As a result, the query fails to find any matching records. That is why your chart is blank.

By using cf_sql_timestamp you are converting the "value" into a full date/time object. However, YEAR() only returns a four digit number. So you are comparing apples and oranges. Conceptually, your query is actually doing this:

  WHERE  2014 = {ts '2009-02-13 23:31:30'}

The reason it does not throw an error is that date/time values are stored as numbers internally. So you are actually comparing a small number (ie year) to a really big number (ie date/time). Obviously the date value will be much bigger, so it will almost never match the year number. Again, conceptually your query is doing this:

 WHERE 2014 = 1234567890

Since cfsqltype is optional, a lot of people think it is not very important - but it is.

• Validation: In addition to its other benefits, cfqueryparam validates the supplied "value", based on the cfsqltype (date, date and time, number, etcetera). This occurs before the sql is ever sent to the database. So if the input is invalid, you do not waste a database call. If you omit the cfsqltype, or just use the default ie string, then you lose that extra validation.

• Accuracy Selecting the proper cfsqltype ensures you send the correct value to the database. As demonstrated above, using the wrong type can cause CF to send the wrong value to the database.

  The cfsqltype also ensures values are submitted to the database in a non-ambiguous format the database will interpret the way you expect. Technically you could send everything to the database a string. However, that forces the database to perform implicit conversion (usually undesirable).

  With implicit conversion, the interpretation of the strings is left entirely up to the database - and it might not always come up with the answer you would expect. Submitting dates as strings, rather than date objects, is a prime example of that. How will the current database interpret a date string like "05/04/2014"? As April 5th or a May 4th? It depends. Change the database or the database settings and the result may be completely different.

The only way to ensure consistent results is to specify the appropriate cfsqltype. It should match the data type of the comparison column/function, or at least an equivalent type. In the case of YEAR(), it returns a four digit number. So you should use cf_sql_integer, as Adrian mentioned the comments. The same applies to your MONTH() comparison.

 WHERE Year(ColumnName) = <cfqueryparam value="2014" cfsqltye="CF_SQL_INTEGER">
 AND   Month(ColumnName) = <cfqueryparam value="11" cfsqltye="CF_SQL_INTEGER"> 

Now having said all that, Dan's suggestion is the better way to perform date comparisons. That paradigm is more index friendly and works regardless of whether your target column contains a date (only) or a date and time. Note the use of cf_sql_date in his example.

• cf_sql_timestamp - sends both a date and time
• cf_sql_date - sends a date only. the time value is truncated

Solution 2:

I suggest you change your approach. Start with

StartDate = CreateDate(TheYearYouWant, TheMonthYouWant, 1);
EndDate = DateAdd("m", 1, StartDate);

and the date filtering of your query becomes:

where closed_date >= <cfqueryparam cfsqltype="cf_sql_date" value="#StartDate#">
and closed_date < <cfqueryparam cfsqltype="cf_sql_date" value="#EndDate#">

This will execute faster than using functions in your where clause.

Solution 3:

Your best bet it to parameterize every variable dropped into your query.

Include the cfsqltype as well as the value.

<cfquery name="total" datasource="#datasource#">          
    select *
    from closed_tickets
    where MONTH(closed_date) = <cfqueryparam cfsqltype="cf_sql_integer" value="#month#">
          AND YEAR(closed_date) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#dateFormat(theMonth,"yyyy")#" > 
          AND technician_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#techID#">
</cfquery>

CF_SQL_VARCHAR might work and it might not, depending on how the date is actually stored (date type or var char).