Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here’s “Ask Tom”‘s point of view from 2002 on the subject: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595
Oracle. Why U No Boolean?
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 ( val char(1) );
And then, run this Java program (we’re using jOOQ to keep things concise)
try {
DSL.using(configuration)
.execute(
"insert into boolean (val) values (?)", true);
}
catch (Exception e) {
e.printStackTrace();
}
DSL.using(configuration)
.fetch("select * from booleans");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:
- CUBRID
- Derby
- H2
- Ingres
- PostgreSQL
- SQLite
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 of TD. [...] e) If SD is boolean, then Case: i) If SV is True and LTD is not less than 4, then TV is 'TRUE' extended on the right by LTD–4 s. ii) If SV is False and LTD is not less than 5, then TV is 'FALSE' extended on the right by LTD–5 <space>s. 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.
