How do I use a boolean field in a where clause in SQLite?

Question

It seems like a dumb question, and yet. It could be my IDE that's goofing me up. Here's the code (this is generated from DbLinq):

SELECT  pics$.Caption, pics$.Id, pics$.Path, pics$.Public, pics$.Active, portpics$.PortfolioID
FROM main.Pictures pics$
inner join main.PortfolioPictures portpics$    on  pics$.Id = portpics$.PictureId

WHERE   portpics$.PortfolioId = 1 AND pics$.Id > 0
--AND pics$.Active = 1 AND pics$.Public = 1
ORDER BY pics$.Id

If I run this query I get three rows back, with two boolean fields called Active and Public. Adding in the commented out line returns no rows. Changing the line to any of the following:

pics$.Active = 'TRUE'
pics$.Active = 't' 
pics$.Active =  boolean(1)

It doesn't work. Either errors or no results. I've googled for this and found a dearth of actual SQL queries out there. And here we are.

So: how do I use a boolean field in a where clause in SQLite?

IDE is SQLite Administrator.

Update: Well, I found the answer. SQLite Administrator will let you make up your own types apparently; the create SQL that gets generated looks like this:

CREATE TABLE [Pictures] ([Id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Path] VARCHAR(50)  UNIQUE NOT NULL,[Caption] varchAR(50)  NULL,
[Public] BOOLEAN DEFAULT '0' NOT NULL,[Active] BOOLEAN DEFAULT '1' NOT NULL)

The fix for the query is

AND pics$.Active = 'Y' AND pics$.Public = 'Y'

The real issue here is, as the first answerer pointed out, there is no boolean type in SQLite. Not an issue, but something to be aware of. I'm using DbLinq to generate my data layer; maybe it shouldn't allow mapping of types that SQLite doesn't support. Or it should map all types that aren't native to SQLite to a string type.

Answer 1

SQLite does not have the boolean type: What datatypes does SQLite support?

The commented-out line as it is should work, just use integer values of 1 and 0 in your data to represent a boolean.

Answer 2

SQLite has no built-in boolean type - you have to use an integer instead. Also, when you're comparing the value to 'TRUE' and 't', you're comparing it to those values as strings, not as booleans or integers, and therefore the comparison will always fail.

Source: http://www.sqlite.org/datatype3.html