Page 1 of 1

SQLIte DAC: Error value in Boolean field

Posted: Wed 13 Feb 2013 10:41
by VadimShvarts
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

Re: SQLIte DAC: Error value in Boolean field

Posted: Wed 13 Feb 2013 14:13
by AlexP
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');