I’m about to create a documentation Issue. This is because the SQLite docs for the SQLiteDatabase, and more particularly ExecuteSQL and SelectSQL, seem to assume that an SQL statement such as CREATE, DELETE, INSERT, or UPDATE would always be done using ExecuteSQL, and SELECT would always be done using SelectSQL, the reason being that while SELECT returns a RowSet, the other statements (DELETE and so on), don’t.
However, I am just starting to use the RETURNING clause (see RETURNING), which for example allows me to delete some rows from a database while at the same time returning some data from those deleted rows. This appears to work as hoped for a statement such as:
Var dbh as SQLiteDatabase, reg as RowSet
// Assume dbh initialised
reg = dbh.SelectSQL ("delete from mytable where myid=? returning othercol", idvar)
// Process Rowset of othercol values of the deleted rows.
IOW, SelectSQL should be used for anything that may return a RowSet, and ExecuteSQL otherwise.
The only reason ExecuteSQL and SelectSQL exist is because Xojo can’t override based on return type alone. Otherwise it’d likely be QuerySQL() and QuerySQL() As RowSet. With the normal parameters of course. They both do the same thing, it’s just a matter of whether or not you’re getting a RowSet back.
Usually we have multiple rowsets on other languages, but I mostly only care being able to return the last one, before it I may start a transaction, insert something, update something, commit and return something, all at once if I wanted.