People have worked around this limitation by using numeric or string literals instead. For instance 1 / 0, Y / N, T / F or the SQL standard 'true' / 'false'.
Booleans in JDBC
From a JDBC API perspective, boolean values can be set as bind values through PreparedStatement.setBoolean() or fetched from result sets through ResultSet.getBoolean(), and similar methods. If your database supports booleans, the Java boolean type nicely maps to SQL BOOLEAN – even if Java’s Boolean wrapper type would have been a better fit to respect NULLs.
But if you’re storing boolean values in INTEGER, CHAR(1) or VARCHAR(1) columns, things look differently in various databases. Consider the following example:
CREATE TABLE booleans (
And then, run this Java program (we’re using jOOQ to keep things concise)
Not all databases / JDBC drivers support the above. These databases will run the above program:
Firebird (inserts ‘Y’ or ‘N’)
HSQLDB (inserts ‘1’ or ‘0’)
IBM DB2 (inserts ‘1’ or ‘0’)
MariaDB (inserts ‘1’ or ‘0’)
Microsoft Access (inserts ‘1’ or ‘0’)
MySQL (inserts ‘1’ or ‘0’)
Oracle (inserts ‘1’ or ‘0’)
SQL Server (inserts ‘1’ or ‘0’)
Sybase (inserts ‘1’ or ‘0’)
… whereas these databases will throw an exception:
Booleans in the SQL standard
It is worth mentioning, that the SQL standard specifies how to deal with boolean to string conversion in the specification of the CAST() function:
6.13 <cast specification>
10) If TD is fixed-length character string,
then let LTD be the length in characters
e) If SD is boolean, then
i) If SV is True and LTD is not less than 4,
then TV is 'TRUE' extended on the right by
ii) If SV is False and LTD is not less than 5,
then TV is 'FALSE' extended on the right by
iii) Otherwise, an exception condition is
raised: data exception — invalid character
value for cast.
So, most Open Source databases show what could be interpreted as the “correct” behaviour, even if from a historic perspective, 1/0 should be accepted behaviours. Beware of this limitation when using an Open Source test database!
For more information about this and the H2 database, please refer to this thread on the H2 user group.