New SQL Insert and Update functions for our Xojo Plugin
InsertRecord(TableName as String, Record as Dictionary)
UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
Those functions are available both on SQLDatabaseMBS and SQLConnectionMBS classes. They allow you to pass table name and field values as dictionaries. The keys in the dictionaries must of course match the names of the database fields exactly. We create the SQL statement for you and run it with the given values bound to the parameters. For the update statement, you provide the primary keys in a dictionary to identify the records to update.
Especially for the primary keys, the pair syntax is quite useful: new dictionary("ID":2). This means you create a new dictionary by passing a pair with key and value to be inserted in the new dictionary.
Same code may look like this:
dim db as SQLDatabaseMBS // your database connection
dim d as new Dictionary
d.Value(kFieldPersonFirstName) = FirstName.text
d.Value(kFieldPersonLastName) = LastName.text
db.UpdateRecord(kTablePerson, d, new dictionary("ID":2))
if db.Error then
MsgBox db.ErrorMessage
end if
As always, it's useful to put names of tables and fields into constants, so you may not type them incorrectly and the compiler catches mistyped constant names.