« xDev Magazine Issue 1… | Home | MBS Xojo Plugins, ver… »

New SQL Insert and Update functions for our Xojo Plugin

Writing prepared statements to update fields in a database or to make an insert can be challenge for some of our users. So we got an easier way for most inserts/updates, which may help you:

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.
10 07 18 - 13:56