How to retrieve an int value rather than a boolean from a TINYINT(1) column?

Question

I have a MySQL column specified as:

`type` TINYINT(1) NOT NULL DEFAULT '0'

The intent of the column is to store an integer value not to exceed 127 as there are not expected to be more than a very few object "types."

I've stored '2' in the field for one of my rows.

Using SqlYog, a simple SELECT type FROM table yields the proper result, 2.

However, using the Connector/Net 6.1.2 (admittedly it's a bit out of date, as the current version is 6.5.4), the following happens:

var Temp = Reader["type"].GetType(); // equals "Boolean"

This type of column is usually used for Boolean values, but in this case I want to get the integer value. The following fails to yield the expected result:

int i = Reader.GetInt32("type"); // equals 1 (should be 2)

What's the proper way to get int values from a TINYINT(1) column using Connector/Net in a .NET app?

This is using MySQL version 5.5.16

Answer 1

Put that in your connection string to use tinyint as number

TreatTinyAsBoolean=false

Answer 2

The answer for the Connector's confusion may be this part from MySQL docs; Numeric Types:

As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) ...

or (even more probable) this part from Numeric Type Overview:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true ...

Answer 3

One solution I've found is to simply change the column type to UNSIGNED:

`type` tinyint(1) unsigned NOT NULL DEFAULT '0'

This has the following effect:

var Temp = Reader["type"].GetType(); // equals "Byte"

Which does retrieve the proper result (2) upon calling .GetInt32().

However if for some reason you wanted to store values from -127 to 127 (using it as a signed TINYINT) this solution doesn't work.

---

Another solution is to change the column type to TINYINT(2):

`type` tinyint(2) NOT NULL DEFAULT '0'

This gives:

var Temp = Reader["type"].GetType(); // equals "SByte"

Also yielding the proper result (2).

However this brings into question the purpose of the number after TINYINT (i.e. TINYINT(#)). Isn't it for "optionally specifying the display width"? Maybe someone can shed more light on this point.