1

I've just come across what I think must be a coldfusion bug.

I have a query:

<cfquery name="qryTest" datasource="#This.ds#">
SELECT *
FROM MyLovelyTable 
WHERE 1=1 
AND phoneNumber = <cfqueryparam cfsqltype="cf_sql_bigint" value="#variables.phoneNumber#"> 
AND callTime between <cfqueryparam cfsqltype="cf_sql_date" value="#variables.startDate#"> AND <cfqueryparam cfsqltype="cf_sql_date" value="#variables.endDate#"> 
ORDER BY phoneID
</cfquery>

The problem is that if I pass through the second date variable nothing is returned. If I copy the query prefix SQL and parameters out and query the database directly results are returned. Its just when it goes through CF that it doesn't work. If I remove the second date parameter it works fine. And if I convert the second date parameter into a string it will work.

I've tried swapping the value of the second parameter out to now() which doesn't work either.

As far as I can tell the issue is that the second date parameter is a date type.

Am I doing anything clearly wrong which im not aware of?

The startDate and endDate variables are coldfusion dateTime objects. CallTime is a dateTime column in a MariaDB.

Rumpleteaser
  • 4,142
  • 6
  • 39
  • 52
  • As mentioned below, cf_sql_datetime is not a valid type. The valid date/time types are: `cf_sql_date` (date only) and `cf_sql_timestamp` (date and time). However, the correct expression depends on what values you are comparing. Are the `callTime` values just dates or do they contain a date and time? If they only contain dates, then switching to `cf_sql_date` will do the trick. Otherwise, you will need a different expression. – Leigh Feb 03 '14 at 02:59
  • @Leigh using cf_sql_timestamp makes no difference. using cf_sql_date makes no difference. As mentioned in my question, the SQL expression is fine, it works when i transfer it into the database with the query parameters that show in the query object. The date objects do include time elements too. – Rumpleteaser Feb 03 '14 at 23:16
  • @ Laura - You have only given us part of the picture. We need to see a) the actual values of the variables: #startDate# and #endDate# b) a sample of the `callTime` values and c) the "good" results when you *"transfer it into the database* – Leigh Feb 03 '14 at 23:46
  • Also, based on the column name we are all making the assumption that the `callTime` data type of is datetime. Is that correct? – Leigh Feb 04 '14 at 00:06
  • @Leigh Yes callTime is a dateTime column. A) are being fed coldfusion datetime objects. B)example callTime value from the database: '2014-02-01 10:27:48' c) don't understand what you mean here. You're familiar with query responses in CF? you can copy the generated sql out and replace the parameters with the supplied parameters. I've done that. The results of the query are irrelevant. The issues is not with the between statement, or the actual dates i'm passing through. its something funky with with cfqueryparam – Rumpleteaser Feb 04 '14 at 00:53
  • The idea behind `a,b and c` is to provide an [sscce](http://www.sscce.org/) which we can run independently, and reproduce your issue. There are several possible reasons for the results you are getting from cfquery, but since we do not even know the values of your parameters... we can only guess what that reason might be. Make sense? – Leigh Feb 04 '14 at 01:58
  • *The issues is not with the between statement, or the actual dates i'm passing through. its something funky with with cfqueryparam* Possibly. But until you actually figure out the real cause/solution, do not rule out anything. FWIW, your current query seems to work fine with the built in mySQL driver. But again, without an actual repro case, I am just guessing about the actual values ... [SQLFiddle](http://sqlfiddle.com/#!2/c2b38/1/0) * [Test Case](http://pastebin.com/f1DF9ywp) – Leigh Feb 04 '14 at 02:20

1 Answers1

4

Try using cfsqltype="cf_sql_date" instead, as "cf_sql_datetime" is not a valid option. See the help docs for cfqueryparam.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Carl Von Stetten
  • 1,149
  • 8
  • 13
  • 1
    cf_sql_datetime isn't documented but it can work - probably depends on the specific JDBC drivers used or something. – Peter Boughton Feb 03 '14 at 14:21
  • @PeterBoughton - If it works, it is probably a coincidence. To get the proper date functionality, you need to use the correct type: `cf_sql_timestamp`. ACF just ignores invalid types like `cf_sql_datetime` and uses `cf_sql_char` instead, which would probably pass a string rather than a date object. – Leigh Feb 03 '14 at 15:02
  • I remember specifically needing to use `datetime` instead of `timestamp` - it may well have been because the date/timezone stuff was buggy and letting it go through as a string (which the DB itself then converted) avoided the incorrect processing. – Peter Boughton Feb 03 '14 at 15:30
  • Yeah, CF was almost certainly was passing the value as a string and letting the db do an implicit conversion. I suspect you could have used `cf_sql_foo` and it would have worked too ;-) – Leigh Feb 03 '14 at 15:36
  • (And just to clarify, I'm not suggesting the OP doesn't need to use `date` or `timestamp` instead, just that there are potential reasons why `datetime` might be used.) – Peter Boughton Feb 03 '14 at 15:37
  • 2
    Nah, doing that would definitely have broken because any existence of "foo" in my code will automatically throw a DullExampleVariableName exception. :P – Peter Boughton Feb 03 '14 at 15:38
  • 3
    Haha. Okay, how about `cf_sql_charcoalDateThymeInfusedBriquette`? Otherwise known as the more mundane `cf_sql_char` ;-) – Leigh Feb 03 '14 at 16:27
  • @cfvonner cf_sql_datetime certainly does work in general for dates. The field isn't a timestamp field hence I was using date. I've updated the syntax in the question and tested your suggestion. But it didn't change a thing. The only thing that helps is passing the second value through as a string in the right format :S – Rumpleteaser Feb 03 '14 at 23:14
  • 1
    @LauraHansen - No, it has nothing to do with dates. The cfsqltypes are based on the [jdbc java.sql.Types](http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/Types.html) and **there is no such type as cf_sql_datetime**. The jdbc type for passing a date and time is [TIMESTAMP](http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/Types.html#TIMESTAMP). When CF looks sees "cf_sql_datetime", it has no clue what that means, and rather than throwing an error, it simply substitutes `cf_sql_char` instead. – Leigh Feb 03 '14 at 23:57
  • 1
    (cont'd) So when you use it, you are most likely passing a `string`. While your database may be able to implicitly convert that string to the proper type, ... it does not mean "cf_sql_datetime" is actually a valid cfsqltype. That said - what is the actual data type of your column: `date, datetime, ...`? – Leigh Feb 03 '14 at 23:59