Error in loading SQlite database

Question

I've been learning how to create and load an SQLite database in Android and I've tried modifying a tutorial I found. Link to the original tutorial

I've been able to get this up and running, but I've since tried modifying it to accept four inputs, two text strings and two numbers (saving them as strings for now). Whenever I try to run OpenViewData it crashes and I'm not able to understand why.

My MainActivity is:

public class MainActivity extends AppCompatActivity {

public Button Save;
public  Button View;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    Save = (Button)findViewById(R.id.Save);
    View = (Button)findViewById(R.id.View);


    Save.setOnClickListener(new android.view.View.OnClickListener() {
        @Override
        public void onClick(android.view.View view) {
            SaveButtonHit();
        }
    });

    View.setOnClickListener(new android.view.View.OnClickListener() {
        @Override
        public void onClick(View view) {

            OpenViewData();
        }
    });


}
void SaveButtonHit(){


    EditText Word1 = (EditText)findViewById(R.id.Word1);
    EditText Word2 = (EditText)findViewById(R.id.Word2);
    EditText Num1 = (EditText)findViewById(R.id.Number1);
    EditText Num2 = (EditText)findViewById(R.id.Number2);

    String ReceivedWord1 = Word1.getText().toString();
    String ReceivedWord2 = Word2.getText().toString();
    String ReceivedNum1 =  Num1.getText().toString();
    String ReceivedNum2 =  Num2.getText().toString();


    Toast.makeText(getApplicationContext(), "words read", Toast.LENGTH_SHORT).show();
    DatabaseHandler db = new DatabaseHandler(this);

    db.addContact(new Contact(ReceivedWord1, ReceivedWord2, ReceivedNum1, ReceivedNum2));
    db.close();
}



void OpenViewData(){

    DatabaseHandler db = new DatabaseHandler(this);
    List contacts = db.getAllContacts();
    Iterator var5 = contacts.iterator();

      while(var5.hasNext()) {
        Contact cn = (Contact)var5.next();
        String log = "Id: " + cn.getID() + " ,WORD1: " + cn.getWord1() + " ,WORD2: " + cn.getWord2()+ " ,NUM1: " + cn.getNum1()+ " ,NUM2: " + cn.getNum2();
        Log.d("Name: ", log);
    }

  }
}

My Contact class is:

public class Contact {
private int _id;
private String _word1;
private String _word2;
private String _num1;
private String _num2;

public Contact() {
}

public Contact(int id, String _word1, String _word2, String _num1, String _num2) {
    this._id = id;
    this._word1 = _word1;
    this._word2 = _word2;
    this._num1 = _num1;
    this._num2 = _num2;
}

public Contact(String _word1, String _word2, String _num1, String _num2) {
    this._word1 = _word1;
    this._word2 = _word2;
    this._num1 = _num1;
    this._num2 = _num2;
}

public int getID() {
    return this._id;
}

public void setID(int id) {
    this._id = id;
}

public String getWord1() {
    return this._word1;
}

public void setWord1(String word1) {
    this._word1 = word1;
}

public String getWord2() {
    return this._word2;
}


public void setWord2(String word2) {
    this._word2 = word2;
}


public String getNum1() {
    return this._num1;
}

public void setNum1(String num1) {
    this._num1 = num1;
}


public String getNum2() {
    return this._num2;
}

public void setNum2(String num2) {
    this._num2 = num2;
  }

}

And my helper class is:

public class DatabaseHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "test_database";
private static final String TABLE_CONTACTS = "contacts";
private static final String KEY_ID = "id";
private static final String KEY_WORD1 = "word1";
private static final String KEY_WORD2 = "word2";
private static final String KEY_NUM1 = "num1";
private static final String KEY_NUM2 = "num2";

public DatabaseHandler(Context context) { super(context, "test_database", null, 1); }

public void onCreate(SQLiteDatabase db) {

    String CREATE_CONTACTS_TABLE = "CREATE TABLE contacts(id INTEGER PRIMARY KEY,word1 TEXT,word2 TEXT,num1 TEXT,num2 TEXT)";
    db.execSQL(CREATE_CONTACTS_TABLE);
    Log.d("Reading: ", "Reading all contacts..");

}

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

        db.execSQL("DROP TABLE IF EXISTS contacts");
        this.onCreate(db);
}

void addContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("word1", contact.getWord1());
    values.put("word2", contact.getWord2());
    values.put("num1",  contact.getNum1());
    values.put("num1",  contact.getNum2());
    db.insert("contacts", null, values);
    db.close();

}

Contact getContact(int id) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query("contacts", new String[]{"id", "word1", "word2", "num1", "num2"}, "id=?", new String[]{String.valueOf(id)}, null, null, null, null);

    if(cursor != null) {
        cursor.moveToFirst();
    }

    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
    cursor.close();
    return contact;
}

public List<Contact> getAllContacts() {

    ArrayList contactList = new ArrayList();
    String selectQuery = "SELECT  * FROM contacts";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if(cursor.moveToFirst()) {

        do {

            Contact contact = new Contact();
            contact.setID(Integer.parseInt(cursor.getString(0)));
            contact.setWord1(cursor.getString(1));
            contact.setWord2(cursor.getString(2));
            contact.setNum1(cursor.getString(3));
            contact.setNum2(cursor.getString(4));
            contactList.add(contact);

        } while(cursor.moveToNext());
    }

    cursor.close();
    return contactList;
}

