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

You don't need to use any comparison operator in order to compare a boolean value in your where clause.

If your 'boolean' column is named is_selectable, your where clause would simply be: WHERE is_selectable