Return the count of records in a variable, Sqlite, Android, Java

Question

My ultimate goal is to limit records that are able to be created so that I can have a trial version of my application. I'm thinking I can do this rather simply by returning an int variable within a sqlite count statement, and using a simple IF statement to determine if a new record should be created.

I call like so:

int jcount = 0;
            mDbHelper.countjournals(jcount);

Right now i'm trying to execute this

 public int countjournals(int jcount){
             mDb.execSQL(jcount + " = SELECT COUNT(*) FROM "+DATABASE_JOURNAL_TABLE);
            return jcount;
        }

error that i recieve:

08-27 22:42:32.417: ERROR/AndroidRuntime(3976): android.database.sqlite.SQLiteException: near "0": syntax error: 0 = SELECT COUNT(*) FROM journals

Answer 1

Your query is incorrect, better way to do what you need is:

    public int countjournals() {
        Cursor dataCount = mDb.rawQuery("select count(*) from" + DATABASE_JOURNAL_TABLE, null);
        dataCount.moveToFirst();
        int jcount = dataCount.getInt(0);
        dataCount.close();
        return jcount;
    }

Side note: Also note that you can't use primitive variables as references (and from you code it looks like you're trying to do so), also you can't pass in references to SQLite queries. Instead you just need to assign method (query) result to your variable.

Answer 2

public int countjournals() {
    SQLiteStatement dbJournalCountQuery;
    dbJournalCountQuery = mDb.compileStatement("select count(*) from" + DATABASE_JOURNAL_TABLE);
    return (int) dbJournalCountQuery.simpleQueryForLong();
}

Answer 3

Both the answers provided seemed to me like they should work, but i couldn't get them to. I've found a third solution that is working form me.

  public long countjournals() {

            return DatabaseUtils.queryNumEntries(mDb,DATABASE_JOURNAL_TABLE);

        }