Xojo 2021 Release 3 includes SQLite 3.36.0, which has a few new features, such as:
- RETURNING on DELETE, INSERT and UPDATE commands
- ALTER TABLE DROP COLUMN
- EXPLAIN QUERY PLAN
RETURNING Clause
The RETURNING clause can be used on DELETE, INSERT and UPDATE commands to get a value back after the command has run without having to do a separate query.
For example, you could send an UPDATE command that recalculates a value and have it return the new value like this:
UPDATE sales SET amount = amount * 2 WHERE ID = 1 RETURNING amount
This would now return the newly calculated amount, where as you would previously have to issue a separate SELECT statement to get the amount.
This example would append ” the Great” to the end of all names in the Artist table and return the new name along with the ID:
UPDATE Artist SET Name = Name || + " the Great" RETURNING Name, ArtistId
To access the RETURNING value from Xojo, you’ll want to send the command using SelectSQL instead of ExecuteSQL so that you can use a RowSet to get the values.
DROP Column
SQLite has historically been pretty inflexible when it comes to modify a table’s structure after it has been created. Until recently you could only rename a table and add new columns to it. Back in SQLite 3.25, the ability to rename existing columns on a table was added. And now you can also remove (drop) columns from a table.
You can do this using the standard syntax like this:
ALTER TABLE MyTable DROP COLUMN OldColumn;
Query Plan
A query plan describes how SQLite processes your SQL query. It is really only useful for debugging purposes. If you have a slow-running query it can sometimes be useful to look at the query plan to see if it is pointing out an obvious problem with the query, which might be that the query is doing a table scan rather than using an index.
To see a query plan, you put EXPLAIN QUERY PLAN in front of the SQL query. This features has been in SQLite for a long time, but it has recently gotten some output improvements that might make it easier to use. Be sure to give it a try.
As an example, here is how to get the query plan for a simple query using the AlbumArtist view in the Chinook database:
EXPLAIN QUERY PLAN SELECT * FROM AlbumArtist
The query plan looks like this, with separate rows returned for each evaluation:
To learn more about using SQLite with Xojo, check out these additional resources: Xojo SQLite Database Documentation, Tutorial: SQLite Basics, Blog Post: 3 Steps to Seamlessly Deploy SQLite Projects on Desktop, Web & iOS and Blog Post: Backwards SQLite Backups.