Exception when accessing cursor

My application has users entering records into a database. The records contain an auto-incrementing id, date (as text in mm-dd-yyyy format), a quantity (int) and a few other fields.

I'm trying to grab all the months and the sum quantity for each month. So that I can display them in my app like: Dec 2010 - 90qty, Jan 2011 - 45qty...

I'm using this query to do that:

return mDb.query("record", new String[] {KEY_ROWID, "strftime('%m-%Y', date)", "sum(quantity)"}, null, null, "strftime('%m-%Y', date)", null, null);

I believe the query is good but I get an exception when accessing the cursor. It says 'date' is not a column. If I rename "date" in from[] to "dateA", the exception reads, 'dateA' is not a column, so I believe that narrows the issue.

Cursor c = mDbHelper.fetchReport();
    String[] from = new String[] { "date", "quantity" };
        int[] to = new int[] { R.id.text1, R.id.text2 };

        setListAdapter(new SimpleCursorAdapter(this, R.layout.basicrow, c, from, to) {
}

Any ideas?

Update: 开发者_运维百科When performing select strftime('%m-%Y', date), sum(quantity) from record; in adb shell, it returns ":94" ... the quantity is there (for all dates however) but date is not.

When I enter, select strftime(date) from record, it returns all the dates (3 in this test case). When I enter, select strftime('%m-%Y', date) from record, it returns 3 blank rows.

---

I believe the following happens. Here is your query (simplified):

select strftime('%m-%Y', date) from record

You do not select date column; you select a function which takes the column as a parameter. Databases in such cases assign the resultset columns some arbitrary names, such as

STRFTIME_
05-2005
08-2009

As you see, date column doesn't exist in the result set (just like the error says).

What you need to try is:

select strftime('%m-%Y', date) as date from record

or

select strftime('%m-%Y', date) as formatted_date from record

and use 'date' or 'formatted_date' to reference the resultset column.

P.S.

select strftime('%m-%Y', date) from record, it returns 3 blank rows

This might be the different issue. If date indded is stored as text, then strftime() function wouldn't know how to format it.