public int updateContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("word1", contact.getWord1());
    values.put("word2", contact.getWord2());
    return db.update("contacts", values, "id = ?", new String[]{String.valueOf(contact.getID())});

}

public void deleteContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete("contacts", "id = ?", new String[]{String.valueOf(contact.getID())});
    db.close();

}

public int getContactsCount() {

    String countQuery = "SELECT  * FROM contacts";
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, (String[])null);
    cursor.close();
    return cursor.getCount();

  }
  }

Edit 1:

Error from Android Monitor:

10-22 12:46:06.554 4936-4943/? E/art: Failed sending reply to debugger: Broken pipe

Edit2: What I think is the full error log??

10-22 12:46:02.858 4683-4683/? E/CursorWindow: Failed to read row 0, column 3 from a CursorWindow which has 6 rows, 3 columns.
10-22 12:46:02.858 4683-4683/? D/AndroidRuntime: Shutting down VM
10-22 12:46:02.858 4683-4683/? E/AndroidRuntime: FATAL EXCEPTION: main
                                                 Process: com.sahxyapps.yetanotherdatabasetutorial, PID: 4683
                                                 java.lang.IllegalStateException: Couldn't read row 0, col 3 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:438)
                                                     at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
                                                     at com.sahxyapps.yetanotherdatabasetutorial.DatabaseHandler.getAllContacts(DatabaseHandler.java:73)
                                                     at com.sahxyapps.yetanotherdatabasetutorial.MainActivity.OpenViewData(MainActivity.java:71)
                                                     at com.sahxyapps.yetanotherdatabasetutorial.MainActivity$2.onClick(MainActivity.java:39)
                                                     at android.view.View.performClick(View.java:5610)
                                                     at android.view.View$PerformClick.run(View.java:22265)
                                                     at android.os.Handler.handleCallback(Handler.java:751)
                                                     at android.os.Handler.dispatchMessage(Handler.java:95)
                                                     at android.os.Looper.loop(Looper.java:154)
                                                     at android.app.ActivityThread.main(ActivityThread.java:6077)
                                                     at java.lang.reflect.Method.invoke(Native Method)
                                                     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
                                                     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)

That's the output of the android Monitor? (sorry, still very new to android) — Diesel, Oct 22 '17 at 16:45
Yes you can find your error messages in the Android Monitor window — Alexandre Martin, Oct 22 '17 at 16:47
@Diesel First if you're so new and for ease also, Use [SQLiteAssetHelper](https://github.com/jgilfelt/android-sqlite-asset-helper), this is a super easy lib to use SQLite database without too much boilerplate code and Second, that's not the error log we need, see android lof monitor with almost 15-20 lines in red color, scroll the list and find those red lines that's what your error would be. — Lalit Fauzdar, Oct 22 '17 at 17:21
@LalitSinghFauzdar, Edit 2 is I think that's what you're looking for? I read that but still can't figure out the issue. I'll checkout that link as well, thanks! — Diesel, Oct 22 '17 at 17:50

MikeT · Accepted Answer · 2017-10-22T19:36:46.683

2

Failed to read row 0, column 3 from a CursorWindow which has 6 rows, 3 columns.

Is a clue, it is saying it cannot get the value from the 4th column of the Cursor (offset 3) because there are only 3 columns (0,1 & 2).

It is very likely that the cause is that you have changed the database structure (added columns) and just re-run the App assuming that the onCreate method will run and apply the changes.

The onCreate method actually only runs automatically when the database is first created.

The way around this is to do one of the following:-

Delete the App's data and rerun (this will delete the database).
Uninstall the App and rerun (deleting the data and thus the database).
Change the DATABASE_VERSION from 1 to 2 and rerun. This will cause the onUpgrade method to be invoked, which after DROPping(deleting) the TABLE will then call the 'onCreate' method.

Note! all 3 will result in the loss of existing data. If you need to keep the data then you have to resort to other methods.

edited Oct 22 '17 at 19:36

answered Oct 22 '17 at 19:29

MikeT

51,415
16
49
68

An obvious error when you mention it!. I've got a new issue now, but I think it's indirectly related. The data in the database wont show up when I try to output to log. – Diesel Oct 23 '17 at 22:30
@Diesel this may be of use :- [Are there any methods that assist with resolving common SQLite issues?](https://stackoverflow.com/questions/46642269/are-there-any-methods-that-assist-with-resolving-common-sqlite-issues) – MikeT Oct 23 '17 at 22:34
Thanks, that's a super useful link! I found my second error just now... I had commented out contactList.add(contact). Not my finest moments on this project it seems – Diesel Oct 24 '17 at 22:36
I'm also working on a module that could be added whilst developing an App to provide an in App SQLite Viewer. This does all that and more, here's a sneak at it [SQLiteInformationAssistant](https://stackoverflow.com/questions/46880610/since-when-does-sqlites-persist-journal-mode-become-the-default-journal-mode-in/46881019#46881019) – MikeT Oct 24 '17 at 22:45

Error in loading SQlite database

1 Answers1