Android Question SQL Search on multiple columns

db0070 · Nov 20, 2019

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?

B4X:

    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 · Nov 20, 2019

you're trying to add (sum) fields.

you need to use AND

B4X:

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

db0070 · Nov 20, 2019

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

sorex · Nov 20, 2019

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 · Nov 20, 2019

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

rosippc64a · Nov 20, 2019

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 · Nov 20, 2019

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.

B4X:

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 · Nov 20, 2019

Ok, it is working - changed the ANDs to OR

Mahares · Nov 20, 2019

db0070 said:
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:

B4X:

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 · Nov 20, 2019

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

rboeck · Nov 20, 2019

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 · Nov 21, 2019

another option for you:

if your database is SQLite

B4X:

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

B4X:

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 · Nov 21, 2019

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

Mahares · Nov 21, 2019

rraswisak said:
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 · Nov 21, 2019

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 · Nov 21, 2019

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

B4X:

    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 · Nov 21, 2019

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

Mahares · Nov 21, 2019

rraswisak said:
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 · Nov 21, 2019

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

Mahares · Nov 21, 2019

Try this:

B4X:

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}

Android Question SQL Search on multiple columns

Active Member

Expert

Active Member

Expert

Active Member

Active Member

Active Member

Active Member

Expert

B4X founder

Well-Known Member

Active Member

B4X founder

Expert

Active Member

Active Member

Active Member

Expert

Active Member

Expert

Privacy & Transparency

Privacy & Transparency