Whilst testing some TestBox glitches (more about that in a later article, but the executive summary is "it's good!"), I noticed some dodgy behaviour in the ODBC-date/time-formatting functions. In both ColdFusion and Railo.
TBH, I have no idea why we are using
createOdbcDate() in our codebase at all... we do not use ODBC drivers. And that'd be the only time one would want to use these functions. Equally, they're only really useful if one wants to pass dates to DBs as strings, hard-coded into the SQL statement, and this should be actively discouraged in favour of passing parameters; and for that a native CFML date/time object is fine.Firstly... what the hell are these functions for? (that's rhetorical... I'm about to tell you...) ODBC defines a string format one can use to unambiguously represent a date value. The details are in the Microsoft docs: "ODBC Datetime Format", but a summary is this:
Note that the specification is very clear that the{ literal_type 'constant_value' }
literal_type
Specifies the type of the escape sequence. Timestamps have three literal_type specifiers:
- d
- date only
- t
- time only
- ts
- timestamp (time + date)
constant_value
Is the value of the escape sequence. constant_value must follow these formats for each literal_type.
- d
yyyy-mm-dd- t
hh:mm:ss[.fff]- ts
yyyy-mm-dd hh:mm:ss[.fff]
d is date only; the t is time only; and the ts is both.My issue is that I had a unit test which was comparing two ODBC dates, and they were not equally each other, despite the fact being created from
now() on virtually consecutive statements. I quickly guessed - correctly as it turns out - where ColdFusion was being dumb.This code demonstrates:
// odbcDateAndTimeFunctions.cfm
param name="URL.dateType" type="string";
function createTestObject(required string dateType, required date date){
return evaluate("createOdbc#dateType#(date)"); // reasonable use of evaluate()!
}
d1 = createTestObject(URL.dateType, now());
sleep(5000);
d2 = createTestObject(URL.dateType, now());
writeDump(var=[
{"d1.getClass().getName()"=d1.getClass().getName()},
{d1=d1},
{d2=d2},
{"d1==d2" = d1==d2},
{'dateDiff("s", d1, d2)' = dateDiff("s", d1, d2)},
{"d2-d1" = d2-d1},
{"d1.getHours()" = d1.getHours()},
{"d1.getMinutes()" = d1.getMinutes()},
{"d1.getSeconds()" = d1.getSeconds()},
{"d1.toString()" = d1.toString()}
], label="Using createOdbc#URL.dateType#()");
Here I do this:
- create two ODBC date objects;
- with a 5sec margin in between.
- As I'm running this code @ 8:45am, both these dates are the same.
- I then output various telemetry values:
- the underlying class of the object;
- the actual string value for both (correct on ColdFusion);
- whether they're equal (they should be, but are not);
- the dateDiff() of them: (should be zero, but it's not);
- (belabouring the point now): the arithmetical difference between the two (again, should be zero);
- calling some of the underlying Java methods to reveal the issue: ColdFusion is - incorrectly - storing hours, minutes, seconds values (that the min and seconds are the same here is purely coincidence);
- the string value from Java's perspective.
| Using createOdbcDate() - array | |||||
|---|---|---|---|---|---|
| 1 |
| ||||
| 2 |
| ||||
| 3 |
| ||||
| 4 |
| ||||
| 5 |
| ||||
| 6 |
| ||||
| 7 |
| ||||
| 8 |
| ||||
| 9 |
| ||||
| 10 |
| ||||
The problem here is revealed if one looks at the definition of the underlying class (courtesy of ClassViewer.cfc... and more on that later, too):
public class coldfusion.runtime.OleDate
extends coldfusion.runtime.OleDateTime
extends java.util.Date
extends java.lang.Object
This inheritance scheme seems screwed up to me? A class that is a date is not a subclass of a class that is a datetime. It's the other way around. I guess there's a case to be made that a "date" is a specialisation of a "datetime" wherein the hour, min and seconds values are always zero. Although I think that's a bit leaden, because in reality it's not that a date has zero for its hours, minutes and seconds... it's that it doesn't have hours, minutes and seconds.Whichever way one spins it, Adobe (or - more to the point - Macromedia) have screwed up here. If they insist on subclassing a date/time class to represent a date... then they need to make sure the hours / minutes / seconds are actually zero.
Railo works the same as far as hours / minutes / seconds go, but the actual functions don't do what they're supposed to. Here's an extract of the dump from above, as run on Railo:
| Using createOdbcDate() | |||||||||||||||||||||
| |||||||||||||||||||||
Everything else was equally wrong, but Railo doesn't even actually return an ODBC-formatted string for the value. This is just a complete fail on their part. I suspect they simply never checked what the function was supposed to do before implementing it.
To be completely frank, these functions are a stupid waste of time, and never should have been implemented. All that should have been implemented is some predefined mask patterns on
dateFormat() and timeFormat() (and, of course, dateTimeFormat() should have been part of the language from the outset). EG:dateFormat(now(), "ODBC"); // ODBC date
timeFormat(now(), "ODBC"); // ODBC time
dateTimeFormat(now(), "ODBC"); // ODBC date/time
My remedy here would be to deprecate the ODBC-specific functions, and add these masks to the date formatting functions. The requirement here is to format a string for use with ODBC drivers. There's not a need for ODBC-aware objects. That was the wrong way to implement this functionality.
I'll raise tickets for ColdFusion (bugs in ODBC functions: 3700847; enhancement to formatting functions: 3700853) and Railo (RAILO-2880, RAILO-2882 respectively), and post them back here. And work out why we are using ODBC-formatted anything in our codebase.
Righto.
--
Adam
