jSqliteCursor and jSqliteDataAccess case sensitive problem

[dseligo]

Consider table created like in SQlite like this: 'create table mytable (myvalue integer)', that contains some positive integers.
If I select them and use jSqliteCursor's method to get values like this:

Code: Pascal  [Select][+][-]

1. somevar := myCursor.GetValueAsInteger('MyValue')

I will get -1, although there are no negative numbers in the table.

That is because column names supplied to 'GetValueAs...' methods of jSqliteCursor and jSqliteDataAccess are treated as case sensitive.
SQlite itself is ignorant of case in column names, but Java function getColumnIndex in the Cursor class treat column names as case sensitive.

Problem is that 'GetValueAs...' methods return -1 (or empty string for GetValueAsString) in case that supplied case doesn't match one in the SQlite table. That is also true if you try to get values from non-existent columns.

I suggest that exception is created in this case or that getColumnIndexOrThrow is used in the underlying Java code instead of getColumnIndex.

[jmpessoa]

I suggest that exception is created in this case or that getColumnIndexOrThrow is used in the underlying Java code instead of getColumnIndex.....

Can you put here the  code to fix this issue?

Thank you!

[dseligo]

Here is how I fixed issue for GetValueAsInteger. If you say this is correct, I will prepare and post fixes for other cursor methods too.

This is GetValueAsInteger function on Java side (in fpc-lazarus\ccr\lamw\android_wizard\smartdesigner\java\jSqliteCursor.java):

Code: Java  [Select][+][-]

1.     public int GetValueAsInteger(String colName) {
2.         if (mCursor == null) return -1;
3.  
4.         int columnIndex = mCursor.getColumnIndex(colName);
5.  
6.         if ((columnIndex < 0) || (columnIndex >= mCursorColumnCount) ||
7.                 (mCursorPos  < 0) || (mCursorPos >= mCursorRowCount)) return -1;
8.  
9.         return mCursor.getInt(columnIndex);
10.     }

Here is how I propose to change this function, so that it raises exceptions:

Code: Java  [Select][+][-]

1.     public int GetValueAsInteger(String colName) {
2.         if (mCursor == null)
3.           throw new IllegalArgumentException("Cursor is null.");
4.  
5.         int columnIndex = mCursor.getColumnIndexOrThrow(colName);
6.  
7.         if ((columnIndex < 0) || (columnIndex >= mCursorColumnCount) ||
8.                 (mCursorPos  < 0) || (mCursorPos >= mCursorRowCount))
9.           throw new IllegalArgumentException("Illegal column index or cursor position.");
10.  
11.         return mCursor.getInt(columnIndex);
12.     }

Change is also needed at the end of function jni_func_t_out_i (in fpc-lazarus\ccr\lamw\android_bridges\androidwidget.pas) - otherwise exception is just 'swallowed' here:

Code: Pascal  [Select][+][-]

1.   _exceptionOcurred:
2.   If jni_ExceptionOccurred(env) then
3.     raise Exception.Create(exceptionInfo); // I added this line

I attached my testing app.