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

[Mahares]

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

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.

 

[Erel]

The pragma probably doesn't affect the CREATE VIEW command.

 

[Mahares]

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

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%"))

 

[Erel]

This means that you are doing something wrong. What is sql? Why isn't it B4XTable.sql1.... ?

 

[Mahares]

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,:

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:

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.

 

[Mahares]

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/

B4XTable1.sql1.ExecNonQuery("PRAGMA case_sensitive_like = True")

There is no such thing as B4XTable CreateDataView. limitation. It is Mahares limitation.

 

[Erel]

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.