| Bug #60648 | ODBC prepared statements ignore fractional part of temporal data types | ||
|---|---|---|---|
| Submitted: | 25 Mar 2011 18:03 | Modified: | 1 Mar 2012 14:16 |
| Reporter: | Vladislav Vaintroub | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 5.1.9 | OS: | Any |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[20 May 2011 11:43]
Bogdan Degtyariov
Thanks Vlad, The bug is verified.
[28 Aug 2011 12:45]
Lawrenty Novitsky
patch pushed to the branch bzr+ssh://lawrin@bazaar.launchpad.net/~myodbc-developers/myodbc/bug60648 it relies a modifies code from the patch for 60646. Patch's revno is 987(-2). Branch also contains patch for 60646 - 985(-4), patch for queries log location - 986(-3). And small patch for SQLCancel, it's more like a part of the transition to the 5.5. That is last revision in the branch.
[1 Mar 2012 14:16]
Lawrenty Novitsky
pushed as rev#1032, with some changes in rev#1034. If nothing goes wrong will be included to the next maintenance release(5.1.11).
[18 Apr 2012 23:46]
John Russell
Added to changelog for 5.1.11: Fractional seconds part of timestamp was ignored in prepared statements that use SQLBindParameter and SQL_C_TIMESTAMP type. For example, a prepared query comparing two timestamp values that only differed in the fractional part would consider the values identical.
Description: Fractional seconds part of timestamp is ignored in prepared statements that use SQLBindParameter and SQL_C_TIMESTAMP type. How to repeat: Here is the test case using odbctap format, for better integration into the test suite. The prepared SQL statement compares 2 different timestamps, that differ only in fractional second part ("2001-01-01 01:01:01" vs "2001-01-01 01:01:01.5") The expected result of prepared "SELECT ?=?" is false (or "0"), however the actual result is "1". DECLARE_TEST(t_fractional_bind_param) { char buf[100]; SQL_TIMESTAMP_STRUCT ts0, ts1; SQLLEN len; SQLRETURN rc; ts0.year=2001; ts0.month=1; ts0.day=1; ts0.hour=1; ts0.minute=1; ts0.second=1; ts0.fraction=0; ts1= ts0; /* Add fraction part 0.5 seconds */ ts1.fraction= 500000000; rc = SQLPrepare(hstmt,"SELECT ? = ?",SQL_NTS); mystmt(hstmt,rc); ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 0, &ts0, sizeof(ts0), NULL)); ok_stmt(hstmt, SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 0, &ts1, sizeof(ts1), NULL)); rc = SQLExecute(hstmt); mystmt(hstmt, rc); ok_stmt(hstmt, SQLFetch(hstmt)); ok_stmt(hstmt, SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &len)); ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); myassert(buf[0] == '0'); return OK; } Suggested fix: I think the place to fix is insert_param() function, where currently timestamps are converted to string using sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d", time->year, time->month, time->day, time->hour, time->minute, time->second); When fractional part is not 0, - string format could be changed to "%04d-%02d-%02d %02d:%02d:%02d.%09d" - time->fraction can be added as last parameter - string length of buff changes from 19 to 29 It can be optimized further (e.g sending triling zeros in fractional part to server is not necessary).