Insert boolean value into sqlite table

Question

I created a table MYTABLE

CREATE TABLE "MYTABLE" (
       "surname" VARCHAR,
       "name" VARCHAR,
       "id" INTEGER PRIMARY KEY  NOT NULL ,
       "flag" BOOL);

when I insert a record with:

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", true);

I get an error message, that no such column: true. If I use this:

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", "true");

I don't get any error, but when i read that record with rs.getBoolean("flag") I get false.

Finally, i tried this

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", 1); 

the rs.getBoolean("flag") returns true. So the lesson here is that the boolean values in Sqlite are inserted with 0/1 ?

Answer 1

SQLite does not have a separate Boolean storage class.Boolean values are stored as integers 0 and 1. source

Answer 2

Yes, the BOOL type is synonymous to a BIT in many databases, including SQLite and SQL Server. Other databases, like Oracle, do not even have a boolean type and a NUMBER(1) field is used to store boolean values by convention.