| Bug #26166 | ADO returns data type values for TinyInt (16,17) instead Boolean (11) | ||
|---|---|---|---|
| Submitted: | 7 Feb 2007 22:31 | Modified: | 8 Feb 2007 13:47 |
| Reporter: | Rick Duke | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ADO Boolean TinyInt | ||
[8 Feb 2007 13:44]
Bogdan Degtyariov
Rick, Thanks for your bug report. However, I must admit that this is expected behavior of MySQL/MyODBC as the definition TINYINT(1) doesn't mean that the appropriate column can take just two values such as 0 or 1 (False or True). MySQL allocates 1 byte for this column. This means that such columns can take the range of -127..127 and 0..255 for signed and unsigned types accordingly. Although I do not consider such work of MyODBC as a bug, it is interesting for me what is the name of the type identifier (16 or 17) returned by MyODBC. I was unable to find any correspondence in Microsoft header files. Accordingly to them SQL_TINYINT is (-6), SQL_C_TINYINT is (-26).
Description: This may not a bug per se, but it was certainly annoying and required code changes on my part to circumvent as I've been trying to support multiple back-end databases against a single app. MySql doesn't have a "true" boolean field type, and uses TinyInt(1) instead. In Sql Server, you pretty much have to do the same. Access just refers to booleans as yes/no fields. The problem is ADO reports back different data type values in MySql vs Sql Server and Access. In mySql the corresponding TinyInt value is returned by ADO as 16 (signed) or 17 (unsigned). In Sql Server and Access the corresponding value returned by ADO is 11. This can break application code that expects and tests for boolean field types. It can be worked around by modifying applicaton code to use boolean conversion wrappers around all field-expression references. In VB6, this would be coded as: cBool(rs.Fields("MyBooleanFieldName").Value) Caveat: TinyInt must be an *Unsigned* field type for this to work. This change makes the ADO fieldtype value 17 instead 16. See my forum thread: http://forums.mysql.com/read.php?37,137224,137224#msg-137224 Rick How to repeat: Create a TinyInt(1) unsigned field type in Mysql. Examine the ado field type value. In Sql Server it will be 11 while in MySql it will be 17. Suggested fix: For consistency, my suggestion is return value 11 indicating boolean field as does Sql Server. Your mileage may vary...