Android - SQLite Cursor getColumnIndex() is case sensitive?

Question

While working with SQLiteCursor in Android I came to know that the getColumnIndex() is behaving case sensitive for example:

Example:

Column Name in DB was: Rules

cursor.getColumnIndex("Rules")  //works fine
cursor.getColumnIndex("rules")  //throws error, see the error detail

The documentation says nothing about that, for detail please see this.

LogCat says:

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

I am confused by this behavior of SQLiteCursor, can someone help me that this is true OR I am doing something wrong? I can provide the code if required.

Thanks.

Answer 1

getColumnIndex() is case sensitive:

Column Name in DB was: Rules

cursor.getColumnIndex("Rules") //workes fine

cursor.getColumnIndex("rules") //throws error, see the error detail

Answer 2

The best and recommended approach using SQLite is that you declare all your table name and column name static, final and class level.. for example:

// write table name
public static final String TABLE_MESSAGE = "messages";
// and column name accordingly
public static final String COLUMN_ID = "_id";
public static final String COLUMN_MESSAGE = "message";

so the benefit of this approach is you don't need to remember the spelling and case etc of the table and column names.

when you access any table or column you simply use these static variables for example:

// TABLE creation sql statement
private static final String TABLE_CREATE = "create table "
            + TABLE_MESSAGE + "( " + COLUMN_ID
            + " integer primary key autoincrement, " + COLUMN_MESSAGE
            + " text not null);";

while querying:

database.query(TABLE_MESSAGE, new String[]{COLUMN_ID,COLUMN_MESSAGE}, null, null, null, null, null);

or it may be used in Cursor

int index = cursor.getColumnIndex(COLUMN_MESSAGE);

this will help you to avoid such conflicts of case sensitivity and spelling mistakes. :)

Answer 3

Another way would be to Query the database itself for the correct name by using PRAGMA table_info, So I wrote a method for just that:

public class database {
    private SQLiteDatabase mainDB = null;

    private boolean CreateOrOpenDB() {
        try {
            if (mainDB == null || !mainDB.isOpen()) {
                mainDB = Context.openOrCreateDatabase("mainDB", SQLiteDatabase.CREATE_IF_NECESSARY, null);
            }
        } catch (SQLiteException e) {
            return false;
        }
        return true;
    }

    private String GetTrueColumnName(String TableName, String column) {
        String TrueColName = "";
        if (CreateOrOpenDB()) {
            try {
                Cursor c = mainDB.rawQuery("PRAGMA table_info(" + TableName + ");", null);

                if (c != null) {
                    if (c.moveToFirst()) {
                        do {
                            String dbcolumn = c.getString(c.getColumnIndex("name"));
                            if (column.toLowerCase().equals(dbcolumn.toLowerCase())) {
                                TrueColName = dbcolumn;
                                break;
                            }
                        } while (c.moveToNext());
                    }
                    c.close();
                }
                mainDB.close();
            } catch (Exception e) {
            }
        }
        return TrueColName;
    }
}

then all you need to call is:

String CorrectName = GetTrueColumnName(TableName, "RuLeS");

and yes, I know it will be hard on the database. But it works and is stable