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.
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
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.