Android Question SQL Search on multiple columns

[db0070]

I have a database that has Arabic words, so the text is unicode utf-8 encoded. I want to search for a word in all columns.

I read a specific word from the database, and then search the same word. If I search on one column I get a result. If I search on multiple columns, I don't get the result. What is wrong with my search query for multiple columns?

    Dim tag2 As String
    Dim c As Cursor
    c = sql1.ExecQuery("SELECT * FROM Verses")
    c.Position = 5 'set to row 5
    tag2 = c.GetString("F1")  'gets the word stored in Column F1, row 5
   'now search the same word obtained from the database
    c = sql1.ExecQuery($"Select * FROM Verses WHERE ((F1+F2) LIKE '%${tag2}%')"$) 'this query does not work
    log (c.RowCount) ' gives 0
    c = sql1.ExecQuery($"Select * FROM Verses WHERE ((F1) LIKE '%${tag2}%')"$) 'this query works
    log (c.RowCount) ' gives 1

 

[sorex]

you're trying to add (sum) fields.

you need to use AND

c = sql1.ExecQuery($"Select * FROM Verses WHERE F1 LIKE '%${tag2}%' and F2 like '%${tag2}%' )"$) 'this query does not work

 

[db0070]

Thanks, that works. I have 27 columns, so I guess I have to write the 'ANDs' out 27 times!

 

[sorex]

sounds like bad database design then.

you should've used a seperate table where you will have 27 F records per verses records then you can use a single search.

 

[db0070]

Thanks again for the tip. I am no database expert. I will redesign

 

[rosippc64a]

don't be sad, if I think of a multilingual dictionary, that 27 field maybe good.
so the sql can looks like:

select * from konyvtar where path || file like '%x%'

string fields concatenation works with ||

 

[db0070]

Bear with me with the database design for now - while I can read from the database then search successfully what I just read, the code below does not appear to search for the clicked word in the database correctly. Is the clicked word changed in some way so that it does not match the unicode values in the database? A log of the clicked word appears correct.

Sub Button1_Click
    Dim Cursor As Cursor
    Dim cs As CSBuilder
    Dim s As String
    Cursor = sql1.ExecQuery("SELECT * FROM verses")
    cs.Initialize.Size(20)
    For i = 0 To 35 'Cursor.RowCount-1
        Cursor.Position = i
        For j = 0 To 26
            s = Cursor.GetString2(j)
                If s = Null Then
                    Log("null here")
                Else
                    s = s & " "
                    cs.Clickable("Word", s).Append(s).PopAll.Size(20)
                End If
        Next
    Next
    Label1.Text = cs
    cs.EnableClickEvents(Label1)
End Sub

