Re: [JDBC] JDBC String to Bool spec

On 2017-01-16 11:10, Mark Rotteveel wrote:
> IIRC, there is a section about conversion to boolean (maybe with CAST,
> or maybe with the parameter specification), I'll try to find it when I
> have access to a copy of the standard.

I have done some research:

JDBC 4.2 says about boolean:

"If the designated column has a datatype of CHAR or VARCHAR and contains
a "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and
contains a 0, a value of false is returned. If the designated column has
a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of
BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of
true is returned." (javadoc ResultSet.getBoolean)

"When the column value in the database is SQL NULL, it may be returned
to the Java
application as null, 0, or false, depending on the type of the column
value.
Column values that map to Java Object types are returned as a Java null;
those
that map to numeric types are returned as 0; those that map to a Java
boolean are
returned as false." (section 15.2.3.3)

SQL:2011 (6.13 <cast specification>:

A table in the syntax rules shows that cast only allows conversion from
character types to boolean (and boolean to boolean) and boolean to
character types, and - if supported - to user defined types and
reference types (following rules I haven't looked at).

"[if TD is fixed length character string]:
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 <space>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."

"[if TD is variable length character string]:
e) If SD is boolean, then
Case:
i) If SV is True and MLTD is not less than 4, then TV is 'TRUE'.
ii) If SV is False and MLTD is not less than 5, then TV is 'FALSE'.
iii) Otherwise, an exception condition is raised: data exception —
invalid character value for cast."

"20) If TD is boolean, then
Case:
a) If SD is character string, then SV is replaced by
TRIM ( BOTH ' ' FROM VE )
Case:
i) If the rules for <literal> in Subclause 5.3, “<literal>”, can be
applied to SV to determine a valid
value of the data type TD, then let TV be that value.
ii) Otherwise, an exception condition is raised: data exception —
invalid character value for cast.
b) If SD is boolean, then TV is SV."

TD: Target dataype, SD: Source dataype, VE: value expression of the cast
operand, SV: source value, is the value of VE, TV: target value, (M)LTD:
(max) length of TD

Valid values according to 5.3:
<boolean literal> ::=
TRUE
| FALSE
| UNKNOWN

In other words, JDBC allows a bit wider definition, probably to address
compatibility with database systems that do not support boolean data
types.

Mark