Android Question Why does Cursor.GetInt("ColName") have to be lowercase?

[Widget]

I am going through the UserManual example SqliteLight4 and it appears the Cursor.Getxx will throw an exception if the column name parameter has an uppercase letter.

So this works:
Starter.SupplierIDList.Add(Cursor1.GetInt("rowid")) 'add the ID's to the ID list

but this throws an exception:
Starter.SupplierIDList.Add(Cursor1.GetInt("RowId")) 'add the ID's to the ID list

and this throws an exception:
Starter.SupplierIDList.Add(Cursor1.GetInt("rowID")) 'add the ID's to the ID list

Sqlite identifiers are case insensitive so all of these examples should work fine.
Does anyone know why it doesn't?

Here is the exception:
java.lang.IllegalArgumentException: column 'RowId' does not exist

 

[Erel]

It doesn't need to be lowercase. It is case sensitive. This means that the column name must exactly match the column name you used when you created the table.

Note that you can set aliases in the query and then the column names will actually be the aliases:

Dim crsr As Cursor = sql.ExecQuery("SELECT Id AS ID FROM Grades")
For i = 0 To crsr.RowCount - 1
   crsr.Position = i
   Log(crsr.GetString("ID"))
Next

 

[klaus]

In the SqliteLight4 source code I use:
Starter.SupplierIDList.Add(Cursor1.GetInt2(0)).

I see too that:
Starter.SupplierIDList.Add(Cursor1.GetInt("rowID")) doesn't work and
Starter.SupplierIDList.Add(Cursor1.GetInt("rowid")) works.

I suppose that the problem is that rowid is a special column in SQLite.

The strange thing is that:
Cursor1 = Starter.SQL1.ExecQuery("SELECT rowID FROM Suppliers") works
but
Starter.SupplierIDList.Add(Cursor1.GetInt("rowID")) doesn't work.

After some more investigations it seems that in B4A Getxxx needs the correct spelling of column names.
Example in the same program:
A column is defined with 'Name'.
Cursor1 = Starter.SQL1.ExecQuery("SELECT DISTINCT Name FROM Suppliers") works, of course.
But even
Cursor1 = Starter.SQL1.ExecQuery("SELECT DISTINCT nAme FROM Suppliers") works too

But with
Cursor1 = Starter.SQL1.ExecQuery("SELECT DISTINCT nAme FROM Suppliers")
spnSuppliers.Add(Cursor1.GetString("nAme")) doesn't work
But this works
Cursor1 = Starter.SQL1.ExecQuery("SELECT DISTINCT nAme FROM Suppliers")
spnSuppliers.Add(Cursor1.GetString("Name"))

 

[Widget]

It doesn't need to be lowercase. It is case sensitive.

1) According to SQLite's documentation, the identifiers like table names, column names etc. are case-insensitive and can be referred to as "rowid" or "ROWId" etc. regardless of how they are defined in the schema. Unfortunately B4A not not adhere to this standard. This may cause a problem if the table definitions are created outside of B4A. If the project manager decides one day to rename the column name from "rowID" to "RowId" we are now going to have to ferret out and replace all occurrences of "rowID" with "RowId" in the code otherwise we will get an exception at runtime. All of this extra work is not necessary with other languages that use SQLite. (Project managers can be quite anal about the proper naming conventions.)

This means that the column name must exactly match the column name you used when you created the table.

2) Nope. That's NOT what B4A is doing. The table column was created with the column name "rowID" yet Cursor1.GetInt("rowID") throws an exception. All column names in the Getxx() parameter MUST BE LOWER CASE otherwise it throws an exception regardless of how they were defined originally in the table.

 

[klaus]

The table column was created with the column name "rowID"

In SqliteLight4 the rowID column is not explicitly defined, it is the hidden primary key column.

 

[Widget]

I think the problem is not with case sensitivity of the identifiers in the SQL statement (it seems to work regardless of case), but the problem is with the sub Cursor.Getxx() doing a lookup into a list that probably has only lower case entries. That's my guess. So if you pass anything other than a lowercase column name to Cursor.Getxx() , it won't find the parameter in the lowercase list. To solve the problem Getxx() should set the parameter to lowercase right away so it will be found in the list. At least that's how I see the problem.

 

