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.
精彩评论