Getting up to speed with SQLite, I thought this might be a good way to save my app preferences.
So I got this DbSend Method which works pretty good. Let me know if you have any suggestions.
It’s basically a single Method which adds, updates, retrieves and deletes any variable. Of any size.
Here’s a link to an example program: http://bit.ly/2WvyTPZ_dbSend
And here’s the actual code:
[code]//Create Module or use existing Module
//Add Property in Module: Name=DB, Type=SQLiteDatabase, Global
//Add Property in Module: name=dbName, Type=String, Default=filename of your sqlite file. Global
//Add Method in Module called dbSend(badge as string, token as string), return String, Global
//Paste ALL this code into Module dbSend
//
//Always Call Method dbSend with two String parameters dbSend(badge,token)
//Insert or Update: requires both badge and token, returns “ok”
//Retrieve: requires badge and “” token, returns token
//Delete: requires badge and “delete” as token, returns “ok”
//Dump: requires “all” for both badge and token, returns all data in Table
Dim dbFile As FolderItem = SpecialFolder.Desktop.Child(dbName) //Note: db on Desktop
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
Dim sql, temp As String
Dim data As RecordSet
//first we open or create database file as need be ==========================
If dbfile.exists Then
If Not DB.Connect Then //opens database file
Return "dbSend Error: " + DB.ErrorMessage
End
Else // does db does not exist createTable
If Not DB.CreateDatabaseFile Then
Return "dbSend Error: " + DB.ErrorMessage
End
End // now that the db is created & opened or just opened
sql = “CREATE TABLE [dbSend]( [Badge] text PRIMARY KEY NOT NULL UNIQUE, [Token] text);”
DB.SQLExecute(sql)
If DB.Error Then
//do nothing, the table was already created
End
// then we process requests as indicated =====================================
If badge =“all” And token = “all” Then //dump: badge and token both = “all” then
sql = “SELECT * FROM dbSend;”
data= DB.SQLSelect(sql)
If DB.Error Then
Return "dbSend Select Error: " + DB.ErrorMessage
Else
If data <> Nil Then
While Not data.eof
//temp = data.field(“badge”).StringValue
temp = temp + data.field(“badge”).StringValue + Chr(9) + data.field(“token”).StringValue + EndOfLine
data.MoveNext
Wend
End
Return temp
End
Elseif badge > “” And token = “delete” Then //delete: both badge and token = “delete”
sql = “DELETE FROM dbSend WHERE badge=” + “’” + badge + “’;”
Elseif badge > “” And token > “” Then //insert or replace data: got badge and token
sql = "INSERT OR REPLACE INTO [dbSend] ([badge],[token]) "
sql=sql + “VALUES (” + “’” + badge + “’, '” + token + “’);”
Elseif badge > “” And token = “” Then //retrieve data: got badge, but token is empty
sql = “SELECT * FROM dbSend WHERE badge=” + “’” + badge + “’;”
data= DB.SQLSelect(sql)
If DB.Error Then
Return "dbSend Select Error: " + DB.ErrorMessage
Else
temp=data.field(“token”)
Return temp
End
Else
Return “dbSend Error: no match” // should never happen
End If
// finally we execute the SQL that do not return data
DB.SQLExecute(sql)
If DB.Error Then
Return "dbSend Execute Error: " + DB.ErrorMessage
Else
Return “ok”
End
Return “nothing here”
[/code]