The BOOLEAN Data Type

Go Up to Specifying Data Types

The BOOLEAN data type is a 16-bit data type that represents TRUE and FALSE values in a column. When not prohibited by a NOT NULL constraint, it also supports the UNKNOWN truth value.

For ESQL and DSQL, the following types are defined in ibase.h:

#define SQL_BOOLEAN 590

SQL Data Type	Macro expression	C data type or typedef	sqlind used?
BOOLEAN	SQL_BOOLEAN	Signed short	NO
BOOLEAN	SQL_BOOELAN + 1	Signed short	YES

In ISQL and IBConsole, the output for a BOOLEAN, regardless of values given, is always TRUE, FALSE or UNKNOWN. However, using API function calls, UNKNOWN is treated as NULL, TRUE returns 1, and FALSE returns 0.

Note: InterBase looks for Booleans of the form “literal <relop> literal” that evaluate to FALSE and returns a false Boolean inversion node to short-circuit data retrieval.

Examples: The following code illustrates the use of the BOOLEAN data type.

• SQL statements:

CREATE TABLE AWARDS_1 (isEligible BOOLEAN, name VARCHAR(20));
INSERT INTO AWARDS_1 VALUES (TRUE, 'Jim Smith');
INSERT INTO AWARDS_1 VALUES (FALSE, 'John Buttler');
SELECT * FROM AWARDS_1;

Result:

ISELIGIBLE NAME
========== ============
TRUE       Jim Smith
FALSE      John Buttler

• SQL statement:

SELECT * FROM AWARDS_1 WHERE isEligible = TRUE; 

Result:

ISELIGIBLE NAME
========== =========
TRUE       Jim Smith

• SQL statement:

SELECT * FROM AWARDS_1 WHERE isEligible;

Result:

ISELIGIBLE NAME
========== =========
TRUE       Jim Smith

• SQL statement:

SELECT * FROM AWARDS_1 WHERE NOT isEligible;

Result:

ISELIGIBLE NAME
========== ============
FALSE      John Buttler