Android Question SQL Problem using wildcard "%" in a parameter query (Sqlite)

[Diceman]

I am having a problem with a parameter query that is using a wild card in Sqlite.
The SQL.ExecQuery2 is returning the wrong rows.

It looks something like this (quick example-may have typos):
Sql.ExecQuery2("select * from countries where country like ? and population <= ?", array as String("canada/%", 1))

If I use ExecQuery and hard code the "canada/%" into the sql statement, it returns the correct rows.
If I replace the ExecQuery2 parameter with "canada/ottawa" without the "%" it works fine .
Only when using the wildcard character "%" do I have problems. If the 2nd parameter =1, then it returns rows for population=1 and population=3 which is clearly wrong.
If I drop the 2nd parameter "population <= ?" then it works fine.

The wildcard "%" in the 1st parameter is somehow screwing up the filtering of the 2nd parameter in the where clause to limit the rows returned. (I'm getting population=3 for the returned rows when the 2nd parameter is equal to 1).

Has anyone else noticed this. Is there a fix for it?
If no one else has noticed this, then I will create a test program and submit it.

TIA
Using B4A 9.80

 

[Bladimir Silva Toro]

it can works like this:

Sql.ExecQuery2("select * from countries where country like ? and population <= ?", array as String("canada", 1))

Another option could be:

SQL.ExecQuery("select * from countries where country like LIKE '%"&namecity&"%' and population <= 1"

 

[Diceman]

Sorry, I did not mean to imply I was using the "/" as a special escape character in Sqlite. The escape character in Sqlite is a backward slash "\" and I'm not using it here.

The "/" is needed in my example because it is part of the data that is stored in the column. There is a forward slash "/" between "canada" and "ontario" so the data is stored in the column as "canada/ontario/toronto". If I do a wild a wildcard search on ontario then I would search on "canada/ontario/%" and if I want to search on all of canada I would use "canada/%". If I wanted the rows from toronto then I would use "canada/ontario/toronto/%".

I have used Sql.ExecQuery extensively in the past few years. Just not with wildcards all that much. Since the data is coming from the client, I need to use a parameter query. In the example I have given at the start of the thread, the SQL results returned are totally incorrect for the parameter values used. The use of the wildcard "%" in the first parameter is causing the 2nd parameter to be incorrectly executed by the SQL interpreter in B4A.

 

[mangojack]

I have only used the wildcards in situations to find matching records as user is typing data and followed the principal from W3Schools SQL

I might be wrong here , but I do not see how using a wildcard on that field helps here.

So the data is stored in the column as "canada/ontario/toronto".

"canada/%" . . . should return all records where Country = "canada/ontario/toronto"
"canada/ontario/%" . . . should return all records where Country = "canada/ontario/toronto"
"canada/ontario/toronto/% . . . * is the trailing '/' a typo ? . this should return 0 results.


Edit: just to add ..

"ca%" . . . should return all records where Country = "canada/ontario/toronto"
"%/ontario/%" . . . should return all records where Country = "canada/ontario/toronto"

 

[Mahares]

If no one else has noticed this, then I will create a test program and submit it.

All of these tested and work:

MyQuery="select * from countries where country like ? and population <= ?"
    cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("can%", 21000)) ' canada/ also canada/ottawa also canada/ottawa/toronto/
    cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("%/ontario/%", 21000)) 'canada/ontario/toronto
    cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("canada/ontario/%", 21000)) 'canada/ontario/toronto
    cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("canada/%", 21000)) 'same as first one
    cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("canada/%", 1)) 'works

If you are still having problems, you need to export your project as you said.

 

[Diceman]

Thanks for your feedback. I will write an example app to see if I can suss out the problem. If not, I'll post it here.

 

[Diceman]

I might be wrong here , but I do not see how using a wildcard on that field helps here.

"canada/%" . . . should return all records where Country = "canada/ontario/toronto"
"canada/ontario/%" . . . should return all records where Country = "canada/ontario/toronto"
"canada/ontario/toronto/% . . . * is the trailing '/' a typo ? . this should return 0 results.


