For years we had requests to add support for Edit and Update functions in the RecordSet to our own classes, SQLDatabaseMBS and SQLConnectionMBS. Well, till now we didn't have an idea how to do this well for all database types.
Recently we got some ideas, so we started to implement them. Let me explain how it works now: First we got a little SQL parser to find the table name in a SQL statement. If the SQL is complex, has some JOIN operator or references multiple tables, we stop and return an empty string. That means we won't allow you to edit the record set from such a statement.
The function is available for you to try as FindTableName in SQLGlobalsMBS. Let us know if you can find some SQL, where it doesn't work as expected. As we use it later with a record set, the SQL has already be parsed and accepted by the database server, so it is always valid SQL.
If you call Edit on the RecordSet, we just check the pre-conditions. We currently don't do any record locking there, but could maybe in future.
Next you may assign values to some fields in the RecordSet. Please only assign data fields and never the primary key fields as that helps us distinguish the fields.
Finally you call the Update method. The plugin looks up the table name. We separate the fields into two categories: Fields you changed are data fields. Also we count all numeric, double, interval, BLob, CLob and date/time fields in this category. What is left will be a few string and integer fields, which are the invariants to identify the record. This usually may include ID fields and UUID fields. You may notice that we don't need to query the schema this way, which is great when you have 15 different target databases.
To be sure we only edit one record, we make a SELECT query to count the records matching the given keys. This should always find exactly one record. But if your primary key is not part of the record set and no other unique key was selected, we find multiple records and return an error. We fill in the key values from the old values in the RecordSet and fill the new values as passed by Xojo. Data type is either given by Xojo as parameter to the plugin or we pick it from the matching field.
If something goes wrong now, we return an error. For examples if you used AS in the SQL to rename a field or an expression, the update statement will fail with unrecognized field name. The call may also fail if we can't identify a key field.
If you like to get a more detailed update statement or control how it is build, you may just use prepared statement with the UPDATE statement you like to use. Otherwise you can collect record data via dictionary and use our InsertRecord/UpdateRecord methods with those dictionaries:
UpdateRecord(TableName as String, Record as Dictionary, Keys as Dictionary)
InsertRecord(TableName as String, Record as Dictionary)
Please try soon with next pre-release. This new feature may need some testing and tuning. For example we may need to tune the auto generated SQL for some database types. But since this is working, converting a Xojo example from SQLiteDatabase to SQLDatabaseMBS is only a handful of lines to change.