My Foray into SQLite fulltext search

Databases
1

So I have read the information, pretty sure I have a basic grasp of what is happening… but… the results are less than encouraging.

I will post the entire set up just to make sure I got it all correct:
Window.Open
’ *****************************************************

' -- Preliminary Code --

' :--* None Defined * --:

' Primary Variable Declaration(s)
srchDbase = New SQLiteDatabase

' Function(s) / Logic...
txtProdSearch.SetFocus

Window.Properties
Name: srchDbase
Type: SQLiteDatabase
Default:
Scope: Public

Window.Method
Name: mthAddValues
Parameters:
’ *****************************************************

' -- Preliminary Code --

' :--* None Defined * --:

' Primary Variable Declaration(s)

' :--* None Defined * --:

' Function(s) / Logic...
Try
  srchDbase.ExecuteSQL( "INSERT INTO virtTblProdList ( barcode, prodName, price, tax ) FROM tblProductList" )
  
Catch err As DatabaseException
  MessageDialog.Show( "Error: " + err.Message )
  
End Try

Window.Method
Name: mthSetupSearchDB
Parameters:
***************************************************

' -- Preliminary Code --

' :--* None Defined * --:

' Primary Variable Declaration(s)

' :--* None Defined * --:

' Function(s) / Logic...

Try
  srchDbase.Connect
  
Catch err as DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
  
End Try

// Create FTS5 table
Try
  srchDbase.ExecuteSQL( "CREATE VIRTUAL TABLE virtTblProdList USING fts5( barcode, prodName, price, tax ); " )
  
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
  
End Try

Window.Method
Name: mthSearchByText
Parameters: strSearchParam As String
’ *****************************************************

' -- Preliminary Code --

' :--* None Defined * --:

' Primary Variable Declaration(s)
Var rowSet As RowSet // 
Var strSql As String = "SELECT highlight( virtTblProdList, 0, '<', '>' ) FROM virtTblProdList WHERE virtTblProdList MATCH '" + strSearchParam + "' ORDER BY prodName"

' Function(s) / Logic...
lstBxSearchReturns.RemoveAllRows

Try
  rowSet = srchDbase.SelectSQL( strSql )
  
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  'Return
End Try

If rowSet <> Nil Then
  While Not rowSet.AfterLastRow
    lstBxSearchReturns.AddRow( rowSet.ColumnAt( 2 ).StringValue, rowSet.ColumnAt( 3 ).StringValue, rowSet.ColumnAt( 10 ).StringValue, rowSet.ColumnAt( 11 ).StringValue )
    
    rowSet.MoveToNextRow
  Wend
  rowSet.Close
Else
  
End If

When I run the application and attempt to search I get the following error:

Operation cannot be completed because the database is closed.
This error happens here:

Window.Method
Name: mthSearchByText
Parameters: strSearchParam As String
at this Line --> rowSet = srchDbase.SelectSQL( strSql )

I followed the tutorial but… did I?

2

Wait… I forgot to call the set up method… okay, new errors have cropped up… if I am stuck I’ll be back!

3

Some people… I forgot to call the Add Method… after doing so I get this error

near “FROM”: syntax error

This is the SQL --> srchDbase.ExecuteSQL( “INSERT INTO virtTblProdList ( barcode, prodName, price, tax ) FROM tblProductList;” )

4

Okay, sorted that out BUT now I have this issue…

no such table: tblProductList

This is updated SQL --> srchDbase.ExecuteSQL( “INSERT INTO virtTblProdList SELECT barCode, prodDescrip, sellPrice, taxable FROM tblProductList;” )

tblProductList does exist as I used it with normal tables no problem. What am I doing wrong?

5

Okay, so I created a way to get data into the virtual table… it’s probably not the cleanest but… it’s working… now to deal with an OutOfBounds Exception.

Var recSet As RecordSet = modSel.mthSelProdList( 0, "" )
Var strBarCode, strProdDesc, strSellPrice, strTaxable As String

' Function(s) / Logic...
Try
  While NOT recSet.EOF
    strBarCode = recSet.IdxField( 2 ).StringValue
    strProdDesc = recSet.IdxField( 3 ).StringValue 
    strSellPrice = recSet.IdxField( 10 ).StringValue
    strTaxable = recSet.IdxField( 11 ).StringValue
    
    srchDbase.ExecuteSQL( "INSERT INTO virtTblProdList( barCode, prodDescrip, sellPrice, taxable ) VALUES( '" + strBarCode + "','" + strProdDesc + "','" + strSellPrice + "','" + strTaxable + "')" )
    
    'db.ExecuteSQL("INSERT INTO Employees (Name,Job,YearJoined) VALUES ('Dr.Strangelove','Advisor',1962)")
    
    recSet.MoveNext
  Wend
6

I DID IT!!! IT WORKS!!! I AM … oops, ego getting in the way there. Success. Now to test the crap out of it!

7

The tokenizer is missing from your table definition. You will also want to load icu if you have any unicode characters because icu isn’t loaded by default.