[LucaMs]

If the project manager decides one day to rename the column name from "rowID" to "RowId" we are now going to have to

... to read column names from a text file, using variables in code

 

[Widget]

... to read column names from a text file, using variables in code

That sounds like a good idea.

I'm not sure if it is better to read the text file entries into individual variables like FldNameRowId or a map? A map would reduce the # of variables defined if it were a large project.

The text file (or table) could load the InternalName=ExternalName into a Map so it would be easy to do a lookup using the program string "ColId" (internal name) to get the actual column name "Col_Id" (external name). It could also be used for table names and database names/directory name and even temporary storage of the current record value when the program is suspended. A code module would have to be written but it won't take much.

I shall have to ponder it some more. Thanks for the suggestion.

 

[klaus]

My investigations confirm Erels answer in post #2.
In the SqlieLight4 program a column is defined with the name 'Name' when the database is created.
GetInt expects GetInt("Name") nothing else.
The rowid column is a special case, I saw in the SQLite documentation that they use 'rowid' which is understood by B4A.

Erel, could this be changed to be the same as in SQLite, case independant ?
If no I will change it in the User's Guide.

 

[LucaMs]

That sounds like a good idea.

I'm not sure if it is better to read the text file entries into individual variables like FldNameRowId or a map? A map would reduce the # of variables defined if it were a large project.

The text file (or table) could load the InternalName=ExternalName into a Map so it would be easy to do a lookup using the program string "ColId" (internal name) to get the actual column name "Col_Id" (external name). It could also be used for table names and database names/directory name and even temporary storage of the current record value when the program is suspended. A code module would have to be written but it won't take much.

I shall have to ponder it some more. Thanks for the suggestion.

Your idea (about maps) is pretty much the same as what I use for routines' obfuscated names (to allow communications between clients and server)

 

[Erel]

Erel, could this be changed to be the same as in SQLite, case ibdependant ?

This how the native API works.

Developers who want to use case insensitive strings can do something like:

Dim crsr As Cursor = sql.ExecQuery("SELECT Id AS ID FROM Grades")
Dim m As Map
m.Initialize
For i = 0 To crsr.ColumnCount - 1
   m.Put(crsr.GetColumnName(i).ToLowerCase, i)
Next

For i = 0 To crsr.RowCount - 1
   crsr.Position = i
   Log(crsr.GetString2(m.Get("ID".ToLowerCase)))
Next

The only way that I can make the columns case insensitive is by creating this map internally in the library and this is something that I prefer not to do as it is rarely required.

 

[klaus]

Thank you, I will change in the User's Guide.

 

[Widget]

Thank you, I will change in the User's Guide.

Klaus,

Erel's solution also solves a problem I've been having with DbUtils.

DBUtils.ExecuteMap and DBUtils.ExecuteListMap when they create the map of the column name=value, it converts the column name to lower case.

Here is a code snippet from ExecuteMap:

For i = 0 To cur.ColumnCount - 1
  res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
Next

This forces me to use map.get("firstname") instead of map.get("FirstName") which is a pain because the columns may be read from the Sqlite table or hardcoded with the capitalized letters so it is easier to read.

The solution is to use:

map.Get("FirstName".ToLowerCase)   'So I can have my cake and eat it too!

I thought I should mention this in case (excuse the pun) someone else has the same problem retrieving column values with DBUtils.

 

[klaus]

Thank you for the clarification.
I had almost never a problem with the case in SQLite queries, I don't use DBUtils, I prefer the 'original' queries.
The only one was with 'rowid'. In this case I changed to GetXXX2 with the column index instead its name.

 

[Widget]

... I don't use DBUtils, I prefer the 'original' queries.

I started off this way too. Then I realized there are differences in accessing SQLite in iOS compared to Android. For example, iOS uses ResultSet and Android uses Cursor. If I restrict my app to accessing the SQLite tables only through a code module like DBUtils which returns the rows in a standard List or Map, then I only need to swap out the code module DBUtils when I change between iOS and Android. DBUtils acts as a middle layer to the database so it will make supporting both OS's much easier (a lot less code to change).

 

[klaus]

DBUtils acts as a middle layer to the database so it will make supporting both OS's much easier (a lot less code to change).

That makes sense !