Searching Database

[hodgeod]

I have a database with 9 fields...

I want the user to enter something to search for like a search engine. This way only works with one word.
Example: "m42" returns 1 result as I intend
Example: "m 42" does not return any results

Also, if the user enters more than one criteria then I would like it to evaluate each word separately.

Example: "Rocket U.S.S.R" returns 0 results but "Rocket" returns 13 results as expected

Sub DisplayResults
lv_SearchResults.clear
Dim Search As String
Search = edt_SearchOrdnance.Text

      Dim Cursor1 As Cursor
          Cursor1 = SqlOrdnance.ExecQuery("SELECT * FROM " & DBTablename & _
                                            " Where "& ColumnName(1) &" LIKE '%" & Search & "%' OR " _
                                                                     & ColumnName(2) &" LIKE '%" & Search & "%' OR " _
                                                                         & ColumnName(3) &" LIKE '%" & Search & "%' OR " _
                                                                         & ColumnName(4) &" LIKE '%" & Search & "%' OR " _
                                                                         & ColumnName(5) &" LIKE '%" & Search & "%' OR " _
                                                                         & ColumnName(6) &" LIKE '%" & Search & "%' OR " _
                                                                         & ColumnName(7) &" LIKE '%" & Search & "%' " & _
                                                            "Order by " & ColumnName(5) & " ASC")
                                                            
            lbl_numberofresults.Text = ("Displaying " & Cursor1.RowCount & " results.")

Dim bitmap1 As Bitmap
   For i = 0 To Cursor1.RowCount - 1
     Cursor1.Position = i
     bitmap1.Initialize(File.DirAssets, (Cursor1.GetString(ColumnName(7))) )
      lv_SearchResults.AddTwoLinesAndBitmap((Cursor1.GetString(ColumnName(3))), (Cursor1.GetString(ColumnName(2))) & " Diameter: " & (Cursor1.GetString(ColumnName(5))),bitmap1)
  Next
    Cursor1.Close      
      
      Dim p As Phone
      p.HideKeyboard (Activity)
End Sub

I have attached a copy of the database.

The steps that I have taken to research this problem is exploring using a Full Text Search (FTS) and creating a virtual table to use the MATCH function of SQL. I just don't know if B4A supports any of the FTS extensions.

I have also researched the REGEX function to separate the individual words. Then use a do while loop to search and return the results to the list view.

I just don't know what the most efficient approach will be. I also want to choose the best route before educating myself toward the best answer.

My background is using Visual Basic for Applications (Excel) and I am new to the SQL environment. I have read the Beginners manual and taken the SQL tutorials suggested.

Any Help is appreciated.

 

[Erel]

Seems like Android does support FTS module.

See this blog: Android Quick Tip: Using SQLite FTS Tables | Blog
The code is in Java however the queries should be the same as the queries you need to do.

 

[rboeck]

FTS3 search

Hi Erel,

i tried your tipp and changed my search to FTS type. The main work is to create the virtual database, but then it's much easier. My first search logic was, to make internaly three different searches as long i got success. First i looked for the field name1, then for name2 and so on. Now i search in all columns in one step and get an extremly fast answer.
Example: i look for the first name 'Klara' in more then 135.000 records - the answer, more then 40 records, together with filling the listview takes fewer then a half second! Very nice - i can now replace more then 100 lines search code to 10 lines.
Thank you for this tipp and greetings from austria
Reinhard Böck

 

[Erel]

Great. We both learned from this question

 

[hodgeod]

FTS3 Search

Thanks for the reply Eril!

I will work toward educating myself on FTS3 and will post the updated code that I get that shows how I created and searched through the virtual table using the MATCH function.

Finished Code to come soon!

 

[hodgeod]

Updating the Beginner's Guide

Any chance on mentioning the SQLite FTS extension in the next update in the beginner's guide to point beginners toward it a solution toward the programming challenges?

 

[hodgeod]

Finished Code With FTS3 Search

Finished Code Using MATCH function:

Sub DisplayResults
SqlOrdnance.ExecNonQuery ("CREATE VIRTUAL TABLE ordnance_fts USING fts3(OrdnanceUsedWith, _id, Type, Country, Nomenclature, NEW, Diameter, Length, Picture_FileName, tokenize=porter)")
SqlOrdnance.ExecNonQuery ("INSERT INTO ordnance_fts SELECT * FROM ordnance")

lv_SearchResults.clear
Dim Search As String
Search = edt_SearchOrdnance.Text
Dim numofspaces As Int
numofspaces = Search.IndexOf (Chr(32))
      Dim Cursor1 As Cursor
          Cursor1 = SqlOrdnance.ExecQuery("SELECT * FROM ordnance_fts WHERE ordnance_fts MATCH '" & Search & "*'")
                                                            
            lbl_numberofresults.Text = ("Displaying " & Cursor1.RowCount & " results.")
            
Dim bitmap1 As Bitmap
   For i = 0 To Cursor1.RowCount - 1
     Cursor1.Position = i
     bitmap1.Initialize(File.DirAssets, (Cursor1.GetString(ColumnName(7))) )
      lv_SearchResults.AddTwoLinesAndBitmap((Cursor1.GetString(ColumnName(3))), (Cursor1.GetString(ColumnName(2))) & " Diameter: " & (Cursor1.GetString(ColumnName(5))),bitmap1)
  Next
    Cursor1.Close      
      
      Dim p As Phone
      p.HideKeyboard (Activity)
If Cursor1.RowCount = 0 Then
Msgbox ("Do not include spaces in Nomenclature Ex: M42, but include spaces in dimensions Ex: 40 MM", "Hint")
End If
SqlOrdnance.ExecNonQuery("DROP TABLE ordnance_fts")
End Sub

When user enters: Rocket HEAT then it displays the intended results with just one query!

I don't know if creating and dropping the virtual table is the most efficient method with large databases... mine only has 40 test records at this time...

 

[rboeck]

Why do you want do delete and destroy your database each time?
You create the database one time and update, if needed.
Greetings from austria
Reinhard