0

I am trying to add a TIMESTAMP Column to my SQLite database. The column is to be used to capture timestamp data using "System.currentTimeMillis();". App is crashing and the error is from the cursor code shown below in the line with ** **. The error reads "Unable to start activity ComponentInfo{...ListActivity}: java.lang.IllegalStateException: Couldn't read row 0, col 6 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it."

Initially I set up the variable as a String in the model file. Then I tried a long and neither worked. What am I missing here?

UserData file:

...
public long getTimestamp() {
    return timestamp;
}

public void setTimestamp(long timestamp) {
    this.timestamp = timestamp;

DatabaseHelper.java file:

...
private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE IF NOT EXISTS " + DBContract.DBEntry.TABLE_NAME +
            "(" + DBContract.DBEntry.COLUMN_NAME_ID +
                  " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + DBContract.DBEntry.COLUMN_NAME_TODO +
                  " TEXT,"
                + DBContract.DBEntry.COLUMN_NAME_NOTE1 +
                  " TEXT,"
                + DBContract.DBEntry.COLUMN_NAME_NOTE2 +
                  " TEXT,"
                + DBContract.DBEntry.COLUMN_NAME_DUEDATE +
                  " TEXT,"
                + DBContract.DBEntry.COLUMN_NAME_DUETIME +
                " TEXT,"
                + DBContract.DBEntry.COLUMN_NAME_TIMESTAMP +
                  " TEXT" + ")";
...

public void insertIntoDB(String todo, String note1, String note2, String duedate, String duetime, long timestamp) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(DBContract.DBEntry.COLUMN_NAME_TODO, todo);
    values.put(DBContract.DBEntry.COLUMN_NAME_NOTE1, note1);
    values.put(DBContract.DBEntry.COLUMN_NAME_NOTE2, note2);
    values.put(DBContract.DBEntry.COLUMN_NAME_DUEDATE, duedate);
    values.put(DBContract.DBEntry.COLUMN_NAME_DUETIME, duetime);        
    values.put(DBContract.DBEntry.COLUMN_NAME_TIMESTAMP, timestamp);

    db.insert(DBContract.DBEntry.TABLE_NAME, null, values);
    db.close();
}
...

Cursor cursor = db.rawQuery(query,null);

        try {
            if (cursor.moveToFirst()) {
                do {
                    UserData userData = new UserData();
                    userData.setTodo(cursor.getString(1));
                    userData.setNote1(cursor.getString(2));
                    userData.setNote2(cursor.getString(3));
                    userData.setDuedate(cursor.getString(4));
                    userData.setDuetime(cursor.getString(5));
                    **userData.setTimestamp(cursor.getLong(6));**

                    modelList.add(0, userData);
                    } while (cursor.moveToNext());
...

ListAdapter.java file:

...
public void onBindViewHolder(final ListViewHolder holder, final int position) {
    ...
    holder.cardBlankText5.setText((int) dbList.get(position).getTimestamp());

Activity.java file:

...
public void onClickSave(View v) {
    ...
    long timestamp=System.currentTimeMillis();
    helper = new DatabaseHelper(Activity.this);
    helper.insertIntoDB(todo,note1,note2,duedate,duetime,timestamp);
    startActivity(new Intent(Activity.this,ListActivity.class));
}
AJW
  • 1,578
  • 3
  • 36
  • 77
  • after `rawQuery` call `DatabaseUtils#dumpCursor`, what do you see? – pskink Apr 28 '16 at 06:12
  • @CL Do you mean call dumpCursor and show the Logcat result? – AJW Apr 28 '16 at 11:13
  • @pskink after running dumpCursor this is what was shown: "E/CursorWindow: Failed to read row 0, column 6 from a CursorWindow which has 33 rows, 6 columns." – AJW Apr 28 '16 at 11:25
  • I am wondering if I should be deleting the existing database since it previously only had 5 data columns (plus the _ID) column) and already had data and starting with a new database with no data, please advise. – AJW Apr 28 '16 at 11:34
  • what is query passed to rawQuery method? – pskink Apr 28 '16 at 11:44
  • String query = "select * from "+DBContract.DBEntry.TABLE_NAME; – AJW Apr 28 '16 at 11:45
  • CL's link above to was very helpful to resolve my issues. Specifically see "laalto's" answer at url: http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run. – AJW Apr 28 '16 at 23:51

2 Answers2

-1

Having DBContract.DBEntry.COLUMN_NAME_TIMESTAMP as a DATETIME type should work. I usually have utility method that takes the cursor and column name and returns a Date object:

public static Date getDateColumn(Cursor c, String column) {
    Date d = null;
    Long time = c.getLong(c.getColumnIndexOrThrow(column));
    if (time != null && time != 0) {
         d = new Date();
         d.setTime(time);
    }
    return d;
}

The second thing to check is whether or not that field is being asked for in your query. Sharing the value of query could shed some light, but usually you get things like this when the column you are asking for isn't in the projection.

e.g. select columnA, columnB from mytable; and then you try to get something from columnC which isn't in the results.

rossco
  • 523
  • 4
  • 20
  • Right so I am trying to do the query on the database and setting the timestamp data in the Cursor so I can use the Cursor in a RecyclerView list. Not sure how I would use your utility method with my userData object for the cursor as shown above. – AJW Apr 28 '16 at 04:24
  • Only line 3 is pertinent for you as you wish to store it as a long in your POJO, if you want to store it as a Date object then the method is useful. In any case, storing as `DATETIME` in the table should resolve your issue. Make sure you remove your app and reinstall it after changing the table definition though. – rossco Apr 28 '16 at 04:39
  • So you are recommending replacing the "TEXT" for TIMESTAMP with "DATETIME"? – AJW Apr 28 '16 at 04:44
  • Yep, then Uninstall the app like @Sergey Chechenev mentioned. Hold down the app icon and drag up to the top and dump in Uninstall. Then run the app again from Android Studio. – rossco Apr 28 '16 at 04:50
  • Ok did that with device (phone). App still crashing. – AJW Apr 28 '16 at 05:01
  • Can you debug and post the value of `query` that you pass into `db.rawQuery(query, null)`? – rossco Apr 28 '16 at 05:37
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110456/discussion-between-rossco-and-ajw). – rossco Apr 28 '16 at 05:42
  • Haven't done debug as I'm an android newbie. Too late here now so I will pick back up tomorrow. I appreciate your help to try to get me up and running. – AJW Apr 28 '16 at 05:42
-1

Don't try to get Long from column declared as TEXT. Declare it as INTEGER. Or use getString() and convert value to Long.

Sergey Chechenev
  • 79
  • 5