Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that's what the SQL standard thinks it is, in sorts: <boolean value expression> ::= <predicate> The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can … Continue reading A Condition is a Field
Tag: boolean
How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, column_name, data_type, column_type from information_schema.columns where table_name = 't'; The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL
One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. Before you move on reading this article, note that starting with Oracle 23c, the standard BOOLEAN … Continue reading Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL
jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a … Continue reading jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
Three-State Booleans in Java
Every now and then, I miss SQL's three-valued BOOLEAN semantics in Java. In SQL, we have: TRUE FALSE UNKNOWN (also known as NULL) Every now and then, I find myself in a situation where I wish I could also express this UNKNOWN or UNINITIALISED semantics in Java, when plain true and false aren't enough. Implementing … Continue reading Three-State Booleans in Java
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 … Continue reading The JDBC Boolean Compatibility List
SQL and booleans, some trivia
Some trivia about SQL and booleans: SQL 1992 defines three values for a boolean: <truth value> ::= TRUE | FALSE | UNKNOWN True booleans aren't always supported, though. Here's the truth table of boolean support: SQL DialectSupports booleansDB20 (use 1/0 instead)Derbytrue (you can safely use true/false)H2trueHSQLDBtrueIngrestrueMySQLtrueOracle0PostgrestrueSQL Server0SQLite0Sybase ASE0Sybase SQL Anywhere0 Trivia... but nice to know.