Android - Sqlite Cursor Getcolumnindex() Is Case Sensitive?

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.getColumnIn

Solution 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

Solution 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 namepublicstaticfinalStringTABLE_MESSAGE="messages";
// and column name accordinglypublicstaticfinalStringCOLUMN_ID="_id";
publicstaticfinalStringCOLUMN_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 statementprivatestaticfinalString TABLE_CREATE = "create table "
            + TABLE_MESSAGE + "( " + COLUMN_ID
            + " integer primary key autoincrement, " + COLUMN_MESSAGE
            + " text not null);";

while querying:

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

or it may be used in Cursor

intindex = cursor.getColumnIndex(COLUMN_MESSAGE);

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

Solution 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:

publicclassdatabase {
    privateSQLiteDatabasemainDB=null;

    privatebooleanCreateOrOpenDB() {
        try {
            if (mainDB == null || !mainDB.isOpen()) {
                mainDB = Context.openOrCreateDatabase("mainDB", SQLiteDatabase.CREATE_IF_NECESSARY, null);
            }
        } catch (SQLiteException e) {
            returnfalse;
        }
        returntrue;
    }

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

                if (c != null) {
                    if (c.moveToFirst()) {
                        do {
                            Stringdbcolumn= 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

Solution 4:

return readableDatabase
            .query(
                ProductosContract.ProductosEntry.TABLE_NAME,
                ProductosContract.ProductosEntry.ALL_COLUMNS_NAME_ALIAS, null, null, null, null, null
            )

You can specify the columns to retrieve, in that parameter add column name alias to lower case like (Kotlin):

arrayOf("name as 'name'")

So you will get always the lowercase one. Use the lower case or the one you prefer, it will work.