mysql - ColdFusion Parameterizing a Query

Question

I have a query that I run to populate a CFChart that I am trying to parametrize:

<cfquery name="total" datasource="#datasource#">
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = #month# 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")# 
    AND    technician_id = #techID#
 </cfquery>

Here is what I have tried:

<!---Open tickets from chosen year where technician is active --->
<cfquery name="total" datasource="#datasource#">          
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = <CFQUERYPARAM Value="#month#"> 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"> 
    AND    technician_id = <CFQUERYPARAM Value="#techID#">
</cfquery>

When I change my query to this it breaks my CFChart somehow. I don't get any CFErrors on the screen but my CFChart is blank.

I have narrowed it down to being related to this in my query:

#dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"

When I remove this parametrized part of the query and just put

#dateFormat(theMonth,"yyyy")#

it works.

Can anyone shed some light on this?

Answer 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