Skip to content

TSTool / Command / NewSQLiteDatabase


Overview

The NewSQLiteDatabase command creates a new, empty, SQLite database. SQLite databases are single file databases that are useful for creating, manipulating, and publishing datasets. SQLite can also create an in-memory database that facilitates data processing. TSTool tables can be read from a database using ReadTableFromDataStore command and written to a database using WriteTableToDataStore command. The following are technical considerations:

  • database names typically have .db extension
  • a database file that is in use cannot be written over; therefore any software using an existing database file should close the connection before recreating
  • if appropriate for a workflow, use the RemoveFile or CopyFile commands to handle an existing database file before creating a new database with the same name
  • use a CloseDataStore command to close a previously created (and opened) datastore, for example if rerunning a command file
  • the datastore is dynamically created as a GenericDatabase using ODBC/JDBC connection

In-Memory Database

A file database may perform slowly on database inserts due to the need to write to disk and the relatively slow speed of disk seek and write (compared to in-memory operations). Therefore, if performing many inserts, such as when populating a database for distribution, it may be better to create an in-memory database by specifying Memory as the database filename. Then, when inserts are complete, write the database to a filename using a database backup, for example, by running a command similar to the following (note that backup to must be lower case):

RunSql(DataStore="DatastoreName",Sql="backup to ${WorkingDirPortable}/db/thedatabase.db")

Command Editor

The command is available in the following TSTool menu:

  • Commands / Datastore Processing

The following dialog is used to edit the command and illustrates the command syntax.

NewSQLiteDatabase command editor

NewSQLiteDatabase Command Editor (see full-size image)

Command Syntax

The command syntax is as follows:

NewSQLiteDatabase(Parameter="Value",...)

Command Parameters

Parameter             Description Default          
DataStore
required
DataStore name for the database that will be created. Can be specified using processor ${Property}. None – must be specified.
DatabaseFile The name of the database file as full path or path relative to the command file. A file extension of .db is typical. Specify as Memory to create an in-memory database that will be active while the TSTool session is active. Rerunning a command file will recreate the database. Use a datastore configuration file rather than this command if the database should be available throughout multiple TSTool sessions. None - must be specified.

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.

See Also