Getting values out of MySQL TinyInt(1)

Posted At : January 25, 2011 11:52 AM | Posted By : Stephen Moretti | 2870 Views
Related Categories: Adobe , CFML , MySQL

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 :

view plain print about
1tinyInt1isBit=false

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.

Comments Comments (7) | Print Print | Send Send | Bookmark and Share
TweetBacks
Comments
[Add Comment] [Subscribe to Comments]
To be slightly pedantic, the advice came too late to avert much of the swearing - however the heartache and changing of tables were indeed avoided, for which I am extremely grateful.
# Posted By Andy Lewis | 1/25/11 1:37 PM
Stephen, I do use tinyint(1) for bit fields, but also have had no problem using tinyint for positive integer values up to 255 without adding any connection strings. I just make sure the field is unsigned with a length of 3. This has worked for me on all versions of CF since 6 when I started using MySQL.
# Posted By Julian Halliwell | 1/27/11 1:54 PM
@Julian, so surely that would be "tinyint(3) unsigned" then not "tinyint(1)"? Which would kind of defeat the object.
# Posted By Stephen Moretti | 1/27/11 2:53 PM
@Stephen, I guess I'm misunderstanding the object then, sorry. I thought you were wanting to store and return integers other than 1 or 0 in your tinyint fields.

Is your point then that an unsigned tinyint(1) should be able to store positive integers up to 9, but the JDBC drivers will only allow 0 or 1?
# Posted By Julian Halliwell | 1/27/11 3:03 PM
@Julien, that is exactly right. TinyInt(1) does hold any number in the ranges I mention above, but the display attribute of (1) says that only 1 digit will be returned/displayed.

However, by default the JDBC drivers assume that TinyInt(1) is a BIT/BOOL rather than an integer of display length 1. To over come this you need the connection string.
# Posted By Stephen Moretti | 1/27/11 3:50 PM
Understood, Stephen. I always assumed tinyint(1) would only allow a single digit number, but you are quite right an unsigned field would allow values up to 255. Apologies.

I guess I also didn't pick up that in your case the DB presumably couldn't be altered, in which case the connection string is the perfect solution.

Thanks, all very useful information.
# Posted By Julian Halliwell | 1/27/11 3:58 PM
Thank you for this little bit of information. It was a life-saver.
# Posted By Anuraag Godika | 9/2/11 3:46 PM
[Add Comment] [Subscribe to Comments]