ColdFusion parametrizar una consulta

Tengo una consulta que ejecuto para rellenar un CFChart que estoy tratando de parametrizar:

 SELECT * FROM closed_tickets WHERE MONTH(closed_date) = #month# AND YEAR(closed_date) = #dateFormat(theMonth,"yyyy")# AND technician_id = #techID#  

Esto es lo que he intentado:

   SELECT * FROM closed_tickets WHERE MONTH(closed_date) =  AND YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"> AND technician_id =   

Cuando cambio mi consulta a esta, rompe mi CFChart de alguna manera. No tengo ningún CFErrors en la pantalla pero mi CFChart está en blanco.

Lo he reducido a estar relacionado con esto en mi consulta:

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

Cuando elimino esta parte parametrizada de la consulta y acabo de poner

 #dateFormat(theMonth,"yyyy")# 

funciona.

¿Alguien puede arrojar algo de luz sobre esto?

No tengo ningún CFErrors en la pantalla pero mi CFChart está en blanco.

Ignorando el enfoque correcto por un momento, la razón por la que sucede es que está utilizando el cfsqltype incorrecto para los parámetros. Por lo tanto, en realidad está enviando valores diferentes a la base de datos (y, en consecuencia, realizando una comparación diferente) de lo que está pensando. Como resultado, la consulta no puede encontrar ningún registro coincidente. Es por eso que su tabla está en blanco.

Al usar cf_sql_timestamp está convirtiendo el “valor” en un objeto completo de fecha / hora. Sin embargo, AÑO () solo devuelve un número de cuatro dígitos. Entonces estás comparando manzanas y naranjas. Conceptualmente, su consulta realmente está haciendo esto:

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

La razón por la que no arroja un error es que los valores de fecha / hora se almacenan como números internamente. Entonces, en realidad está comparando un número pequeño (es decir, año) con un número realmente grande (es decir, fecha / hora). Obviamente, el valor de la fecha será mucho mayor, por lo que casi nunca coincidirá con el número del año. Nuevamente, conceptualmente su consulta está haciendo esto:

  WHERE 2014 = 1234567890 

Como cfsqltype es opcional, mucha gente piensa que no es muy importante, pero lo es.

  • Validación: además de sus otros beneficios, cfqueryparam valida el “valor” proporcionado, basado en el tipo de cfsqltype (fecha, fecha y hora, número, etcétera). Esto ocurre antes de que el sql se envíe alguna vez a la base de datos. Entonces, si la entrada no es válida, no desperdicia una llamada a la base de datos. Si omite el tipo cfsql, o simplemente utiliza la cadena por defecto, entonces pierde esa validación adicional.

  • Precisión Seleccionar el tipo de cfsql adecuado le asegura que envíe el valor correcto a la base de datos. Como se demostró anteriormente, usar el tipo incorrecto puede hacer que CF envíe el valor incorrecto a la base de datos.

    El cfsqltype también asegura que los valores se envíen a la base de datos en un formato no ambiguo que la base de datos interpretará de la manera esperada. Técnicamente, podrías enviar todo a la base de datos una cadena. Sin embargo, eso obliga a la base de datos a realizar conversiones implícitas (generalmente no deseables).

    Con la conversión implícita, la interpretación de las cadenas queda en manos de la base de datos, y es posible que no siempre obtenga la respuesta esperada. Enviar fechas como cadenas, en lugar de objetos de fecha, es un excelente ejemplo de eso. ¿Cómo interpretará la base de datos actual una cadena de fecha como “05/04/2014”? ¿Como el 5 de abril o el 4 de mayo? Depende. Cambie la base de datos o la configuración de la base de datos y el resultado puede ser completamente diferente.

La única manera de garantizar resultados consistentes es especificar el cfsql tipo apropiado. Debe coincidir con el tipo de datos de la columna / función de comparación, o al menos un tipo equivalente. En el caso de YEAR() , devuelve un número de cuatro dígitos. Entonces debería usar cf_sql_integer , ya que Adrian mencionó los comentarios . Lo mismo aplica a su comparación MES () .

  WHERE Year(ColumnName) =  AND Month(ColumnName) =  

Ahora que ha dicho todo eso, la sugerencia de Dan es la mejor forma de realizar comparaciones de fechas. Ese paradigma es más amigable con los índices y funciona independientemente de si su columna de destino contiene una fecha (solo) o una fecha y hora. Tenga en cuenta el uso de cf_sql_date en su ejemplo.

  • cf_sql_timestamp – envía una fecha y hora
  • cf_sql_date – envía una fecha solamente. el valor de tiempo se trunca

Te sugiero que cambies tu enfoque. Empezar con

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

y el filtro de fecha de su consulta se convierte en:

 where closed_date >=  and closed_date <  

Esto se ejecutará más rápido que usando funciones en su cláusula where.

Lo mejor es apostar por parametrizar todas las variables incluidas en su consulta.

Incluya el cfsqltype así como el valor.

  select * from closed_tickets where MONTH(closed_date) =  AND YEAR(closed_date) =  AND technician_id =   

CF_SQL_VARCHAR podría funcionar y no, dependiendo de cómo se almacena realmente la fecha (tipo de fecha o var char).