How to get all results where column is larger then zero in sqlite?

I have created a table in SQLite Chrome (html5):

transaction.executeSql(
            'CREATE TABLE IF NOT EXISTS record('+
            'id INTEGER NOT NULL PRIMARY KEY,'+
            'fkindicatorspeciesid INTEGER NOT NULL REFERENCES indicatorspecies(indicatorspeciesid),'+
            'latitude INTEGER NOT NULL,'+
            'longitude INTEGER NOT NULL,'+
            'time TEXT NOT NULL,'+
            '`when` TEXT NOT NULL,'+
            'numberseen INTEGER NOT NULL,'+
            'notes TEXT NOT NULL,'+
            'online_recordid INTEGER,'+
            'status TEXT,'+
            'locationid INTEGER REFERENCES location(locationid),'+
            'surveyid INTEGER REFERENCES survey(id));',
            [], nullHandler,errorHandler);

Then I add some da开发者_JAVA技巧ta to it:

Now I try to retrieve all the records where 'online_recordid' is larger then zero:

select * from record where online_recordid > 0;

I should not get my record back because there is nothing in the column 'online_recordid'. The problem is that I do get the record back!

---

SQLite uses a more general dynamic type system.

You can validly store '' (empty string) into online_recordid, EVEN IF it is defined as "INTEGER". That affects only Type Affinity and does not restrict the data in any way unlike a traditional RDBMS.

What has most likely happened is that your code is inserting '' into online_recordid when what you really wanted to store was NULL.

When the query online_recordid > 0 fires, the blank is converted into [something] that makes it >0.

---

Just check to see that online_recordid is not null:

select * from record where online_recordid > 0 and online_recordid is not null

However, the behavior is strange. I tried doing the same query as you, but I get no records back. Maybe I did something different than you.

---

This is how I eventually solved it.

select * from record where online_recordid <> '' and online_recordid > 0;