SQL - Commit

GSAK (Geocaching Swiss Army Knife)

SQL - Commit

Warning: The SQL command was the first introduction of SQL to the macro language. It has many limitations and quirks and is now deprecated. It is provided for here for backwards compatibility only. It is very much recommended that you use the more complete and robust SQLite database engine. See the SQLite() function.

Allows you to save in-memory tables to disk.

Syntax:
COMMIT

All data handling is done in memory and nothing is saved to disk until you issue the COMMIT command.

Whenever you make a change to a table with ALTER TABLE, UPDATE, INSERT, or DELETE, the change flag of the table is set. Only tables that have the change flag set will be saved. The change flag is reset after saving.

Only disk based tables are saved (tables that are associated with disk file names). That is, a table you created via SQL with the CREATE command, or create externally with a function like DbToSql()

The COMMIT command does not apply to "non disk" tables which have been created with the ASSIGN TO sql statement. To commit (save) such tables, use SAVE TABLE to convert these to disk based tables.

If the table is already created on disk and you don't use any commands that would change the contents of the Table (use only SELECT statements for example) then there is no need to use COMMIT

Example

# update all traditional cache type to found
$status = sql("update caches set Found='T' where CacheType = 'T' ","")

# now make sure update is written to disk
$status = sql("commit","")

Note: You can use the RELEASE and COMMIT commands to simulate "transactions". So you could do a whole bunch of update SQLs, but only COMMIT the updates to disk at the end of the "good" transaction. If there was an error condition detected in your macro, you could issue a RELEASE command which would effectively "Roll Back" all your updates.

# update all traditional cache type to found
$status = sql("update caches set Found='T' where CacheType = 'T' ","")

# update last GPX date to today
$status = sql("update caches set LastGPXDate='" + DateToSQL($_Today) + "'","")

# Commit changes if OK, otherwise roll back
Input msg="Changes made to SQL table caches, are you sure you want to update? (Y or N) " Default=Y
If Upper($Result) = "Y"
$status = sql("Commit","")
else
$status = sql("Release Table caches","")
EndIf