Android Question SQLite SELECT ... LIKE ... UPPER() {SOLVED}

[VictorTandil]

Hi. I have a database in SQLite format and I need to do a partial search with a certain text. I would like to know what is the correct way to do it, since I have tried LIKE '%text%' but apparently does not yield results. Also, I used the UPPER (text) function to simplify the search.

Dim DLG as InputDialog
Dim SQL1 as SQL
Dim Cursor1 as Cursor
'.... SQL1.Initialize ....
'.... DLG.Show
'.... DESC is a TEXT field in MARKERS table
'.... Bla bla bla

Cursor1 = SQL1.ExecQuery("SELECT LAT, LON, DESC FROM MARKERS WHERE UPPER(DESC) LIKE '%" & DLG.Input.ToUpperCase & "'%")

This Code does not produce errors, but neither results.

 

[Ormente]

I see no obvious error, but you should avoid naming columns like reserved words (DESC). So, try to qualify the request :

Cursor1 = SQL1.ExecQuery("SELECT lat, lon, markers.desc FROM markers WHERE UPPER(markers.desc) LIKE '%" & DLG.Input.ToUpperCase & "'%")

(You can also use write the "faulty" column name inside square brackets)

Did you display the built request with log() to check it? And try it "manually" against your database?

 

[klaus]

Try this one:

Cursor1 = SQL1.ExecQuery("SELECT LAT, LON, DESC FROM MARKERS WHERE UPPER(DESC) LIKE '%" & DLG.Input.ToUpperCase & "%'")

At the end "%'" instead of "'%'".

 

[OliverA]

Without @klaus' correction you were looking for a match that ends with your DLG.Input value, instead of containing the value at any point.

 

[LucaMs]

OT.

"I" don't like that query:

1) usually we should write db queries inside classes or at least code modules;
2) you're not checking the user input;
3) you should use ExecQuery2, because it solves some problems, like escaping.

 

[OliverA]

you should use ExecQuery2, because it solves some problems, like escaping.

This ^ 10

 

[Ormente]

Nice catch Klaus! I need to change my glasse

 

[Mahares]

Just another way with parameterized query and collate no case:

Cursor1 = SQL1.ExecQuery2("SELECT LAT, LON, [DESC] FROM MARKERS WHERE [DESC] LIKE ? COLLATE NOCASE"), _
    Array As String( "%" & DLG.Input & "%")

 

[VictorTandil]

Thanks for your suggestions. Finally, it worked in this way:

Sub FindMarkerButton_Click
    Dim Dlg As InputDialog
    Dlg.Hint = "Buscar Marcador"
    Dlg.InputType = Dlg.INPUT_TYPE_TEXT
    Dlg.Show("Ingrese el nombre del Marcador a buscar", "Buscar Marcador", "Aceptar", "Cancelar", "", Null)
    If Dlg.Response = DialogResponse.POSITIVE Then
        If Dlg.Input <> "" Then
            Dim Cursor1 As Cursor
            Dim Sf As StringFunctions
            Sf.Initialize
            Cursor1 = Starter.SQL1.ExecQuery("SELECT LAT, LON, [DESC] FROM MARKERS WHERE [DESC] LIKE '%" & Dlg.Input.ToUpperCase & "%'")
            If Cursor1.RowCount > 0 Then
                For i = 0 To Cursor1.RowCount - 1
                    Cursor1.Position = i
                    Dim Busqueda As String = Cursor1.GetString("DESC")
                    Log("Busqueda: " & Busqueda)
                    Dim Pos As CameraPosition
                    Pos.Initialize(Cursor1.GetString("LAT"), Cursor1.GetString("LON"), gmap.CameraPosition.Zoom)
                    gmap.MoveCamera(Pos)
                    Dim m1 As Marker = gmap.AddMarker2(Cursor1.GetString("LAT"), Cursor1.GetString("LON"), Cursor1.GetString("DESC"), gmap.HUE_BLUE)
                Next
            Else
                Log("No se han encontrado resultados")
            End If
            Cursor1.Close
        Else
            Msgbox("Debe establecer un nombre para la busqueda el Marcador", "Error")
        End If
    End If
End Sub

 

[Ormente]

Congrats
Now that it works you can improve it so it's less vulnerable, because for now you trust the user provided data too much.
As the others said it's better to use escaping, because a user can accidentaly enter a "malformed" string (think: what if she enter a string containing a single quote?), or intentionaly craft a request to hack your app.

To learn more about the risk, look at sql injection on google. Most often it's talked about websites, but any software trusting user input is vulnerable.

 

[VictorTandil]

It is true. I've tried entering a letter only and it throws hundreds of results, which in low-end phones causes a freeze of several seconds. I'm going to narrow the search a bit with certain parameters. Thank you.

 

[OliverA]

low-end phones causes a freeze of several seconds

Switch to SQL with Wait For. That should keep the UI from freezing. See https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content.

 

[KMatle]

If a query is slow, it is always caused by a bad db design (structure and/or missing indexes). Often users retrieve thousands of rows which is bad, too. Just retrieve what an user can handle (max. 50 rows or less at one time). In most cases it doesn't make sense to show all entries starting with e.g. "A" if you get 1000 rows. So if a device takes seconds to do a query there surely is a design problem. Good queries take about max. 0.2 to 0.5 secs (even 0.5 can be seen as bad)