Android column "_id" does not exist?

Question

I'm having problems with something that works in the notepad example. Here is the code from 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 is working fine. But to be clear, I ran the ADB utility and run SQLite 3. I checked 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); 

I'm fine.

---

Now in my application, which, as far as I see, is basically the same with a few minor changes. I have 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 fingerprints in LogCat from my Log.e() statement:

LogCat message:

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

So, back to SQLite 3 to find out what is different from my schema:

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

I don’t see how I am missing "_id".

What did I do wrong?

One thing that differs from my application and the notepad example is that I started by creating my application from scratch using the Eclipse Wizard while the sample application is already compiled. Is there any environmental changes that I need to make for a new application to use the SQLite database?

Answer 1

I see the documentation for the CursorAdapter says:

The cursor must contain a column named _id or this class will not work.

SimpleCursorAdapter is a derived class, so this statement appears to apply. However, the expression is technically incorrect and somewhat misleading to the beginner. the result set for the cursor must contain _id , not the cursor itself.
I am sure that this is understandable to the database administrator, because they understand this brief documentation, but for these newcomers, being incomplete in the application, it causes confusion. Cursors are similar to iterators or pointers; they contain nothing but a data transversion mechanism; they do not contain the columns themselves.

The bootloader documentation contains an example where you can see that _id included in the forecast 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"); } 

Answer 2

This has been answered , and I would like to make it more complete.

SimpleCursorAdapter requires that the Cursor result set must contain a column named exactly "_id". Do not rush to change the scheme if you did not specify the column "_id" in your table. SQLite automatically adds a hidden column named "rowid" for each table. All you have to do is just select rowid explicitly and an alias as the "_id" Ex.

 SQLiteDatabase db = mHelper.getReadableDatabase(); Cursor cur = db.rawQuery( "select rowid _id,* from your_table", null); 

Answer 3

Tim W's code really works ...

If you use db.query, then it will be so ...

 db.query(TABLE_USER, new String[] { "rowid _id", FIELD_USERNAME, }, FIELD_USERNAME + "=" + name, null, null, null, null); 

Answer 4

What solved my problem with this error was that I did not include the _id column in my DB query. By adding this, I solved the problem.

Answer 5

This is probably no longer relevant, but today I ran into the same problem. Turns out column names are case sensitive. I had a _ID column, but Android is expecting a _id column.

Answer 6

Yes, I also modify the SELECT string query to fix this problem.

 String query = "SELECT t.*,t.id as _id FROM table t "; 

Answer 7

If you read the documents in sqlite, creating any column of type INTEGER PRIMARY KEY will be an internal alias of ROWID, so you should not add an alias to each SELECT, deviating from any common utilities that can take advantage of something like listing the columns that define the table.

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

It is also easier to use this as a ROWID instead of the AUTOINCREMENT option, which can cause _ID to deviate from the ROWID. Binding _ID to ROWID means that the primary key is returned from insert / insertOrThrow; if you write ContentProvider, you can use this key in the returned Uri.

Answer 8

Another way to deal with the absence of the _id column in the table is to write a subclass of CursorWrapper that will add the _id column if necessary.

This has the advantage that no changes to tables or queries are required.

I wrote such a class, and if interested, then it can be found at https://github.com/cmgharris/WithIdCursorWrapper