73

I'm having trouble with something that works in the Notepad example. Here's the code from the NotepadCodeLab/Notepadv1Solution:

String[] from = new String[] { NotesDbAdapter.KEY_TITLE };
int[] to = new int[] { R.id.text1 };

SimpleCursorAdapter notes = new SimpleCursorAdapter(this,
R.layout.notes_row, c, from, to);

This code seems to work fine. But just to be clear, I ran the ADB utility and run SQLite 3. I inspected the schema as follows:

sqlite> .schema

CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE notes (_id integer primary key autoincrement, title text
not null, body text not null);

All seems good to me.

Now on to my application, which, as far as I can see, is basically the same with a few minor changes. I've simplified and simplified my code, but the problem persists.

String[] from = new String[] { "x" };
int[] to = new int[] { R.id.x };

SimpleCursorAdapter adapter = null;
try
{
    adapter = new SimpleCursorAdapter(this, R.layout.circle_row, cursor, from, to);
}
catch (RuntimeException e)
{
    Log.e("Circle", e.toString(), e);
}

When I run my application, I get a RuntimeException and the following prints in LogCat from my Log.e() statement:

LogCat Message:

java.lang.IllegalArgumentException: column '_id' does not exist

So, back to SQLite 3 to see what's different about my schema:

sqlite> .schema CREATE TABLE android_metadata (locale TEXT); CREATE TABLE circles (_id integer primary key autoincrement, sequence integer, radius real, x real, y real);

I don't see how I'm missing the '_id'.

What have I done wrong?

One thing that's different between my application and the Notepad example is that I started by creating my application from scratch using the Eclipse wizard while the sample application comes already put together. Is there some sort of environmental change I need to make for a new application to use a SQLite database?

Deepzz
  • 4,573
  • 1
  • 28
  • 52
Andrew
  • 731
  • 1
  • 5
  • 3

8 Answers8

155

I see, the documentation for CursorAdapter states:

The Cursor must include a column named _id or this class will not work.

The SimpleCursorAdapter is a derived class, so it appears this statement applies. However, the statement is technically wrong and somewhat misleading to a newbie. The result set for the cursor must contain _id, not the cursor itself.
I'm sure this is clear to a DBA because that sort of shorthand documentation is clear to them, but for those newbies, being incomplete in the statement causes confusion. Cursors are like iterators or pointers, they contain nothing but a mechanism for transversing the data, they contain no columns themselves.

The Loaders documentation contains an example where it can be seen that the _id is included in the projection parameter.

static final String[] CONTACTS_SUMMARY_PROJECTION = new String[] {
    Contacts._ID,
    Contacts.DISPLAY_NAME,
    Contacts.CONTACT_STATUS,
    Contacts.CONTACT_PRESENCE,
    Contacts.PHOTO_ID,
    Contacts.LOOKUP_KEY,
};
public Loader<Cursor> onCreateLoader(int id, Bundle args) {
    // ...
    return new CursorLoader(getActivity(), baseUri,
            CONTACTS_SUMMARY_PROJECTION, select, null,
            Contacts.DISPLAY_NAME + " COLLATE LOCALIZED ASC");
}
JJD
  • 50,076
  • 60
  • 203
  • 339
user405821
  • 1,845
  • 2
  • 16
  • 17
  • 15
    I recommend the statement in the documentation be written as "The Cursor's result set must include a column named exactly "_id". Failing to do so will produce a RuntimeException." This would be less confusing and correct. – user405821 Jul 29 '10 at 16:02
  • 6
    @user405821 - Thanks man - this guy didn't mark your answer as correct - but this was an excellent addition to a part of Android that is poorly documented. +1 – tpow Dec 11 '10 at 15:55
  • i made a note about in the android docs using google sidewiki. sidewiki is perfect for this sort of thing, and i hope that more people use it in conjunction with the android documentation. http://developer.android.com/reference/android/widget/CursorAdapter.html – Ben H Oct 28 '11 at 22:21
  • Oh man, this is the amazing Android SDK + documentation for you! Thank you so much for the answer. I spent more than an hour trying to figure out what the problem was. – the_dark_destructor Jul 03 '13 at 09:16
  • Thanks! In my case the error was due to a problem of uppercase v/s lowercase. – Vish Jan 08 '16 at 09:05
  • Thanks! I change database eid to _id, uninstalled previous one and reinstall the app. Now it works. – Shark Deng Oct 14 '19 at 03:55
