Sqlite.open Action

Format

d = CS.Sqlite.open(path := ... ‖ name := ...ʃ, openFlags := ...ʅ
ʃ, encryptionKey := ...ʅʃ, encryptionKeyFormat := ...ʅ)

Argument	Description	Types / Required
path	The path of a file to open.	string not required
name	The name of a dictionary or "paradata".	string not required
openFlags	Flags to control how the database is opened. The default value is "read".	string not required
encryptionKey	An encryption key to use when opening or creating a database.	string not required
encryptionKeyFormat	The format of encryptionKey. The default value is "autodetect".	string not required

Description

The Sqlite.open action opens a SQLite database and returns an ID that can be used for future operations. After performing any querying or rekeying operations, close the database so that its resources are properly released.

Either path or name must be specified. Based on the argument, the following kind of SQLite database is opened:

path with a non-blank string: A file on the disk is opened.
path with a blank string: A temporary on-disk database is opened. It will be deleted automatically when closed.
name with a dictionary name: A reference to the SQLite database associated with the data source is returned. Typically this is used to query a CSPro DB data source. Not all data sources have associated SQLite databases.
name with "paradata": The reference to the currently-open paradata log is returned.

When using name, no SQLite database is opened but instead a reference to an already-open SQLite database is returned, so openFlags is ignored. The read/write permissions of the database are maintained from how the database was initially opened by CSPro.

When specifying a file to open, the openFlags argument can be one of the following:

"read": The database is opened in read-only mode, with an error occurring is the database does not exist.
"readWrite": The database is opened in read and write mode, with an error occurring is the database does not exist.
"readWriteCreate": The database is opened in read and write mode, with a new database created if it does not already exist.

When opening or creating an encrypted SQLite database, you must specify an encryption key. The encryptionKey argument specifies the key, with the the optional encryptionKeyFormat argument indicating how to process the encryptionKey argument. If opening an Encrypted CSPro DB data source, you can use Hash.createHash action to convert a password into the encryption key necessary to open the database.

Further information about opening databases is available on the SQLite website: sqlite3_open_v2, sqlite3_key.

Return Value

The action returns a numeric database ID that is used to identify this database in calls to future Sqlite actions.

Exceptions

The action throws an exception if any of its arguments are not specified in a valid form, or:

When opening a file, if the SQLite database could not be opened or created.
When opening a file, if the encryption key is not valid, or if the file is already encrypted and no encryption key is provided.
When opening a dictionary, if there is no SQLite database associated with the data source.
When opening paradata, if no paradata log is open.

Example (CSPro Logic)

// example data available at: https://www.csprousers.org/resources/help/census2020.db

// open the SQLite database: census2020.db
numeric dbCensus2020 = tonumber(CS.Sqlite.open(path := "census2020.db"));

// quit if there was an error opening the database
if dbCensus2020 = default then
exit;
endif;

// query the database...

// string scalar: result is California
string largestState =
CS.Sqlite.exec(db := dbCensus2020,
sql := "SELECT `name` FROM `census2020` ORDER BY `resident_pop` DESC LIMIT 1;");

// numeric scalar: result is 15920696
numeric populationStatesBeginningWithA = tonumber(
CS.Sqlite.exec(db := dbCensus2020,
sql := "SELECT SUM(`resident_pop`) FROM `census2020` WHERE `name` LIKE 'A%';"));

// scalar array: result is California, District of Columbia, Georgia, Pennsylvania, Virginia, West Virginia
List string statesEndingInIa;
statesEndingInIa.updateValueFromJson(
CS.Sqlite.exec(db := dbCensus2020,
sql := "SELECT `name` FROM `census2020` WHERE `name` LIKE '%ia' ORDER BY `name`;",
rowFormat := "scalarArray"));

// close the database
CS.Sqlite.close(db := dbCensus2020);

Additional Examples