| Bug #30626 | No result record for SQLGetTypeInfo for TIMESTAMP | ||
|---|---|---|---|
| Submitted: | 24 Aug 2007 16:38 | Modified: | 10 Nov 2009 15:08 |
| Reporter: | Colin Stearman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1.4 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Bug #7231, regression | ||
[24 Aug 2007 16:38]
Colin Stearman
[30 Aug 2007 6:22]
Tonci Grgin
Hi Colin and thanks for spotting this. Analyzing.
[30 Aug 2007 7:23]
Tonci Grgin
Colin, what do you mean by "definitely"? I've tested 3.51.19GA with generic MS ODBC client (odbcte32.exe) and found no problems (with MySQL 5.0.48pb on WinXP Pro SP2 localhost). Cross check client output with attached trace and tell me where's the error you're mentioning : Test sequence: 1) connect 2) SELECT * FROM table with DATE and TIMESTAMP FIELD 3) SQLGetTypeInfo, twice with different arguments (SQL_TIMESTAMP=11 and SQL_TYPE_TIMESTAMP=93) 4) SQLColumns to check on table structure Client output: Full Connect(Use Driver) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN 'duc129'. SQLExecDirect: In: hstmt = 0x008B1BF0, szSqlStr = "SELECT * FROM tblzaliha LIMIT 10", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "ID", "DokID", "TipDok", "TipSkl", "DatumDok", "StatusDok", "Promjena", "Godina", "ArtiklID", "Kolicina", "MT", "OrgDokID" 1, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:04, 2004, 1839, -1.0000, 129, 0 2, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:07, 2004, 1839, -1.0000, 129, 0 3, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:09, 2004, 1839, -1.0000, 129, 0 4, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:10, 2004, 1836, -1.0000, 129, 0 5, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:12, 2004, 1833, -1.0000, 129, 0 6, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:13, 2004, 1831, -1.0000, 129, 0 7, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:14, 2004, 1838, -1.0000, 129, 0 8, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:15, 2004, 1836, -1.0000, 129, 0 9, 3, "IRA", "MP", 2004-11-08, 0, 2004-11-08 14:59:54, 2004, 20659, -1.0000, 129, 0 10, 2, "IRA", "MP", 2004-11-08, 0, 2004-11-08 15:00:36, 2004, 1614, -1.0000, 129, 0 10 rows fetched from 12 columns. SQLGetTypeInfo: In: StatementHandle = 0x008B1BF0, DataType = SQL_TIMESTAMP=11 Return: SQL_SUCCESS=0 Get Data All: "TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATATYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION" "datetime", 93, 21, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "datetime", 0, 0, 9, 93, <Null>, <Null> "timestamp", 93, 14, "'", "'", <Null>, 0, 0, 3, 0, 0, 0, "timestamp", 0, 0, 9, 93, <Null>, <Null> 2 rows fetched from 19 columns. SQLGetTypeInfo: In: StatementHandle = 0x008B1BF0, DataType = SQL_TYPE_TIMESTAMP=93 Return: SQL_SUCCESS=0 Get Data All: "TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATATYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION" "datetime", 93, 21, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "datetime", 0, 0, 9, 93, <Null>, <Null> "timestamp", 93, 14, "'", "'", <Null>, 0, 0, 3, 0, 0, 0, "timestamp", 0, 0, 9, 93, <Null>, <Null> 2 rows fetched from 19 columns. SQLColumns: In: StatementHandle = 0x008B1BF0, CatalogName = "ducan129", NameLength1 = 8, SchemaName = "ducan129", NameLength2 = 8, TableName = "tblzaliha", NameLength3 = 9, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0 Return: SQL_SUCCESS=0 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "ducan129M", <Null>, "tblzaliha", "ID", 4, "integer unsigned", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, <Null>, 1, "YES" "ducan129M", <Null>, "tblzaliha", "DokID", 4, "integer unsigned", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, <Null>, 2, "YES" "ducan129M", <Null>, "tblzaliha", "TipDok", 1, "char", 10, 10, <Null>, <Null>, 1, "", <Null>, 1, <Null>, 10, 3, "YES" "ducan129M", <Null>, "tblzaliha", "TipSkl", 1, "char", 2, 2, <Null>, <Null>, 1, "", <Null>, 1, <Null>, 2, 4, "YES" "ducan129M", <Null>, "tblzaliha", "DatumDok", 91, "date", 10, 6, <Null>, <Null>, 1, "", <Null>, 9, 91, <Null>, 5, "YES" "ducan129M", <Null>, "tblzaliha", "StatusDok", -6, "tinyint unsigned", 3, 1, 0, 10, 1, "", <Null>, -6, <Null>, <Null>, 6, "YES" "ducan129M", <Null>, "tblzaliha", "Promjena", 93, "timestamp", 19, 16, 0, 10, 0, "", <Null>, 9, 93, <Null>, 7, "NO" "ducan129M", <Null>, "tblzaliha", "Godina", 5, "year", 4, 1, 0, 10, 1, "", <Null>, 5, <Null>, <Null>, 8, "YES" "ducan129M", <Null>, "tblzaliha", "ArtiklID", 4, "integer unsigned", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, <Null>, 9, "YES" "ducan129M", <Null>, "tblzaliha", "Kolicina", 3, "decimal", 12, 12, 4, 10, 1, "", <Null>, 3, <Null>, <Null>, 10, "YES" "ducan129M", <Null>, "tblzaliha", "MT", 4, "integer unsigned", 10, 4, 0, 10, 0, "", <Null>, 4, <Null>, <Null>, 11, "NO" "ducan129M", <Null>, "tblzaliha", "OrgDokID", 4, "integer unsigned", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, <Null>, 12, "YES" 12 rows fetched from 18 columns.
[30 Aug 2007 7:24]
Tonci Grgin
DM trace
Attachment: Bug30626trace.zip (application/x-zip-compressed, text), 8.44 KiB.
[30 Aug 2007 18:40]
Colin Stearman
Tonci, Our app works with many other ODBC drivers so I assume (?) it's OK. I have run it with logging with V3.51.11.1 and with V3.51.19. The problems occur when I call SQLGetTypeInfo/SQLFetch looking for type 11 or 93. For type 11 V.11 returns a good result set saying to use type 93, but in V.19 the SQLFetch returns SQL_NO_DATA_FOUND. When either version looks for type 93 directly, the SQLGetTypeInfo returns "SQL data type out of range", meaning presumably it does not like the 93. Here are the snippets from the logs... For V3.51.11.1 . . sample 1eb8-c8c ENTER SQLGetTypeInfo HSTMT 016B20E8 SWORD 11 <SQL_TIMESTAMP> sample 1eb8-c8c EXIT SQLGetTypeInfo with return code 0 (SQL_SUCCESS) HSTMT 016B20E8 SWORD 11 <SQL_TIMESTAMP> sample 1eb8-c8c ENTER SQLFetch HSTMT 016B20E8 sample 1eb8-c8c EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 016B20E8 . . . sample 1eb8-c8c ENTER SQLGetTypeInfo HSTMT 016B20E8 SWORD 93 <SQL_TYPE_TIMESTAMP> sample 1eb8-c8c EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR) HSTMT 016B20E8 SWORD 93 <SQL_TYPE_TIMESTAMP> DIAG [S1004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) ======== V3.51.19 . . sample 1b4c-1a24 ENTER SQLGetTypeInfo HSTMT 016B20E8 SWORD 11 <SQL_TIMESTAMP> sample 1b4c-1a24 EXIT SQLGetTypeInfo with return code 0 (SQL_SUCCESS) HSTMT 016B20E8 SWORD 11 <SQL_TIMESTAMP> sample 1b4c-1a24 ENTER SQLFetch HSTMT 016B20E8 sample 1b4c-1a24 EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND) HSTMT 016B20E8 . . . sample 1b4c-1a24 ENTER SQLGetTypeInfo HSTMT 016B20E8 SWORD 93 <SQL_TYPE_TIMESTAMP> sample 1b4c-1a24 EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR) HSTMT 016B20E8 SWORD 93 <SQL_TYPE_TIMESTAMP> DIAG [S1004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) ============= FWIW, I am running ODBC32.DLL V3.525.1117.0 Thanks, Colin
[3 Sep 2007 13:11]
Tonci Grgin
Colin, I believe you're hitting problem Bogdan described in Bug#9410 and you need fix provided as of MyODBC 3.51.17 (Bug#13766): [20 Jul 14:00] MC Brown A note has been added to the 3.51.17 changelog: Added a new DSN "OPTION" (FLAG_ZERO_DATE_TO_MIN) to retrieve XXXX-00-00 dates as the minimum allowed ODBC date (XXXX-01-01). Added another option (FLAG_MIN_DATE_TO_ZERO) to mirror this but for bound parameters. FLAG_MIN_DATE_TO_ZERO only changes 0000-01-01 to 0000-00-00. Can you please try latest available MyODBC with respect to discussions mentioned above and inform me of result?
[4 Sep 2007 13:45]
Colin Stearman
Tonci, I don't see how Bug#9410 and Bug#13766 are relevant. They are all to do with putting a zero date into a record. I am not attempting to write anything when the problem occurs. Just trying to find out how MySQL supports timestamp types. And in any case, I have testing with 3.51.11 and 3.51.19, so any fix in 3.51.17 would be in .19 presumably. The problem here is SQLGetTypeInfo, which works correctly in .11 when asked for info on SQL_TIMESTAMP (11) and does not in .19. And that both versions fail to return any results when asked about SQL_TYPE_TIMESTAMP (93). I will post the entire log files for my test, one for each version, as that may help. Search on SQLGetTypeInfo, SQL_TYPE_TIMESTAMP and SQL_TIMESTAMP to find the discrepancies. Both were created doing the same operation with our app.] Thanks, Colin
[4 Sep 2007 13:46]
Colin Stearman
SQLGetTypeInfo log for 3.51.11.1
Attachment: mySQL_3.51.11.1.LOG (application/octet-stream, text), 214.45 KiB.
[4 Sep 2007 13:47]
Colin Stearman
SQLGetTypeInfo log for 3.51.19
Attachment: mySQL_3.51.19.LOG (application/octet-stream, text), 312.94 KiB.
[10 Sep 2007 13:04]
Tonci Grgin
Hi Colin and thanks for additional info provided. I get a colleague of mine to take a look at your traces and here's what he has found. The fact is that SQL_TYPE_TIMESTAMP is defined in ODBC 3 and often used by clients presenting themselves as ODBC 2 compliant, like Borland's BDE. Same is in your case. If you look into the logs you'll see setting SQL_ATTR_ODBC_VERSION to 3, however that environment handle is closed soon after its allocation... Next you use another handle without setting ODBC version to 3 (whereas it is obligatory because SQL_TYPE_TIMESTAMP is defined in ODBC 3)! When I connected as ODBC2 client from the standard ODBC client I got the same error: szErrorMsg = "[Microsoft][ODBC Driver Manager] SQL data type out of range". So please set your SQL_ATTR_ODBC_VERSION to 3 and try again.
[10 Sep 2007 15:43]
Colin Stearman
Tonci, Thanks for the information on the second part of the problem, the difficulties with getting type information on SQL_TYPE_TIMESTAMP (93). I understand what you are saying about ODBC Version and will look into it further. As I noted before, Our app works fine with other ODBC drivers. It is not so clear why V.11 returns good information when asked for data on type SQL_TIMESTAMP (11), whereas V.19 (and all other versions I've tried) return no result (SQL_NO_DATA_FOUND). Seems to me this is a problem and was the original one reported here. (See differences between lines 3638 - 3649 in mySQL_3.51.11.1.LOG and lines 4328 - 4341 in mySQL_3.51.19.LOG). SQL_TIMESTAMP is an ODBC 2.0 supported type so should return something for this type, I would think. Thanks for your continued interest in getting to the bottom of this. Colin
[10 Sep 2007 16:25]
Tonci Grgin
Hi Colin. > Thanks for the information on the second part of the problem, the difficulties with getting type information on SQL_TYPE_TIMESTAMP (93). I understand what you are saying about ODBC Version and will look into it further. As I noted before, Our app works fine with other ODBC drivers. I do believe "other" functioning drivers do not pay much attention to what language (API) client speaks. There was also a discussion among us about allowing "dirty" clients to go unpunished (mainly because of BDE), but I'll had to check on status. > It is not so clear why V.11 returns good information when asked for data on type SQL_TIMESTAMP (11), whereas V.19 (and all other versions I've tried) return no result (SQL_NO_DATA_FOUND). Seems to me this is a problem and was the original one reported here. (See differences between lines 3638 - 3649 in mySQL_3.51.11.1.LOG and lines 4328 - 4341 in mySQL_3.51.19.LOG). SQL_TIMESTAMP is an ODBC 2.0 supported type so should return something for this type, I would think. We are contiguously trying to improve MyODBC especially in following ODBC specs, thus the differences. I think, regardless of your problem, the improvement is both visible and significant, don't you agree? > Thanks for your continued interest in getting to the bottom of this. No Colin, thanks goes to you and the community helping us make MySQL better! And, of course, I am monitoring this report as there always is a possibility I am wrong and the ruling of "Can't repeat" should be reverted. So, feel free to reopen the report if new facts come around. Also, you might wish to open feature request (S4) report regarding support for calling ODBC v3 functions from ODBC v2 client. Thanks again for your interest in MySQL. Waiting on your results.
[10 Sep 2007 18:33]
Colin Stearman
Tonci, Saw your notes. I have extensively tested .11 and .19 regarding ODBC version functionality WRT SQLGetTypeInfo and timestamps. I will upload sqlgettypeinfo.pdf which contains my results. Basically, our app was issuing a call to SQLAllocEnv, and the MS documentation clearly states that this will cause the driver to implicitly consider it a V2 ODBC app. With the app like this I asked for information on type 11 and 93 from V.11 and V.19. The results were not the same, as the table shows. I then removed the call to SQLAllocEnv and added a call to SQLAllocHandle to get an environment handle, then set the version to 2 using SQLSetEnvAttr. Then I repeated the tests. Once again V.11 did not do the same thing as V.19. Curiously, though, the results were also different from the first test. Finally I repeated the last test but with the ODBC version set to 3. Now both versions returned the same result (each saying that 93 was the type to use.) So ... I deduce the following: 1. V.11 is OK for ODBC V2, but test method 2 is flaky when asked about type 11. 2. V.19 just doesn't support ODBC v2 at all. 3. Both versions correctly support ODBC V3. From a backward compatibility POV, I would think that the driver SHOULD support ODBC V2, but if MySQL does not support SQL_TIMESTAMP, and has to return SQL_TYPE_TIMESTAMP, which is not known in ODBC V2, I can understand the dilemma. My solution will be to force an ODBC V3 environment using test method 3, and my app will work correctly with MySQL. Hopefully it will not break any other database types! So I think this "bug" can be considered closed. At least the issue will have been documented. Regards, Colin
[10 Sep 2007 18:34]
Colin Stearman
Test Results Table
Attachment: sqlgettypeinfo.pdf (application/pdf, text), 4.21 KiB.
[14 Jun 2008 17:19]
Jess Balint
test to show initial problem
Attachment: bug30626.c (text/plain), 2.59 KiB.
[24 Oct 2008 19:30]
Jess Balint
Fix + test pushed to: https://code.launchpad.net/~myodbc-developers/myodbc/bug30626
[11 Nov 2008 18:25]
Jess Balint
Pushed as rev 811.
[12 Nov 2008 11:29]
Tony Bedford
An entry has been added to the 5.1.4 changelog: No result record was returned for SQLGetTypeInfo for the TIMESTAMP data type. An application would receive the result return code 100 (SQL_NO_DATA_FOUND).
[9 Nov 2009 19:25]
Lawrenty Novitsky
Backported to 3.51 and pushed there as rev#706. will be in 3.51.28 release
[10 Nov 2009 11:27]
Lawrenty Novitsky
(ported to 3.51)
[10 Nov 2009 15:08]
Tony Bedford
Changelog entry updated to include version 3.51.28.