108

This has been answered and I would like to make it more comprehensive here.

SimpleCursorAdapter requires that the Cursor's result set must include a column named exactly "_id". Don't haste to change schema if you didn't define the "_id" column in your table. SQLite automatically added an hidden column called "rowid" for every table. All you need to do is that just select rowid explicitly and alias it as '_id' Ex.

SQLiteDatabase db = mHelper.getReadableDatabase();      
Cursor cur =  db.rawQuery( "select rowid _id,* from your_table", null);
Community
  • 1
  • 1
Tim Wu
  • 2,047
  • 2
  • 22
  • 32
  • How about overriding `getItemId` and provide my own defined ID? Can it solve the problem? – fikr4n Jun 06 '12 at 09:58
  • @max4ever has the best answer. It is the perfect solution to this issue. No need to start creating unnecessary _id columns in your database – Mike Baxter Aug 02 '13 at 11:24
  • +1, Using "rowid _id" in a query really helps. But what a mess is Android if its default DB implementation creates `rowid` key while its default cursor adapter requires `_id`?! – Stan Mar 16 '16 at 15:41
  • Worked for me too. This is the answer that saves me from adding _id into my database. – TryinHard Sep 12 '16 at 17:13
21

Tim Wu's code really works...

If you are using db.query, then it would be like this...

db.query(TABLE_USER, new String[] { 
                "rowid _id",
                FIELD_USERNAME,
                }, 
                FIELD_USERNAME + "=" + name, 
                null, 
                null, 
                null, 
                null);
Deepzz
  • 4,573
  • 1
  • 28
  • 52
8

Yes , I also change the SELECT string query to fix this issue. 

String query = "SELECT t.*,t.id as _id FROM table t ";
Deepzz
  • 4,573
  • 1
  • 28
  • 52
Felipe FMMobile
  • 1,641
  • 20
  • 17
7

What solved my issue with this error was that I had not included the _id column in my DB query. Adding that solved my problem.

Tapan Nallan
  • 1,762
  • 3
  • 17
  • 37
RoadXY
  • 447
  • 4
  • 10
6

This probably isn't relevant anymore, but I just hit the same problem today. Turns out column names are case sensitive. I had an _ID column, but Android expects an _id column.

zmbq
  • 38,013
  • 14
  • 101
  • 171
  • This is an easy mistake to make; the official documentation says to use _ID. –  Dec 26 '13 at 15:49
0

If you read the docs on sqlite, creating any column of type INTEGER PRIMARY KEY will internally alias the ROWID, so it isn't worth the trouble of adding an alias in every SELECT, deviating from any common utilities that might take advantage of something like an enum of columns defining the table.

http://www.sqlite.org/autoinc.html

It is also more straightforward to use this as the ROWID instead of the AUTOINCREMENT option which can cause _ID can deviate from the ROWID. By tying _ID to ROWID it means that the primary key is returned from insert/insertOrThrow; if you are writing a ContentProvider you can use this key in the returned Uri.

Eric Woodruff
  • 6,380
  • 3
  • 36
  • 33
0

Another way of dealing with the lack of an _id column in the table is to write a subclass of CursorWrapper which adds an _id column if necessary.

This has the advantage of not requiring any changes to tables or queries.

I have written such a class, and if it's of any interest it can be found at https://github.com/cmgharris/WithIdCursorWrapper

cmgharris
  • 389
  • 4
  • 11