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

i think you are going to need to provide additional information to get an acceptable answer — Aaron Saunders
– Aaron Saunders, Commented Aug 28, 2010 at 6:15
I chose my answer because it was the one that worked for me. Although it looks, to me at least, that they should all work. — Brian
– Brian, Commented Aug 30, 2010 at 15:38

Brian · Accepted Answer · 2010-08-28 05:41:52Z

14

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);

        }

answered Aug 28, 2010 at 5:41

Brian

6093 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

orip Over a year ago

Very awesome, I missed class DatabaseUtils

Aaron Saunders · Accepted Answer · 2010-08-28 04:40:33Z

6

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

edited Aug 28, 2010 at 4:40

answered Aug 28, 2010 at 4:22

Aaron Saunders

33.4k5 gold badges64 silver badges83 bronze badges

2 Comments

Brian Over a year ago

what should dbJournalCountQuery be defined as?

Brian Over a year ago

This seems to work as well. But I'm still only getting 1 as the result when there are 4

Konstantin Burov · Accepted Answer · 2010-08-28 03:34:52Z

3

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.

answered Aug 28, 2010 at 3:34

Konstantin Burov

69.3k16 gold badges117 silver badges93 bronze badges

4 Comments

Brian Over a year ago

no errors, but only gives me a count of 1 when there are more.

Konstantin Burov Over a year ago

That can be for two reasons: 1 - there is really only one record, 2 - probably there are more, but only 1 was already committed to the db. The code snippet is almost 100% copy of my production, so I'm totally sure it is correct.

Brian Over a year ago

To me it looks like it should work. What do you mean by already committed?

Brian Over a year ago

Even if there are no records I am still getting a value of 1

Tofeeq Ahmad · Accepted Answer · 2012-05-21 06:00:16Z

1

Just insert a SPACE after from an it start working.....

Cursor dataCount = mDb.rawQuery("select count(*) from " + DATABASE_JOURNAL_TABLE, null);

edited May 21, 2012 at 6:00

Tofeeq Ahmad

12k4 gold badges63 silver badges89 bronze badges

answered Apr 5, 2011 at 18:01

Skomo

111 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related