Cannot read value from database

Question

This is my first attempt to write/read to a database using Android and SQLite.

The code below appears to be inserting data (I can see an increment in number of rows) but when I try to call the value, an exception is thrown

E/CursorWindow﹕ Failed to read row 0, column -1 from a CursorWindow which has 6 rows, 2 columns.

I can't see why the below is failing.

  public void Save(String name, String email) {

    _db.execSQL("CREATE TABLE IF NOT EXISTS MailingList (Email VARCHAR, Name VARCHAR);");

    _db.execSQL("INSERT INTO MailingList (Email, Name) VALUES('" + email + "', '" + name + "');");


    ReadDatabase();
    _db.close();

    //_db.deleteAll();
}

private void ReadDatabase() {

    Cursor cursor = _db.rawQuery("SELECT * FROM MailingList", null);

    int i = cursor.getCount();

    ArrayList<String> results = new ArrayList<String>();
    if (cursor != null) {

        if (cursor.moveToFirst()) {
            do {

                String name = cursor.getString(cursor.getColumnIndex("Name")); //ERROR
                String email = cursor.getString(cursor.getColumnIndex("Email"));
                results.add("Name: " + name + ", Email: " + email);
            } while (cursor.moveToNext());
        }
    }
    ListView myList = (ListView) findViewById(R.id.listViewFromDB);
    myList.setAdapter(new ArrayAdapter<String>(this, R.layout.item_layout, results));
}

LogCat

 Caused by: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
            at android.database.CursorWindow.nativeGetString(Native Method)
            at android.database.CursorWindow.getString(CursorWindow.java:435)
            at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.ReadDatabase(MyActivity.java:106)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.Save(MyActivity.java:88)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.SaveClick(MyActivity.java:73)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at android.view.View$1.onClick(View.java:3830)
            at android.view.View.performClick(View.java:4450)
            at android.view.View$PerformClick.run(View.java:18600)
            at android.os.Handler.handleCallback(Handler.java:733)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:136)
            at android.app.ActivityThread.main(ActivityThread.java:5026)
            at java.lang.reflect.Method.invokeNative(Native Method)

Answer 1

E/CursorWindow﹕ Failed to read row 0, column -1 from a CursorWindow which has 6 rows, 2 columns.

This error indicates that you were trying to get the column index of a column which does not exist. getColumnIndex() returns -1 when it cannot find the specific column. I don't see any error in your code so I think there might be something wrong with the database itself.

When developing you have to remember that the SQLite database is only really deleted when you uninstall the app or wipe all data in the application manager.
So if you at some point made a mistake in an sql script or you added, removed or renamed some columns you need to uninstall and then reinstall the app for those changes to take effect in the database.

---

This of course is a problem for many apps and when developing you are going to run into this all the time. But as soon as your app is in the app store people are going to use it and you cannot ask everyone to reinstall your app every time you update it. That would be the ridiculous and people aren't going to like it, but there is a way around this.

You need to pass a version number into your SQLiteOpenHelper. Increase this version number every time you change something about the database and then when Android notices that the database version has increased the onUpgrade() callback will be called to upgrade your database accordingly! Try something like this:

public class ExampleSQLiteOpenHelper extends SQLiteOpenHelper {

    public ExampleSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // The create query here always has to be for the most up to 
        // date version of the database
        db.execSQL("CREATE TABLE MailingList (Email VARCHAR, Name VARCHAR, PhoneNumber VARCHAR);");
        db.execSQL("CREATE TABLE SomeTable (_id PRIMARY KEY AUTOINCREMENT, SomeColumn VARCHAR)");
        ...
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // This will be called when you are starting your app for the first time 
        // after increasing the version number.

        // This loops through all the version between the current version of             
        // the database and the newest version. upgradeTo is called with each 
        // version in between.
        for(int i = oldVersion + 1; i <= newVersion; i++) {
            upgradeTo(db, i);
        }
    }

    private void upgradeTo(SQLiteDatabase db, int version) {

        // With this switch you can execute the upgrade scripts for each version 
        // of the databse
        switch (version) {
            case 2:
                // in this version we added a new column to MailingList so we 
                // use ALTER TABLE to add the new column
                db.execSQL("ALTER TABLE MailingList ADD COLUMN PhoneNumber VARCHAR");
                break;

            case 3:
                // In this version we added a new table
                db.execSQL("CREATE TABLE SomeTable (_id PRIMARY KEY AUTOINCREMENT, SomeColumn VARCHAR)");
                break;

            case 4:
                ...
                break;

            case 5:
                ...
                break;
        }
    }
}

The simplest solution of course is to drop all tables and recreate them in onUpgrade but than all the data in the database is lost. If you don't want that you can use something like the code above to gradually upgrade your database!