Sub Word_Click (Tag As String)
    Log($"You have clicked on word: ${Tag}"$)
    Dim c As Cursor
    c = sql1.ExecQuery($"Select * FROM Verses WHERE F1 LIKE '%${Tag}%' AND F2 LIKE '%${Tag}%' AND F3 LIKE '%${Tag}%' AND F4 LIKE '%${Tag}%' 
    AND F5 LIKE '%${Tag}%' AND F6 LIKE '%${Tag}%' AND F7 LIKE '%${Tag}%' AND F8 LIKE '%${Tag}%' AND F9 LIKE '%${Tag}%' AND F10 LIKE '%${Tag}%' 
    AND F11 LIKE '%${Tag}%' AND F12 LIKE '%${Tag}%' AND F13 LIKE '%${Tag}%' AND F14 LIKE '%${Tag}%' AND F15 LIKE '%${Tag}%' AND F16 LIKE '%${Tag}%' 
    AND F17 LIKE '%${Tag}%' AND F18 LIKE '%${Tag}%' AND F19 LIKE '%${Tag}%' AND F20 LIKE '%${Tag}%' AND F21 LIKE '%${Tag}%' AND F22 LIKE '%${Tag}%' 
    AND F23 LIKE '%${Tag}%' AND F24 LIKE '%${Tag}%' AND F25 LIKE '%${Tag}%' AND F26 LIKE '%${Tag}%' AND F27 LIKE '%${Tag}%'"$)

    Log("number of matches is " & c.RowCount)

End Sub

Button_Click reads a few records from the database then loads Label1 with clickable words read from the database.

 

[db0070]

Ok, it is working - changed the ANDs to OR

 

[Mahares]

Ok, it is working - changed the ANDs to OR

Your SQL statement would be easier if you use a parameterized query and an array of values as shown below:

Sub Word_Click (Tag As String)
        Log($"You have clicked on word: ${Tag}"$)
        Dim MyTag(27) As String  'number of fields
        For i = 0 To 26
            MyTag(i)=$"%${Tag}%"$
        Next
        Dim c As Cursor  
        c = SQL1.ExecQuery2("SELECT * FROM Verses WHERE F1 LIKE ? OR F2 LIKE ? OR F3 LIKE ? OR....", MyTag)
        Log("number of matches is " & c.RowCount)
    End Sub

 

[Erel]

Note that there is a similar implementation in B4XTable. It creates an in-memory database and the search field searches all searchable columns.

 

[rboeck]

If you primary want to search for text in a database, you cound think about using full text search in sqlite.

The good: Your sql statement could be as easys as: SELECT * FROM Verses WHERE content MATCH ${Tag}$

The bad: you have to make another type of database, explained here: https://www.b4x.com/android/forum/threads/full-text-search-fts-as-it-applies-to-sqlite.73578/

 

[rraswisak]

another option for you:

if your database is SQLite

Sub Word_Click(Tag As String)
   Dim sField As String = "("
   For i=1 To 27
       sField = sField & "F" & i & " || "
   Next
   sField = sField.SubString2(0,sField.Length-4) & ")"
   
   Dim c As Cursor
   c = SQL1.ExecQuery($"SELECT * FROM Verses WHERE ${sField} LIKE '%${Tag}%'"$)
   Log("Number of match is " & c.RowCount)   
End Sub

if your database is MySQL

Sub Word_Click(Tag As String)
   Dim sField As String = "Concat("
   For i=1 To 27
       sField = sField & "F" & i & ","
   Next
   sField = sField.SubString2(0,sField.Length-1) & ")"
   
   
   Dim c As Cursor
   c = SQL1.ExecQuery($"SELECT * FROM Verses WHERE ${sField} LIKE '%${Tag}%'"$)
   Log("Number of match is " & c.RowCount)   
End Sub

 

[Erel]

Not using parameterized query means that ExecQuery will throw an exception if Tag includes an invalid character.

 

[Mahares]

Sub Word_Click(Tag As String)
Dim sField As String = "("
For i=1 To 27
sField = sField & "F" & i & " || "
Next
sField = sField.SubString2(0,sField.Length-4) & ")"

Dim c As Cursor
c = SQL1.ExecQuery($"SELECT * FROM Verses WHERE ${sField} LIKE '%${Tag}%'"$)
Log("Number of match is " & c.RowCount)
End Sub

This code cannot work because it will give you:
sField = (F1 || F2 || F3 || F4 || F5 || F6 || F7 || F8 || F9 || F10 || F11 || F12 || F13 || F14 || F15 || F16 || F17 || F18 || F19 || F20 || F21 || F22 || F23 || F24 || F25 || F26 || F27)

 

[rraswisak]

I assume that field name on table was set to F1, F2, F3 ... F27 as typed in op's code at post #7, using concatenation is the best approach (i think) for this purpose. Since SQLite does not support Concat function (as MySQL does) the relevant function is using double pipe ( || ). And i think this work as expected.

 

[db0070]

Yes, I have just tried it, the code below does work.

    For i=1 To 27
        sField = sField & "F" & i & " || "
    Next
    sField = sField.SubString2(0,sField.Length-4) & ")"
    c = sql1.ExecQuery($"SELECT * FROM Verses WHERE ${sField} LIKE '%${Tag}%'"$)

 

[db0070]

Thanks all, for the very useful tips and the various ways to do this

 

[Mahares]

And i think this work as expected.

Thank you for your interesting example. Could you please try using a search on this word to see if you SQL statement works: O'Brien

 

[db0070]

I was about to do it, and then spotted the problem - so it will fall over on the '
How to handle that?

 

[Mahares]

Try this:

c = SQL1.ExecQuery2($"SELECT * FROM Verses WHERE ${sField} LIKE ?"$, Array As String ($"%${tag}%"$))

EDIT: I removed the last $ here: ${sField}$. So, the correct is: ${sField}