M Mahares Expert Licensed User Longtime User Mar 14, 2021 #1 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.. Code: 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.
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.. Code: 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 B4X founder Staff member Licensed User Longtime User Mar 14, 2021 #2 The pragma probably doesn't affect the CREATE VIEW command. Upvote 0
M Mahares Expert Licensed User Longtime User Mar 14, 2021 #3 Erel said: The pragma probably doesn't affect the CREATE VIEW command. Click to expand... Actually, the Create View in SQLite works with the pragma and enforces case, but not with CreateDataView Code: 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
Erel said: The pragma probably doesn't affect the CREATE VIEW command. Click to expand... Actually, the Create View in SQLite works with the pragma and enforces case, but not with CreateDataView Code: 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 B4X founder Staff member Licensed User Longtime User Mar 14, 2021 #4 This means that you are doing something wrong. What is sql? Why isn't it B4XTable.sql1.... ? Upvote 0
M Mahares Expert Licensed User Longtime User Mar 14, 2021 #5 Erel said: What is sql? Why isn't it B4XTable.sql1.... ? Click to expand... 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,: Code: 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: Code: 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
Erel said: What is sql? Why isn't it B4XTable.sql1.... ? Click to expand... 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,: Code: 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: Code: 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.
M Mahares Expert Licensed User Longtime User Mar 14, 2021 #6 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/ Code: B4XTable1.sql1.ExecNonQuery("PRAGMA case_sensitive_like = True") There is no such thing as B4XTable CreateDataView. limitation. It is Mahares limitation. Last edited: Mar 14, 2021 Upvote 0
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/ Code: B4XTable1.sql1.ExecNonQuery("PRAGMA case_sensitive_like = True") There is no such thing as B4XTable CreateDataView. limitation. It is Mahares limitation.
Erel B4X founder Staff member Licensed User Longtime User Mar 14, 2021 #7 Mahares said: 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/ Click to expand... 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
Mahares said: 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/ Click to expand... Of course. You call B4XTable.SetData and the data is inserted to the memory table. It doesn't matter where the data comes from.