Edit: just to add ..

"ca%" . . . should return all records where Country = "canada/ontario/toronto"
"%/ontario/%" . . . should return all records where Country = "canada/ontario/toronto"

There is a method to this madness.

Using %/ontario/% would not always retrieve the desired results because there are many places in the world called "ontario". There is the city of Ontario in California for example.
Using "ca%" will also find all rows belonging to Cambodia, Cayman Islands etc.. Did you know there is also a "Canada" in England? And a "London" in Canada? Confusing isn't it?

 

[Xandoca]

what about using more than one filter? one for each part (country, state or province, city).

MyQuery="select * from countries where (country like ? and country like ? and population <= ?"
cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("can%","%ontario%", 21000))

 

[Diceman]

what about using more than one filter? one for each part (country, state or province, city).

MyQuery="select * from countries where (country like ? and country like ? and population <= ?"
cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("can%","%ontario%", 21000))

Sure. I just like the hierarchical approach where the location is stored in one column. The geographical example I gave was from the top of my head and is not part of my actual app and is used solely to demonstrate the error.

 

[aeric]

How about ?

MyQuery="select * from countries where country like ? and population <= ?"
cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As Object("can%ontario%", 21000))

 

[Mahares]

There is a method to this madness.

We are all trying to impress you with our answers that do not seem to please you. You best bet is to reproduce the problem with your database in an example project to avoid this guess game. I have not seen a problem in SQLite that cannot be solved. If you are still dissatisfied and the problems persist , then the problem is in your database and tables design structure that need addressed. Here is another one like the one Eric just posted and works:

cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String("can%ontario%tor%", 5000))  'works

ADDED: You can also use variables. This way you are not limited to one place:

Dim str() As String= Array As String("can", "ont", "tor")
Dim s As String
For i=0 To str.Length-1
    s=$"${s}${str(i)}%"$
Next
cursor1=Starter.SQL1.ExecQuery2(MyQuery, Array As String(s, 5000))  'works

 

[cimperia]

@Diceman , I did that quick and dirty test, and it works as expected for me.
I attach the code that you can drop in a new project as is, and see whether you get the correct results. The results are logged.

#Region  Project Attributes
    #ApplicationLabel: CountriesTest
    #VersionCode: 1
    #VersionName:

    #SupportedOrientations: portrait
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    Public SQL1 As SQL
End Sub

Sub Globals
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.Title = "Countries Test"

    If FirstTime Then
        File.Delete(File.DirInternal, "countries.db")
        SQL1.Initialize(File.DirInternal, "countries.db", True)
        CreateDataBase
    End If
   
    ReadDataBase(1)
    ReadDataBase(2)
    ReadDataBase(3)
    ReadDataBase(4)
End Sub

Sub Activity_Resume   
End Sub

Sub Activity_Pause (UserClosed As Boolean)
End Sub

Private Sub CreateDataBase
    Private sqlStatement As String
   
    sqlStatement = "CREATE TABLE countries (Country TEXT, Population INTEGER)"
    SQL1.ExecNonQuery(sqlStatement)

    sqlStatement = "INSERT INTO COUNTRIES(country, population) VALUES (?, ?)"
    SQL1.ExecNonQuery2(sqlStatement, Array As Object("canada/ontario/toronto", 1))
    SQL1.ExecNonQuery2(sqlStatement, Array As Object("canada/ontario/Burlington", 1))
    SQL1.ExecNonQuery2(sqlStatement, Array As Object("canada/ontario/toronto", 2))
    SQL1.ExecNonQuery2(sqlStatement, Array As Object("canada/ontario/toronto", 3))
    SQL1.ExecNonQuery2(sqlStatement, Array As Object("canada/ontario/Ottawa", 4))
End Sub

Private Sub ReadDataBase (population As Int)

    Log($"Population <= ${population}"$)
    Private cur As Cursor = SQL1.ExecQuery2("select * from countries where country like ? and population <= ?", Array As String("canada/%", population))
   
    For row = 0 To cur.RowCount - 1
        cur.Position = row
        For col = 0 To cur.ColumnCount - 1
            Log(cur.GetString2(col))
        Next
    Next
    cur.Close
    Log("----------------------------------------------")
