CODEWITHSUNDEEP
androidandroid-sqlite
Tal Kanel
Tal Kanel

Reputation: 10785

SQLiteStatement.simpleQueryForLong() is behaving differently compared to what the documentation says

I'm using the simpleQueryForLong() method in the following way:

mResuableStatment = DatabaseHandler.database.compileStatement(
        "SELECT MIN(timestamp) FROM " + TABLE_LOCAITON_LOGGING);

long oldestTimestamp = mResuableStatment.simpleQueryForLong();

according to the documentation - if there are no rows at all, then this code suppose to throw SQLiteDoneException, but what really happening is that when there are no rows - the simpleQueryForLong() returns zero (without throwing excpetion)

What was even more strange and unexpected, when I actually wrap this code in try/catch block, it will throw SQLiteDoneException every time, even when there are rows in the table:

try {
    long oldestTimestamp = mResuableStatment.simpleQueryForLong();
} catch (SQLiteDoneException e) {
    e.printStackTrace()
}

This behavior was seen on Nexus 5, Galaxy Nexus, LG G2, HTC One X, all running Android KitKat.

I'm a little confused because this forced me not to use the try/catch block, since if it's used - the exception is always thrown regardless if there are rows or not, and vice versa to what the documentation says.

Seems like in every device I tested - without the try/catch block, when there are no columns - it returns zero.

Please help me to understand what is the right way, and what I'm doing wrong, or if simply the documentation is wrong.

Upvotes: 1

Views: 660

Answers (1)

paxdiablo
paxdiablo

Reputation: 882336

Actually, I think you'll find you have it the wrong way around.

select min(something) ...

will never return zero rows. It will either return the minimum value (if the table has rows) or null if the table has no rows. If your query was just select something when the table was empty, that could return zero rows so that might be worth testing.

I've tested this with SqlFiddle, entering the commands:

create table xyzzy(a int);
select a from xyzzy;
select min(a) from xyzzy;

The first select returns zero rows, the second returns one row with a (null) value.

So I don't believe it's the empty table causing the exception. It may be the attempted conversion of (null) into a long.

How you "fix" this depends entirely on what you want to use the data for. Relying on a null row being converted to zero may or may not be what you want. For example, if there was a zero timestamp in the table, you would not be able to tell the difference between that and an empty table.

That may be acceptable, it depends on your business requirements.

If you need to distinguish, I would run two queries. The first would return count(*) from the table. That's guaranteed to be a single row containing a numeric value (no nulls). If that's zero, it means the table is empty.

If non-zero, then do the min(column_name) and you'll get a single row with the minimum timestamp. If you get zero at that point, you know it's because the minimum timestamp was zero. Unless of course there's a NULL timestamp in there in which case you may have to do more checks. But I think that unlikely if you've structured your schema correctly.

Note that I wouldn't normally suggest this in a multi-user database since it may lead to race conditions. But I think it's probably okay since there should only be one "user" of this database, your application.

Upvotes: 2

Related Questions