The JDBC Boolean Compatibility List


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? tweet this

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.

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,838 other followers

%d bloggers like this: