Solving the Boolean Problem of MYSQL in Delphi

I was using Microsoft Access database in a Delphi program and wanted to change over to a MYSQL database using the ADO database components. In fact, I wanted to be able to switch between them interchangeably.

To use the ADO components with MYSQL you just need to install the MYSQL ODBC driver available from the MYSQL website.

Everything works fine except that MYSQL doesn't have any Boolean or True/False field type like Access does. It uses tinyint data type instead, where the value is either 0 or 1. If you use the Navicat program to import your Access database into MYSQL you will get a tinyint data type wherever you had a True/False data type.

So, any code in your program that is reading booleans from the database gets errors. Like this:

s_show := SettingsTable.fieldbyname('show').AsBoolean;

To work around this, you would have to change everything in your program that reads the boolean values and change them to read integer values, and then do something to check the boolean value of the integer.

if SettingsTable.fieldbyname('show').AsInteger=0 then s_show:=false else s_show:=true;

But there are more problems. In grids, those fields are displayed as numbers instead "True" and "False" in the cells. And the same thing for other third party controls - they recognize them as integers instead of booleans.

Then I found a way to cure the problem once and for all. I needed to change two lines in the Delphi runtime library source code.

This example is in Delphi2009.

Open up ADODB.pas and change this one line

into these two lines.

This will cause the tinyint type in MYSQL to be interpreted as boolean value.

Now you can go back to this:

s_show := SettingsTable.fieldbyname('show').AsBoolean;

and it will work. In grids and other controls, the value shows up as "True" "False" just like it did under Microsoft Access.

You have to recompile the Delphi runtime library, which might not be easy, but it can be done.

"["