Saving Preferences with SQLite (DbSend Method)

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:

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
Else // does db does not exist createTable
If Not DB.CreateDatabaseFile Then
Return "dbSend Error: " + DB.ErrorMessage
End // now that the db is created & opened or just opened

sql = “CREATE TABLE [dbSend]( [Badge] text PRIMARY KEY NOT NULL UNIQUE, [Token] text);”
If DB.Error Then
//do nothing, the table was already created

// 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
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
Return temp
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
Return temp
Return “dbSend Error: no match” // should never happen
End If

// finally we execute the SQL that do not return data
If DB.Error Then
Return "dbSend Execute Error: " + DB.ErrorMessage
Return “ok”
Return “nothing here”

Works well. I don’t suppose there’s a RowSet version of this for 2019r2 by any chance? I tried editing it myself and totally screwed it up

Usually whats known as “control coupling” is something you want to avoid
You can tell when it occurs when your description of a method says “well if you pass X then it does Y but if you pass A it does B”

Control coupling is usually exhibited when the caller sends some specific parameters to another to control how that other method that is called operates

I’d split this into 4 methods that have clear names and purposes you can say “this does X”

but this is JUST advice

At the risk of being off topic I woul like to ask… @Norman Palardy : I understand what you mean by control coupling… But take this situation., For example in Booking class, there’s a shared method to count the hotel booking’s within a certain period. I would add in it’s signature a
IncludeNoShow as bookean
IncludeCancelled as Boolean

That way I can count the bookings in the period excluding certain types according to my needs at different times.

That obviously affects the results the method is going to return. I guess it falls in the category you explained “control coupling”… How would do it? you would do four methods with exactly the same code except for two “if” to discard bookings marked as no show or cancelled ?
That would be your approach?

you could write a single method and pass it a “where clause”

the code IN the method then doesnt have a bunch of “if then” statements like your first one does to determine how it should operate. And you can describe it simply “it selects data from these tables using the criteria passed in”

thats one possibility


basically the description for a method should be a simple direct unambiguous statement
if you have to start describing them in really vague terms to avoid using AND OR etc then your method may have control coupling issues

You could add a line
token = token.Replace("’","’’")
to escape a ’ in the token string
I ran into this issue recently when I called the method instead of looking at the return values