End Sub

And here are the results

** Activity (main) Create, isFirst = true **
Population <= 1
canada/ontario/toronto
1
canada/ontario/Burlington
1
----------------------------------------------
Population <= 2
canada/ontario/toronto
1
canada/ontario/Burlington
1
canada/ontario/toronto
2
----------------------------------------------
Population <= 3
canada/ontario/toronto
1
canada/ontario/Burlington
1
canada/ontario/toronto
2
canada/ontario/toronto
3
----------------------------------------------
Population <= 4
canada/ontario/toronto
1
canada/ontario/Burlington
1
canada/ontario/toronto
2
canada/ontario/toronto
3
canada/ontario/Ottawa
4
----------------------------------------------
** Activity (main) Resume **

 

[Diceman]

Thanks for everyone's responses. I found the problem and it wasn't obvious.
The B4A app had the table defined in the database that was in the Files directory so it gets copied to the Android app just fine. No problem there. If I run the query on this table it works fine.

Here is what caused the problem. The table can also be generated in the app using something like this (I renamed the table and column names):

CREATE TABLE Location_Totals as select Location, length(Location)-length(replace(Location,'/','')) as Pop from Locations group by Location;

-- The Locations table gets summarized into Location_Totals table using a Group By
CREATE TABLE Locations (
    ID       INTEGER PRIMARY KEY,
    Location STRING,
    Number   INTEGER
);

--This table gets created from the Group By on the table above
CREATE TABLE Location_Totals (
    Location NUM,
    Pop,
    New_Pop  INTEGER   --I added this column manually and filled it with "Pop"
);

The generated table, Location_Totals has a couple of wrong column types generated. The Location column for some reason is set to "NUM" even though Location values are string, like "/Canada/Toronto" etc.

The Pop column is not Integer even though it is based on an Integer calculation.

I didn't know the table had the wrong column types until I used Android Studio and copied the database to my Windows machine and examined the table structure.
We can ignore the Location column entirely because that is not the problem. The problem is the Pop column.
A simple query like this will fail to return the correct rows. It returns only 1 row:

select * from location_totals where pop <= 2;

[Location]  [Pop]  [New_Pop]
/Ireland/Belfast    2    2

Here are all the rows in the table "Location_Totals":

[Location]  [Pop]  [New_Pop]
    0    0
/Canada    1    1
/Canada/Calgary    2    2
/Canada/Calgary/Beaumont    3    3
/Canada/Toronto    2    2
/Canada/Toronto/Park    3    3
/Canada/Toronto/Younge    3    3
/Ireland    1    1
/Ireland/Belfast    2    2
/Toronto/Younge/Smith    3    3

In other database that I've used, the table that is created from the Select statement will create the correct column types based on the columns that were selected or tallied. If I tallied an Integer then the receiving table gets an Integer column. Same with Double. But with Sqlite it creates a typeless column. It's not even a string column because if I reference the column as a string, it still won't return the proper rows.

Example:

select * from location_totals where Pop < '2' order by location;  --It returns rows with Pop=3 and Pop=2

[Location]  [Pop]  [New_Pop]
    0    0
/Canada    1    1
/Canada/Calgary/Beaumont    3    3
/Canada/Toronto/Park    3    3
/Canada/Toronto/Younge    3    3
/Ireland    1    1
/Ireland/Belfast    2    2
/Toronto/Younge/Smith    3    3

I ran a "pragma integrity_check;" and everything is fine (no errors).

So I guess from now on when I create a table from a Group By, I have to create the table ahead of time with the correct column types (or reuse the existing table if it is there) then Insert the Group By results into the empty table.

BTW, I added the "New_Pop" column as an Integer to the table and updated the rows with the existing Pop values, and the query works fine with this Integer column.

select * from location_totals where New_Pop < 2 order by location;

[Location]  [Pop]  [New_Pop]
    0    0
/Canada    1    1
/Ireland    1    1