Android Question B4XTable CreateDataView Using the LIKE Operator is Not Enforcing Case Sensitivity

Mahares

Expert
Licensed User
Longtime User
When I use the following code, the data is displayed for lower and upper case strings even though the where clause specifies upper and the pragma statement requests it..
B4X:
sql.ExecNonQuery("PRAGMA case_sensitive_like = True")
B4XTable1.CreateDataView($"c1 LIKE '%J%' "$)
It does not differentiate between case sensitive and insensitive. But, on a regular query using the SQLite table or a SQLite view (not the in memory database data table), sensitivity is enforced when we add the pragma statement shown above.
 

Mahares

Expert
Licensed User
Longtime User
The pragma probably doesn't affect the CREATE VIEW command.
Actually, the Create View in SQLite works with the pragma and enforces case, but not with CreateDataView
B4X:
sql.ExecNonQuery("PRAGMA case_sensitive_like = True")
sql.ExecNonQuery("CREATE VIEW myView AS SELECT * FROM mytable")
Dim rs As ResultSet
rs=sql.ExecQuery2("SELECT mycol FROM myView WHERE mycol LIKE ?", Array As String ("%J%"))
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
What is sql? Why isn't it B4XTable.sql1.... ?
In post#3 I am not using B4XTable.sql because the table is not the in memory database table 'data'. But, when I use the CreateDatView of B4XTable it uses the in memory data table. Therefore this displays what I want, which is uppercase,:
B4X:
rs=sql.ExecQuery2("SELECT mycol FROM myView WHERE mycol LIKE ?", Array As String ("%J%")) 'displays only uppercase as it should
But this one shows upper and lowercase:
B4X:
B4XTable1.CreateDataView($"c1 LIKE '%J%' "$)
If it is a limitation of B4XTable CreateDataView, I can work around it by using the SQLite CREATE VIEW, populate a list from the view and repopulate the B4XTable with only uppercase and circumvent B4XTable CreateDataView.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
OK, I got it sorted out with this. All is well now. I thought initially you have to apply the Pragma to the real database , not the in memory one/
B4X:
B4XTable1.sql1.ExecNonQuery("PRAGMA case_sensitive_like = True")
There is no such thing as B4XTable CreateDataView. limitation. It is Mahares limitation.
 
Last edited:
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
OK, I got it sorted out with this. All is well now. I thought initially you have to apply the Pragma to the real database , not the in memory one/
Of course. You call B4XTable.SetData and the data is inserted to the memory table. It doesn't matter where the data comes from.
 
Upvote 0
Top