TSTool / Command / NewSQLiteDatabase

Overview
- In-Memory Database
Command Editor
Command Syntax
Examples
Troubleshooting
See Also

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 (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.