Full Text search

[John_McDonald]

Okay, so I looked up my old post and recreated what I need to do a full text search… but, I apparently have forgotten some aspect of how to get it to work. Maybe it’s where I am creating the virtual table. Not sure.

So I have a form/window that opens with only a ListBox on it. The ListBox is supposed to be loaded with only the rows that match my search criteria. BUT I am not even getting there.

The Form/Windows is set up as follows:
frmAddressSearch.Properties.Name = srchDbse
frmAddressSearch.Properties.Type = SQLiteDatabase
frmAddressSearch.Properties.Default =
frmAddressSearch.Properties.Scope = Public

Next in the Form/Window.Opening EventHandler I have this

// Variables defined here...
 
// The code that does magical stuff...
frmAddressSearch.mthCreateVtble
frmAddressSearch.mthInsert
frmAddressSearch.mthSearch( frmUpdateCustomer.txtAddress.Text )

Next is each Method called from the Form/Window.Opening

First is mthCreateVtble

// Variables defined here...
Var srchDbse As New SQLiteDatabase

// The code that does magical stuff...
Try
  srchDbse.Connect
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

// Create FTS5 table
Try
  srchDbse.ExecuteSQL( "CREATE VIRTUAL TABLE vTblCustomer USING fts5( rNum, address, city ); " )
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

Next is mthInsert, where I insert the data for the search…

// Variables defined here...
Var rsData As RowSet = dbMeters.SelectSQL( "SELECT * FROM tblCustomer ORDER BY rNum ASC" )
Var sRnum, sAddress, sCity As String

// The code that does magical stuff...
Try
  For Each row As DatabaseRow In rsData
    sRnum = rsData.Column( "rNum" ).StringValue
    sAddress = rsData.Column( "address" ).StringValue
    sCity = rsData.Column( "city" ).StringValue
    srchDbse.ExecuteSQL( "INSERT INTO vTblCustomer( rNum, address, city ) VALUES( '" + sRnum + "','" + sAddress + "','" + sCity + "' )" )
  Next
Catch err As DatabaseException
  MessageDialog.Show( "Error: " +err.Message )
  Return
End Try

And then last, the actual search

// Variables defined here...
Var rsData As RowSet
Var sSql As String = "SELECT highlight( vTblCustomer, 0, '<', '>' ) FROM vTblCustomer WHERE vTblCustomer MATCH'" + sQrySrc + "' ORDER BY rNum"

// The code that does magical stuff...
lstBxAddressRtns.RemoveAllRows

Try
  rsData = srchDbse.SelectSQL( sSql )
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  'Return
End Try

If rsData <> Nil Then
  While Not rsData.AfterLastRow
    lstBxAddressRtns.AddRow( rsData.ColumnAt( 0 ).StringValue, rsData.ColumnAt( 1 ).StringValue, rsData.ColumnAt( 2 ).StringValue )
    rsData.MoveToNextRow
  Wend
  rsData.Close
Else
  
End If

That’s all the code and it’s contained in the search form/window. But the error is happening here… in mthInsert. I am not certain why.

[Beatrix_Willius]

@John_McDonald

Var srchDbse As New SQLiteDatabase

This is your problem. SearchDatabase needs to be a property of your class and not a local variable. If you check the debugger for the NOE you will see that SearchDatabase is nil. Because the code compile you probably have both a local variable and a class property.

I’ve always set the tokenizer when using fts like this:

Dim sql As String = “CREATE VIRTUAL TABLE bodyindex USING fts5(MessageContent, tokenize = ‘trigram’);”

Your sql should be a prepared statement. Using fts for short data like address and city is overkill. fts for numbers doesn’t work at all.

[John_McDonald]

Okay I don’t know where to put the various bits and pieces. I read the tutorials and even my own post from 2021, and nothing about where to put the code. I am assuming from your post that I have to create a Class ( I am not strong in this … yet )

So I did
Class.Name = clsSrchDbse
Class.Super = SQLiteDatabase
Class.Interfaces =

Then I added the Method to create the table in the Class, but that doesn’t seem to work…

Class.Properties.Name = srchDbse
Class.Properties.Type = SQLiteDatabase
Class.Properties.Default =
Class.Properties.Scope = Public

I’m trying to find my old code, so I can see how I set it up. But the tutorials just tell me the syntax not where or how to create this. So I am stumped again.

[Beatrix_Willius]

Your class doesn’t need a super:

Your SQLiteDatabase needs to go here:

[John_McDonald]

Okay, my brain skull finally figured it out and now it works. Just need to do a little tweaking but I am on the right path. Thank you.