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

 

[db0070]

This gives me this error

android.database.sqlite.SQLiteException: unrecognized token: "$" (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM Verses WHERE (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)$ LIKE ?

 

[db0070]

spotted the error - removed the extra $ at the end of sField to this:-

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

And now it works! Thanks

 

[rraswisak]

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

no need to try since that will raise error if using ExecQuery method, use ExecQuery2 and send data as parameter... it is more safer