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.

Annual

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

Annual

I can't imagine why on Earth wouldn't they normalize the column names so they would be case insensitive. SQL itself doesn't care to the case, why should the Cursor ? – Ru

some info about 'why' here: code.google.com/p/android/issues/detail?id=42636 – Stereotropism

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. :)

Tuscany

Great thanks. But actually i am not creating my database using code. I need to use an existing database. Why i need it? because it has some default data. I tried different approaches for "how to provide the database with some default data" & i found it the best way to have a database already created & populated with default data. – Annual

then you need to explore the database and see the table and column name once and then use their variables :) – Tuscany

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

Chromate

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

Dorwin