One of my colleagues pestered me just now asking me why his Tinyint(1) on MySQL was being mangled by ColdFusion so that it only ever returned 1 or 0.
You read the documentation on numeric data type is MySQL and you see "BIT is a synonym for TINYINT(1)" or "BOOLEAN is a synonym for TINYINT(1)", so when ColdFusion returns only 1 or 0 for a TINYINT its kind of understandable.
But then again, no it isn't really.
The description of TinyInt is :
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
so you should be able to insert and extract values from -128 to 127 or 0 to 255 if you create your database field as unsigned.
So why doesn't this work in ColdFusion? Well to be fair its not really ColdFusion at fault here. Its actually down to the JDBC drivers connecting CF to MySQL.
By default the JDBC drivers assume that TINYINT is a bit value and returns data accordingly; 0 is zero and anything else is 1. What you need to do is tell the drivers to treat TINYINT as an integer rather than as a bit.
You can do this with an addition to the connectstring of :
I haven't tried this out on ColdFusion 9, but my colleague had to make use of this Adobe Technote kb403748 to get ColdFusion 8.x to use the connectstring correctly and much heartache, swearing and changing of database tables was averted.