Why is the _id column in my sqlite database blank?

Question

I am making my first android project. Its purpose is currently to store times when a button is pressed, in a database. The full contents of the database are then displayed using a LoaderManager / CursorLoader / Custom SimpleCursorAdapter / ContentProvider / ListView based activity.

The problem is this: the database is not storing any numbers in the _id column when I insert a new row. It is just blank:

I have tried to find an answer to this to no avail, and I tried to debug deep into the writing-to-database code, to no avail.

The code that executes when the button is pressed is:

// Function to record an awake time
public void onAwake(View view) {
    DateTime now = new DateTime();
    DateTime startOfCurrentDay = now.withTimeAtStartOfDay();
    DateTime noonOfCurrentDay = startOfCurrentDay.plusHours(12);

    TimeEntry timeEntry = new TimeEntry(noonOfCurrentDay.getMillis(), now.getMillis(), TimeEntry.TimeEntryType.TIME_RECORD, TimeEntry.Direction.WAKE);

    TimeEntryDbHandler timeEntryDbHandler = new TimeEntryDbHandler(this);
    long id = timeEntryDbHandler.addTimeEntry(timeEntry);

    // Notify user of execution
    Toast.makeText(this, R.string.toast_awake + " ID:" + String.valueOf(id), Toast.LENGTH_SHORT).show();

}

When this executes, the ID that is shown in the toast is incrementing as you would expect (currently at 32 or something).

The database handler code is then as follows:

public class TimeEntryDbHandler extends SQLiteOpenHelper implements BaseColumns {
// If you change the database schema, you must increment the database version.
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "FeedReader.db";
public static final String TABLE_TIME_ENTRIES = "time_entries";

//public static final String _ID = "_id"; // now in basecolumns
public static final String COLUMN_CENTER_OF_DAY = "center_of_day";
public static final String COLUMN_TIME = "time";
public static final String COLUMN_TIME_ENTRY_TYPE = "time_entry_type";
public static final String COLUMN_WAKEUP_OR_BEDTIME = "wakeup_or_bedtime";

public TimeEntryDbHandler(Context context) {
    super (context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_TIME_ENTRIES_TABLE = "CREATE TABLE " + TABLE_TIME_ENTRIES + "("
            + _ID + " INTEGER PRIMARY KEY,"
            + COLUMN_CENTER_OF_DAY + " INTEGER,"
            + COLUMN_TIME + " INTEGER,"
            + COLUMN_TIME_ENTRY_TYPE + " TEXT,"
            + COLUMN_WAKEUP_OR_BEDTIME + " TEXT"
            + ")";
    db.execSQL(CREATE_TIME_ENTRIES_TABLE);
}

// Method to add new time entry row to time entries table
public long addTimeEntry(TimeEntry timeEntry) {
    ContentValues values = new ContentValues();
    values.put(COLUMN_CENTER_OF_DAY, timeEntry.get_centerOfDay());
    values.put(COLUMN_TIME, timeEntry.get_time());
    values.put(COLUMN_TIME_ENTRY_TYPE, timeEntry.get_timeEntryType().name());
    values.put(COLUMN_WAKEUP_OR_BEDTIME, timeEntry.get_direction().name());

    SQLiteDatabase db = this.getWritableDatabase();

    long newRowId;
    newRowId = db.insert(TABLE_TIME_ENTRIES, null, values);
    db.close();

    return newRowId;
}

Does anyone know why the database is not simply getting an incrementing integer written into the _id column? Otherwise, what do you suggest I do to debug this?

I also tried the 'AUTO INCREMENT' directive in the onCreate SQL command, but that didn't change anything.

If other areas of the project code need to be posted, let me know what it is and I'll post it.

Many thanks!

While this question shows good research being done on the issue, it lacks research concerning the SE site it belongs to :) I've marked it for migration to [SO]. — Izzy, Jul 19 '15 at 18:17
An INTEGER PRIMARY KEY column *cannot* be blank. Did you ever change the `onCreate` function? — CL., Jul 20 '15 at 09:23
Hmm I'm not sure, but it's possible I did change the onCreate function at some point. How could this affect things? — , Jul 20 '15 at 09:56
OK so I made the app drop the table and onCreate it from fresh. Now the _id column has incrementing numbers. I guess I made some change to the onCreate in the past and forgot to upgrade or drop the table, and that this corrupted it somehow. Cheers — , Jul 20 '15 at 10:13
@CL: An INTEGER PRIMARY KEY can be blank, by default its value is determined by SQLite automatically. It's like AUTOINCREMENT except that it's not monotonic (new entries may reuse values of deleted entries). — BladeCoder, Jul 21 '15 at 19:51
See [When is SQLiteOpenHelper onCreate() / onUpgrade() run?](http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run) — CL., Jul 21 '15 at 20:35

BladeCoder · Answer 1 · 2015-07-21T19:45:52.890

0

Are you sure the column is actually empty ? Did you try to perform a select query to see if it returns a value?

SQLite creates a special column called rowid for each table. If you declare a field of type INTEGER PRIMARY KEY, this field automatically becomes an alias for rowid. So what you want to check is the value of the rowid column.

edited Jul 21 '15 at 19:45

answered Jul 19 '15 at 19:16

BladeCoder

12,779
3
59
51

The database has been opened with 'sqlitebrowser', as in the picture above, those are all the columns in it. Yes its definitely empty because the report page queries it and shows the same thing. All the android studio docs/tutorials say the column is named "_id" though... – Jul 20 '15 at 07:13
`_id`is the standard Android column name used in a result set for the integer primary key. CursorAdapter for example looks for a column with this name to map getItemId(). Most of the time, `_id` is just an alias for the internal `rowid`column of SQLite. Check the SQLite documentation for more information: https://www.sqlite.org/lang_createtable.html#rowid – BladeCoder Jul 21 '15 at 19:45

Why is the _id column in my sqlite database blank?

1 Answers1