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 |
Either path or name must be specified. Based on the argument, the following kind of SQLite database is opened:
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.
The action returns a numeric database ID that is used to identify this database in calls to future
Sqlite actions.
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 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);