SQLIte DAC: Error value in Boolean field

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
VadimShvarts
Posts: 34
Joined: Mon 22 Dec 2008 09:03

SQLIte DAC: Error value in Boolean field

Post by VadimShvarts » Wed 13 Feb 2013 10:41

I download "LiteDAC Demo" and open database "TestBoolean.db3":

Code: Select all

BEGIN TRANSACTION;
CREATE TABLE Test (Value Boolean, Name VarChar(20));
INSERT INTO Test VALUES('t','True value');
INSERT INTO Test VALUES('f','False value');
COMMIT;
Table "Test" has two records:
In first "Value" must be "True"
In second "Value" must be "False"

After execute query "SELECT * FROM Test" I see that both record have "Value=False"

This error is also in the "UniDAC"
Small sample I send to AlexP devart.com

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQLIte DAC: Error value in Boolean field

Post by AlexP » Wed 13 Feb 2013 14:13

Hello,

There is no Boolean type in SQLite, boolean values are stored as 0 and 1. There are the specifications of this type from the SQLite help below ( http://www.sqlite.org/datatype3.html , http://www.sqlite.org/lang_expr.html ):
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
To convert the results of an SQL expression to a boolean value, SQLite first casts the result to a NUMERIC value in the same way as a CAST expression. A NULL or zero value (integer value 0 or real value 0.0) is considered to be false. All other values are considered true.

For example, the values NULL, 0.0, 0, 'english' and '0' are all considered to be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.
I.e.: to insert boolean values, you can use the following queries:

Code: Select all

INSERT INTO Test VALUES(1,'True value');
INSERT INTO Test VALUES(0,'False value');

Post Reply