Description:
the odbc column descriptors look broken.
get a test odbc and see it is very different and looks broken
also the boolean is returned not as 1 and 0 but as a non english char and null
Full Connect(Default)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'LOCALHOST TEST'.
SQLExecDirect:
In: hstmt = 0x009C3008, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Descriptors All:
Descriptor: 0x009C30E4 (hdbc 1, hstmt 1)"Implicit ARD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_SIZE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_BIND_OFFSET_PTR: SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
Descriptor: 0x009C3174 (hdbc 1, hstmt 1)"Implicit APD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_SIZE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_BIND_OFFSET_PTR: SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
Descriptor: 0x009C3204 (hdbc 1, hstmt 1)"Implicit IRD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_ROWS_PROCESSED_PTR:
Descriptor: 0x009C3294 (hdbc 1, hstmt 1)"Implicit IPD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_COUNT:0
How to repeat:
create an access database with a simple table
an auto increment column "I" and yes/no column "B"
call it a_bit
do the same in my sql call it my_bit
create a system odbc connection in odbc settings in control panel one for access database and one for mysql database
get microsoft odbc test(unicode) and play with it
you can download odbc test from:
http://www.datadirect.com/support/downloads/tools/index.ssp
1 do from menu : Conn> full connect , choose the odbc connection name of access
2 you will have a window separated in two horizontal windows
in the top text write select * from a_bit and click on the exclamation mark toolbar button
3 from Result menu choose Get all descriptor
do teh same for mysql with the correct query
"select * from my_bit"
and compare the descriptors
----------------------------mysql result----------------------------
Full Connect(Default)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'LOCALHOST TEST'.
SQLExecDirect:
In: hstmt = 0x009C3008, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Descriptors All:
Descriptor: 0x009C30E4 (hdbc 1, hstmt 1)"Implicit ARD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_SIZE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_BIND_OFFSET_PTR: SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
Descriptor: 0x009C3174 (hdbc 1, hstmt 1)"Implicit APD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_SIZE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_BIND_OFFSET_PTR: SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
Descriptor: 0x009C3204 (hdbc 1, hstmt 1)"Implicit IRD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_ROWS_PROCESSED_PTR:
Descriptor: 0x009C3294 (hdbc 1, hstmt 1)"Implicit IPD"
Header:
SQL_DESC_ALLOC_TYPE:0
SQL_DESC_ARRAY_STATUS_PTR: SQL_DESC_COUNT:0
SQL_DESC_ROWS_PROCESSED_PTR:
Bind Col All:
icol, fCType, cbValueMax, pcbValue, rgbValue
1, SQL_C_SLONG=-16, 4, 0, 0
2, SQL_C_BIT=-7, 1, 0, 0x00
SQLFetchScroll:
In: hstmt = 0x009C3008, fFetchOrientation = SQL_FETCH_NEXT=1, fFetchOffset = 1
Return: SQL_SUCCESS=0
SQLFreeStmt:
In: hstmt = 0x009C3008, fOption = SQL_CLOSE=0
Return: SQL_SUCCESS=0
Get Data All:
SQLNumResultCols returned: SQL_ERROR=-1
: szSqlState = "HY010", *pfNativeError = 0, *pcbErrorMsg = 56
szErrorMsg = "[Microsoft][ODBC Driver Manager] Function sequence error"
SQLExecDirect:
In: hstmt = 0x009C3008, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
TST1005: Highest bound column is 2.
"I", "B"
1,
2,
3,
4,
4, <Null>
5 rows fetched from 2 columns.
--------------------------------------------------------------------
-------------------------------- access result ---------------------
Full Connect(Default)
Successfully connected to DSN 'ACTEST'.
SQLExecDirect:
In: hstmt = 0x032B0870, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Descriptors All:
Descriptor: 0x032B094C (hdbc 2, hstmt 2)"Implicit ARD"
Header:
SQL_DESC_ALLOC_TYPE:1
SQL_DESC_ARRAY_SIZE:1
SQL_DESC_ARRAY_STATUS_PTR:0x00000000
SQL_DESC_BIND_OFFSET_PTR:0x00000000
SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
No Descriptor Record Fields Available
Descriptor: 0x032B09DC (hdbc 2, hstmt 2)"Implicit APD"
Header:
SQL_DESC_ALLOC_TYPE:1
SQL_DESC_ARRAY_SIZE:1
SQL_DESC_ARRAY_STATUS_PTR:0x00000000
SQL_DESC_BIND_OFFSET_PTR:0x00000000
SQL_DESC_BIND_TYPE:0
SQL_DESC_COUNT:0
No Descriptor Record Fields Available
Descriptor: 0x032B0A6C (hdbc 2, hstmt 2)"Implicit IRD"
Header:
SQL_DESC_ALLOC_TYPE:1
SQL_DESC_ARRAY_STATUS_PTR:0x00000000
SQL_DESC_ROWS_PROCESSED_PTR:0x00000000
Record: #1
SQL_DESC_AUTO_UNIQUE_VALUE:1
SQL_DESC_BASE_COLUMN_NAME:"I"
SQL_DESC_BASE_TABLE_NAME:"I"
SQL_DESC_CASE_SENSITIVE:0
SQL_DESC_CATALOG_NAME:""
SQL_DESC_CONCISE_TYPE:4
SQL_DESC_DATETIME_INTERVAL_CODE:0
SQL_DESC_DATETIME_INTERVAL_PRECISION:0
SQL_DESC_DISPLAY_SIZE:11
SQL_DESC_FIXED_PREC_SCALE:0
SQL_DESC_LABEL:"I"
SQL_DESC_LENGTH:0
SQL_DESC_LITERAL_PREFIX:""
SQL_DESC_LITERAL_SUFFIX:""
SQL_DESC_LOCAL_TYPE_NAME:""
SQL_DESC_NAME:"I"
SQL_DESC_NULLABLE:0
SQL_DESC_OCTET_LENGTH:4
SQL_DESC_PRECISION:10
SQL_DESC_SCALE:0
SQL_DESC_SCHEMA_NAME:""
SQL_DESC_SEARCHABLE:2
SQL_DESC_TABLE_NAME:"a_bit"
SQL_DESC_TYPE:4
SQL_DESC_TYPE_NAME:"COUNTER"
SQL_DESC_UNNAMED:0
SQL_DESC_UNSIGNED:0
SQL_DESC_UPDATABLE:0
Record: #2
SQL_DESC_AUTO_UNIQUE_VALUE:0
SQL_DESC_BASE_COLUMN_NAME:"B"
SQL_DESC_BASE_TABLE_NAME:"B"
SQL_DESC_CASE_SENSITIVE:0
SQL_DESC_CATALOG_NAME:""
SQL_DESC_CONCISE_TYPE:-7
SQL_DESC_DATETIME_INTERVAL_CODE:0
SQL_DESC_DATETIME_INTERVAL_PRECISION:0
SQL_DESC_DISPLAY_SIZE:1
SQL_DESC_FIXED_PREC_SCALE:0
SQL_DESC_LABEL:"B"
SQL_DESC_LENGTH:1
SQL_DESC_LITERAL_PREFIX:""
SQL_DESC_LITERAL_SUFFIX:""
SQL_DESC_LOCAL_TYPE_NAME:""
SQL_DESC_NAME:"B"
SQL_DESC_NULLABLE:0
SQL_DESC_OCTET_LENGTH:1
SQL_DESC_PRECISION:0
SQL_DESC_SCALE:0
SQL_DESC_SCHEMA_NAME:""
SQL_DESC_SEARCHABLE:2
SQL_DESC_TABLE_NAME:"a_bit"
SQL_DESC_TYPE:-7
SQL_DESC_TYPE_NAME:"BIT"
SQL_DESC_UNNAMED:0
SQL_DESC_UNSIGNED:1
SQL_DESC_UPDATABLE:1
Descriptor: 0x032B0AFC (hdbc 2, hstmt 2)"Implicit IPD"
Header:
SQL_DESC_ALLOC_TYPE:1
SQL_DESC_ARRAY_STATUS_PTR:0x00000000
SQL_DESC_COUNT:0
SQL_DESC_ROWS_PROCESSED_PTR:0x00000000
No Descriptor Record Fields Available
SQLExecDirect:
In: hstmt = 0x032B0870, szSqlStr = "", cbSqlStr = -3
Return: SQL_ERROR=-1
Get Data All:
"I", "B"
1, 1
2, 0
3, 1
4, 1
4 row fetched from 2 columns.
--------------------------------------------------------------------
look how a result from access looks good
it wold be good if mysql's result will look the same good.
Suggested fix:
debug mysql odbc driver and make the results look the same.
maybe the sql c bit type and result and the descriptor are returned from access as ascii text.
and maybe mysql also should return ascii and not binary.
this trade-off is ok today the computers are fast and have a lot of memory.
the compatibility is more important.
