MySQL Bugs: #450: SQLGetTypeInfo reports NO avaliable data types matching SQL

Bug #450	SQLGetTypeInfo reports NO avaliable data types matching SQL_TIMESTAMP
Submitted:	19 May 2003 5:27	Modified:	4 Jun 2003 7:13
Reporter:	Stephen Dakin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (WIN 2000)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
We use ODBC for all of our data access, and have very portable libraries.
We are trying to add support for MySQL to those libraries, on client request.

For portability all of our database creation is thus done through ODBC as well.
We thus have to query existing data structures, and modify as appropriate.

Your driver reports that it cannot map a SQL_TIMESTAMP field, I would have suspected it to return a datetime from your documentation.

How to repeat:
Open a connection 
Open a Statment

Make a call to SQLGetTypeInfo, with SQL_TIMESTAMP as the data type requested.
This looks like it should match your datetime data type.

Call returns SQL_SUCCESS, but a fetch on it returns SQL_NO_DATA_FOUND.

I stopped at this point.

Thus far from our supported data types this is the only one where this problem has arisen, although I still have to test with Blobs! 

Suggested fix:
Fix SQLGetTypeInfo call to return DATETIME type information for SQL_TIMESTAMP data type request.

Hi !!

Connector/ODBC 3.51 does return 2 row resultset when you query SQLGetTypeInfo with SQL_TIMESTAMP and/or SQL_TYPE_TIMESTAMP.

Here is the output for reference from odbctest tool:

SQLGetTypeInfo:
 In    : StatementHandle = 0x007518C0, 
         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, 93, <Null>, <Null>, <Null>
"timestamp", 93, 14, "'", "'", <Null>, 0, 0, 3, 0, 0, 0, "timestamp", 0, 0, 93, <Null>, <Null>, <Null>
2 rows fetched from 19 columns.

SQLGetTypeInfo:
 In    : StatementHandle = 0x007518C0, 
         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, 93, <Null>, <Null>, <Null>
"timestamp", 93, 14, "'", "'", <Null>, 0, 0, 3, 0, 0, 0, "timestamp", 0, 0, 93, <Null>, <Null>, <Null>
2 rows fetched from 19 columns.

Can you please reverify this.
Thanks
Venu

ODBC Trace of the relevent calls:

ModifyDB        75c-5b4	ENTER SQLGetTypeInfo 
		HSTMT               033E26B0
		SWORD                       11 <SQL_TIMESTAMP>

ModifyDB        75c-5b4	EXIT  SQLGetTypeInfo  with return code 0 (SQL_SUCCESS)
		HSTMT               033E26B0
		SWORD                       11 <SQL_TIMESTAMP>

ModifyDB        75c-5b4	ENTER SQLFetch 
		HSTMT               033E26B0

ModifyDB        75c-5b4	EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
		HSTMT               033E26B0

ModifyDB        75c-5b4	ENTER SQLFreeStmt 
		HSTMT               033E26B0
		UWORD                        0 <SQL_CLOSE>

ModifyDB        75c-5b4	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               033E26B0
		UWORD                        0 <SQL_CLOSE>

Stephen:

I still get 2 row result set; even I tried with other ODBC tools and direct application, but still I get expected results as described earlier. Is there anyway for me to reproduce this ?

Thanks

Stephen:

I still get 2 row result set; even I tried with other ODBC tools and direct application, but still I get expected results as described earlier. Is there anyway for me to reproduce this ?

Thanks

Bug only appears with a ODBC 2 environment handle. 2 rows returned with a V3 environment handle.

Environment handle now changed to a 3 one rather than a 2 env, 2 rows returned as expected.