SQLite – d – GUI Database management

The executable has a number of built-in commands to support the management of the database being processed by the SQLite database machine. Specifically: dbOpen, dbSave, dbSaveAs , dbSaveCopy, dbClose.

The command help("db") will display some information about these commands in the Help Panel.

These commands can also be triggered by menu options under the menu File.

——————-
dbOpen


1) Opens a new or existing data. The database can be either file-based or memory. If a database is already open then an error will be triggered.

Use the command dbOpen ("memory") to create a new (empty) database that resides only in memory. You can save the database later to a disk file using the dbSaveAs command.
 
I usually open an existing (file based) database to memory in such a way that changes are not automatically done to the disk file. Then if I am unhappy with the changes to the database I can simple throw the memory copy away and start again. 

dbOpen( "memory" , <path/name of database file>)

e.g.   dbOpen ( "memory", "c:\myDatabase.db3")

Note: “memory” is used here as a keyword.

If the file specified by <file specifier> does not exist then an error will occur.

To open an existing (file based) database, and have any changes immediately done to the disk file:

dbOpen  ( "file" , <file specifier>)

Note: “file” is used here as a keyword.

If the file specified by <file specifier> does not exist an error will occur.

2) If no parameters are provided then it returns a flag indicating if the SQLite database currently has a database.

  • 0   = no database
  • -1 = file based
  • 1   = memory

——————-

dbSaveAs

A copy of the database will be saved to a user specified file.

e.g.   dbSaveAs (  "c:\myDatabase.db3")

If the specified file already exists then it will trigger an error. You can force the command to overwrite the file (if it exists) by setting the second parameter to a non-zero value.

e.g.   dbSaveAs (  "c:\myDatabase.db3" , 1 )

Note: if this command is successful, then the executable will remember that the current database is (now) related to the disk file. This means that a future dbSave will automatically be done to the file specified by dbSaveAs

——————-

dbSave

If the current database is disk-backed, even if it was loaded as a memory copy, then the current database will be saved to that disk file.

e.g.   dbSave

If the current database is not disk-backed then an error is triggered.

——————

dbSaveCopy

A copy of the database will be saved to a user specified file.

e.g.   dbSaveCopy (  "c:\myDatabase.db3")

If the specified file already exists then it will trigger an error. You can force the command to overwrite the file (if it exists) by setting the second parameter to a non-zero value.

e.g.   dbSaveCopy (  "c:\myDatabase.db3" , 1 )

Note: unlike dbSaveAs this command does not set the current database to be related to the disk file.

——————-

dbClose

If there is no open database then dbClose returns without error.

If there is an open file-based database, then the database will be gracefully closed.

If there is an open memory-based database, and if the command was invoked from a script, then the memory database will be discarded and closed.


If there is an open memory-based database, and if the command was invoked from the command line ( Utility or SQL command =>  input field) then the user will be offered the opportunity to either:

  • cancel the close operation
  • discard and close the memory database