Sqlite API boolean access

Question

This should be an easy question I figure, but I hadn't found this answered else where surprisingly, so I'm posting it here.

I've inherited a Sqlite driven database which contains boolean columns in it, declared like this:

CREATE TABLE example (
  ex_col BOOLEAN NOT NULL DEFAULT 0,
);

This table is trying to be accessed via the sqlite3 C API calls sqlite_column_* functions, now given that sqlite doesn't actually support boolean types, what is the expected behavior here?

It appears sqlite_column_int() always return 0 or false, I assume this is because all columns in sqlite are really text columns...

And what is the proper way to maintain this - fetching as text and then string compare to true? I really don't want to modify the database and all of the other code attached to it.

Answer 1

One obvious way would be to "declare" it as integer column and then when you do INSERT or UPDATE you pass it 1 (True) or 0 (False). This way, you maintain compatibility with the C language. You don't even need to declare it as int, just make sure you always insert integers to it and you'll be fine.

You mentioned this is an inherited database, how did they do before? If they stored as text then you may need to call sqlite_column_text() and then string match for the "true" or "false" literal strings.

Answer 2

I have a data point that might give you a clue.

The SQLite Administrator tool does convert booleans to the strings "true" and "false".