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

user_3684025 0
Viewed 2222

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.

coldfusion-2016 coldfusion mysql
1 Answers

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.

user_14795350
Related