Running SQLite query in Android

Question

I am new to SQLite queries in android. Trying to run a query. However, app keeps crashing with the following error :

Caused by: java.lang.IllegalStateException: Couldn't read row 1, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

Below is the extract of Code

public String getOutputData(){
    String inputData = "'Vineet'";

    Cursor cursorNew = db.rawQuery("SELECT email FROM groupseries INNER JOIN email " +
            "ON groupseries.emailid = email._id INNER JOIN user ON " +
            "groupseries.userid = user._id WHERE name=" + inputData, null);
    cursorNew.moveToFirst ();
    StringBuffer buffer = new StringBuffer();

    while(cursorNew.moveToNext()){
        int index1 = cursorNew.getColumnIndex(DatabaseHelper.KEY_NAME);
        int index2 = cursorNew.getColumnIndex(DatabaseHelper.KEY_EMAIL);
        String userID = cursorNew.getString(index1);
        String emailID = cursorNew.getString(index2);
        buffer.append(userID + " " + emailID + "\n");
    }
    return buffer.toString();
}

Below is the code for table creation I am using. (Open to suggestions to make this less clumsy).

private static final String DATABASE_NAME = "tryDemoDataBase.db";
    private static final String TABLE_NAME_USER = "user";
    private static final String TABLE_NAME_EMAIL = "email";
    private static final String TABLE_NAME_GROUP = "groupseries";
    private static final int DATABASE_VERSION = 12;
    private static final String KEY_ROWID_USER = "_id";
    private static final String KEY_ROWID_EMAIL = "_id";
    private static final String KEY_ROWID_GROUP = "_id";
    private static final String REFERENCE_USER_ID = "userid";
    private static final String REFERENCE_EMAIL_ID = "emailid";
    private static final String KEY_NAME="name";
    private static final String KEY_EMAIL = "email";
    private static final String CREATE_TABLE_USER = "create table "+ TABLE_NAME_USER
            + " ("+ KEY_ROWID_USER+" integer primary key autoincrement, "
            + KEY_NAME + " text)";
    private static final String CREATE_TABLE_EMAIL = "create table "+ TABLE_NAME_EMAIL
            + " ("+ KEY_ROWID_EMAIL+" integer primary key autoincrement, "
            + KEY_EMAIL + " text)";
    private static final String CREATE_TABLE_GROUP = "create table " + TABLE_NAME_GROUP+ " ("
            + KEY_ROWID_GROUP+" integer primary key autoincrement, "
            + REFERENCE_USER_ID + " integer, " + REFERENCE_EMAIL_ID + " integer)";

forpas · Accepted Answer · 2020-05-26 13:19:13Z

First remove this line:

cursorNew.moveToFirst();

because later you use:

while (cursorNew.moveToNext())

so actually you are skipping the 1st (and maybe the only) row of the results.
Then you must include in the selected columns the column name if you want to retrieve it from the Cursor object:

Cursor cursorNew = db.rawQuery("SELECT name, email FROM groupseries INNER JOIN email " +
            "ON groupseries.emailid = email._id INNER JOIN user ON " +
            "groupseries.userid = user._id WHERE name=" + inputData, null);

And a suggestion to use the recommended and safe way of passing parameters to a query instead of concatenating them directly.
Use ? placeholders:

String inputData = "Vineet"; // no quotes
Cursor cursorNew = db.rawQuery(
            "SELECT name, email FROM groupseries INNER JOIN email " +
            "ON groupseries.emailid = email._id INNER JOIN user ON " +
            "groupseries.userid = user._id WHERE name=?", 
             new String[] {inputData}
);

Collectives™ on Stack Overflow

Running SQLite query in Android

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related