Search code examples
mysqlcoldfusioncoldfusion-2016

How to return NULL instead of 0 from MySQL TinyInt(1)

I've moved a site from ColdFusion 10 to ColdFusion 2016, and I've noticed when querying a MySql database, tinyint(1) fields that are null would previously come back to ColdFusion as empty values but now come back as 0.

Anyone know if this is a MySql or CF2016 thing, and how I can get it to return empty values again?

I've googled, but all results seems to be about turning 0 into null. I need it the other way around! CF2018 seems to have changed their null support, but I can't find anything relating to C2016.

Solution

  • For MySQL JDBC drivers, there is a property tinyInt1isBit which is set as true by default (When this is true, tinyint(1) field will be treated as bit field). This feature can be overridden by making a connection string modification by adding tinyInt1isBit=false to it.

    jdbc:mysql://10.0.0.140:3306/testdb?tinyInt1isBit=false

    But when we create a ColdFusion datasource, tinyInt1isBit=false will be added to the connection string by default(from my personal experience). This can be seen in the ColdFusion2016\cfusion\lib\neo-datasource.xml file when we do a local installation.

    I am not sure how hostek is handling getting rid of this from neo-datasource.xml. I believe you'll be creating or editing datasources from the hostek control panel. I don't remember if there is an option to enter connection string when you edit a DSN, but if there is field for that, then you can add tinyInt1isBit=false in there and that should fix your issue.