Getting values out of MySQL TinyInt(1)

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.

[More]

MySQL tables for CFMX Client Variable Storage

I just realised that I'd forgotten to move the client variables store from registry to a database. Then I realised I'd have to manually create the tables as well, because CFMX admin can't do it for you with a MySQL "other" datasource.

Fortunately I have access to a MySQL database where I've done this previously, so it was pretty easy to cut and paste the SQL for creating the table, but I thought I would just drop the SQL in here, so that its available for me the next time I need it or if anyone else looks for it.

Connecting to MySQL 4.1 using Connector J version 3.1.12

For quite some time now I've been using MySQL (http://www.mysql.com/) as my database of choice for building web applications on top of. Why? Well the server software and the GUIs are free. Its a million times better than using MS Access. Its stable (for the most part) and generally accepted in the web development community as a good solution.

Until recently though I've been having problems using the latest MySQL J Connector with CFMX. I would always get a "Connection Failed" error. This has meant that I've been stuck with the 3.1.10 J Connector, which while they work are less than ideal.

Well recently I found out how to get the latest J Connector (3.1.12 at time of 'print') to allow connections!

If you try connecting using the latest connector with a JRun install of CF, then you get a completely different error regarding a Log4 error. A friend of mine Tim Blair had had this problem and found solution to this, so I thought I'd give his solution a try on my bog-standard Professional Server installation of CFMX 7.

It turns out that there isn't actually a connection problem at all! The actual problem is some kind of clash between CFMX/Jrun and the logger in the newer connector. Its the same problem that Tim was encountering, just not giving sufficient or correct information in the error!

So whats the solution you ask? Simple, the solution is to add "logger=com.mysql.jdbc.log.StandardLogger" to the end of the connection string. This tells the J Connector to use the standard logger rather than the Log4 log class.

For example :

jdbc:mysql://localhost:3306/mydatabase?logger=com.mysql.jdbc.log.StandardLogger

Other useful additions to the connection string are useUnicode=true and characterEncoding=UTF8 for internationalisation, giving you :

jdbc:mysql://localhost:3306/mydatabase?logger=com.mysql.jdbc.log.StandardLogger&useUnicode=true&characterEncoding=UTF8

One thing to note : Don't use the connection string box in the datasource form as this doesn't work for "other" type datasources.