Exception when accessing cursor

Question

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.

Did you ever figure this out? I'm having this same problem now. — Kalina
– Kalina, Commented Jul 22, 2011 at 21:54

Vladimir Dyuzhev · Accepted Answer · 2011-04-13 15:12:44Z

1

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.

edited Apr 13, 2011 at 15:12

answered Apr 13, 2011 at 15:06

Vladimir Dyuzhev

18.4k10 gold badges53 silver badges62 bronze badges

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

2 Comments

Roger Over a year ago

When I try those, it returns a result of blank rows. I have 4 dates in the column, it returns 4 blank rows.

Vladimir Dyuzhev Over a year ago

What is the datatype of date field? Is it really text as you said? Then the result is somewhat predictable: strftime works with DATE type, not strings.

Collectives™ on Stack Overflow

Exception when accessing cursor

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related