how to get count column

Question

I'd like to get the value of Count column from cursor.

public Cursor getRCount(String iplace) throws SQLException
{
 try {
      String strSql = "SELECT COUNT(_id) AS RCount FROM tbName WHERE place= '" + iplace + "'";            
      return db.rawQuery(strSql, null);
    } catch (SQLException e) {
        Log.e("Exception on query", e.toString());
        return null;
    }
} 

I tried to get this count column value from cursor like below

Cursor cR = mDbHelper.getRCount(cplace);if (cR.getCount() > 0){long lCount = cR.getLong(0);}cR.close();}

I got the debug error. How to get it?

PS: Could I use nested cursors?

Meaty

Are you sure this is .NET? It looks like Java. – Electrophysiology

Answer 1

You should use DatabaseUtils.longForQuery utility method to run the query on the db and return the value in the first column of the first row.

int sometotal=DatabaseUtils.longForQuery(db,"SELECT COUNT(_id) AS RCount FROM tbName WHERE place= '" + iplace + "'",null);

Grindelia

Thanks, Pentium. I add type cast to int. int sometotal=(int)DatabaseUtils.longForQuery(db,"SELECT COUNT(_id) AS RCount FROM tbName WHERE place= '" + iplace + "'",null); – Meaty

Answer 2

The Cursor is positioned before the first row when you get it back from rawQuery(). Call moveToFirst() before attempting to use the Cursor, and your problem should go away.

Puzzle

How does the moveToFirst() method help if the Cursor is already at the beginning of the records? – Bluebird

@AJW: A fresh Cursor is not "already at the beginning of the records". A fresh Cursor has its position before the first row, not on the first row. – Puzzle

Gotcha. Do you know what would be better as far as efficiency for a large database, the rawQuery method with a Cursor or the DatabaseUtils.longForQuery method? – Bluebird

@AJW: I do not know what longForQuery() does. I suggest that you ask a separate Stack Overflow question on that. – Puzzle

Will do, ty. p.s. enjoyed reading your Busy Coder's book 2 years ago when I first started with Java and Android. Cheers. – Bluebird

@AJW: Thanks for the kind words! – Puzzle