Android - SQLite Cursor getColumnIndex () is case sensitive?

Question

When working with SQLiteCursor in Android, I found out that getColumnIndex () behaves case-sensitive, for example:

Example:

Column Name in DB was: Rules cursor.getColumnIndex("Rules") //workes fine cursor.getColumnIndex("rules") //throws error, see the error detail 

The documentation does not say anything about this, see the details here .

LogCat says:

java.lang.IllegalStateException: Failed to read row 0, column -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 am I doing something wrong? I can provide code if required.

Thank you

Answer 1

getColumnIndex () is case sensitive:

Column Name in DB: Rules

cursor.getColumnIndex (" Rules ") // works great

cursor.getColumnIndex (" rules ") // throws an error, see error description

Answer 2

The best and recommended approach using SQLite is that you declare all table and column names 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"; 

therefore, the advantage of this approach is that you do not need to memorize spelling and case, etc. 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);"; 

upon request:

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

or it can be used in the cursor

 int index = cursor.getColumnIndex(COLUMN_MESSAGE); 

this will help you avoid such case sensitivity conflicts and spelling errors. :)

Answer 3

Another way would be to query the database itself for the correct name using PRAGMA table_info , so I wrote a method for all this:

 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 you just need to:

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

and yes, I know that the database will be difficult. But it works and is stable.

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 extract, in this parameter add the alias of the column name to lowercase, for example (Kotlin):

 arrayOf("name as 'name'") 

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