Functional Spec for Re-enabling the Boolean Datatype

• Overview
• New SQL Syntax
• Legal Casts
• JDBC API
• Limitations

Overview

As part of the Derby 10.2 release, we add the ANSI BOOLEAN datatype. This specification describes what the customer sees.

New SQL Syntax

Customers may create tables with BOOLEAN columns. Customers may also use the boolean literals true, false, and unknown to insert values into BOOLEAN columns. The unknown value means null. Customers may also use these literals in queries anywhere that boolean expressions may occur. Customers may use BOOLEAN columns in all situations which expect linear datatypes, including indexes and sorts; null sorts before false, which sorts before true. So, for instance, the following statements create, populate, and view BOOLEAN columns:

CREATE TABLE sample( boolCol BOOLEAN );

CREATE INDEX boolIndex on sample( boolCol );

INSERT INTO SAMPLE( boolCol ) VALUES
( true ),
( false ),
( null ),
( unknown );

select * from sample order by boolCol;

Legal Casts

A BOOLEAN value may be legally cast to values of the following types:

• SMALLINT
• INTEGER
• BIGINT
• DECIMAL
• REAL
• DOUBLE
• FLOAT
• CHAR
• VARCHAR
• LONG VARCHAR
• CLOB

Casts from these types to BOOLEAN also work.

JDBC API

This JDBC API section discusses the behavior of clients running against 10.2 servers. The following definitions simplify the description of JDBC behavior for BOOLEAN columns and arguments.

• OldFashioned - We call a client/server/vm combination OldFashioned if at least one of the following conditions applies:
  • Server is running on jdk1.3 OR
  • Derby client version is less than 10.2 OR
  • Client is DB2JCC
• NewFangled - We call a client/server/vm combination NewFangled if both of the following conditions apply:
  • Server is running on jdk1.4 or higher AND
  • Client is 10.2 Derby client running on any VM.

DatabaseMetaData

The following table describes how DatabaseMetaData views BOOLEAN columns in tables and BOOLEAN arguments in procedures. That is, the table details the behavior of DatabaseMetaData.getColumns() and DatabaseMetaData.getProcedureColumns(). Note that this table shows the behavior of all four clients: Embedded, DB2JCC, 10.1.1.0, and 10.1.2.0:

ResultSetMetaData

The following table describes how ResultSetMetaData presents information about BOOLEAN columns in 10.2 databases:

JDBC Accessors

Table B-6 in Appendix B of the JDBC 3.0 spec defines the legal JDBC coercions of BOOLEAN columns. Subject to certain limitations, the following ResultSet.getXXX(), CallableStatement.getXXX(), and PreparedStatement.setXXX() methods work on BOOLEAN columns:

The numeric getXXX() methods return 1 for true and 0 for false. The getBoolean() method returns true and false as you expect. The behavior of getString() and getObject() varies as described in the following table:

Limitations

Note that pre-10.2 Derby clients raise an exception if you call setBigDecimal() on a BOOLEAN parameter. This limitation stems from the way pre-10.2 clients treat integer parameters.

Note also that the DB2JCC client does not recognize the java.sql.Types.BOOLEAN datatype. When using DB2JCC to register a procedure's BOOLEAN output parameter, you may use java.sql.Types.SMALLINT instead:

// This fails using DB2JCC:
callableStatement.registerOutParameter( paramNumber, java.sql.Types.BOOLEAN );

// This succeeds:
callableStatement.registerOutParameter( paramNumber, java.sql.Types.SMALLINT );