Adding columns to SQLite FTSExample

I am trying to add columns to the SQLite FTSExample.

I modified the creation of the database as such :

[code]Sub SetupDB()
If db.Connect Then
// Create FTS table
db.SQLExecute("CREATE VIRTUAL TABLE ftstest USING fts4(content TEXT, telephone TEXT, zdate TEXT); ")

If db.Error Then
  MsgBox("Error: " + db.ErrorMessage)
End If

// Add data
db.SQLExecute("INSERT INTO ftstest (content, telephone, zdate) VALUES ('John Doe', '123-123-1234', '2016-05-20')")
If db.Error Then
  MsgBox("Error: " + db.ErrorMessage)
End If[/code]

I looked at W3Schools for the syntax of INSERT INTO and that should create three columns as far as I understand. So far no error, so I suppose it is OK.

Now, I am trying to get back my John Doe record together with his telephone and the date. I cannot seem to understand the content of SearchButton Action.

So far the code seems to fetch only two columns, and I do not see how to get the three of them.

Would a kind soul help the ignorant ?

what happens when you attempt to SELECT the record?
HOW (ie. syntax) of how you are selecting the record
Is your insert being COMMITED
Since its a “virtual” table, what is the lifespan?

Are you getting an error?

SELECT * FROM ftstest WHERE zDate='2016-05-20'
SELECT * FROM ftstest WHERE telephone ='123-123-1234'

remember also. SQLite IS case-sensitive, unlike comparisons in native XOJO

My problem is not SELECT, which does find the content field fine on “Doe”, but how do I get the other fields telephone and zdate ?

SELECT * FROM <table> // where * is an SQL wildcard for ALL fields
SELECT content,telephone,zDate  // where you select ONLY those fields you want

OK. I modified the code as such :

[code] ResultsList.DeleteAllRows

Dim rs As RecordSet
Dim sql As String = “SELECT * FROM ftstest WHERE content MATCH '” + SearchField.Text + “’”

sql = "SELECT content, SUM(matched) " + _
"FROM ( " + _
"SELECT content, telephone, zdate, 1 as matched " + _
"FROM ftstest " + _
“WHERE content MATCH '” + SearchField.Text + “’ AND content LIKE '%” + SearchField.Text + "%’ "

Dim subSQL As String
Dim words() As String
words = SearchField.Text.Split(" ")

Dim unionAll As String = " UNION ALL "
For Each w As String In words
subSQL = unionAll + " SELECT content, telephone, zdate, 1 as matched FROM ftstest WHERE content MATCH ‘" + w + "’"

sql = sql + subSql

Next

sql = sql + “)” + _
"GROUP BY content " + _
“ORDER BY SUM(matched) DESC, content”

sql = sql + “)” + _
"GROUP BY content " + _
“ORDER BY SUM(matched) DESC, content, telephone, zdate”

rs = Db.SQLSelect(sql)

If rs <> Nil And Not DB.Error Then
While Not rs.EOF
ResultsList.AddRow(rs.IdxField(1).StringValue.Left(20), rs.IdxField(2).StringValue)
system.DebugLog str(rs.FieldCount)
rs.MoveNext
Wend
rs.Close
Else
MsgBox("Error: " + DB.ErrorMessage)
End If[/code]

No error so far, yet I don’t see how I can get the other fields content in the If rs <> Nil And Not DB.Error Then conditional at the end of the code.

this is probably your problem

 sql = "SELECT content, SUM(matched) " + _
  "FROM ( " + _
  "SELECT content, telephone, zdate, 1 as matched " + _
  "FROM ftstest " + _
  "WHERE content MATCH '" + SearchField.Text + "' AND content LIKE '%" + SearchField.Text + "%' "

this is a SELECT with a SUB Select

the RESULTS are defined here

sql = "SELECT content, SUM(matched) " + _

this references ONLY content, so that is all you will get back

NOTE : if you add the other fields there, then you MUST add them here as well

"GROUP BY content " + _

Dave, THANK YOU so much. Now I see what to do, and it works !

:slight_smile: