Sqlite.exec Action

Format

s = CS.Sqlite.exec(db := ..., sql := ...
ʃ, bindings := ...ʅ
ʃ, rowFormat := ...ʅ
ʃ, bytesFormat := ...ʅ)

Argument	Description	Types / Required
db	The database ID returned by Sqlite.open.	number required
sql	The SQL statement(s) to execute.	string, array required
bindings	The bindings for the SQL statement.	array, object not required
rowFormat	The format to return results from a query. The default value is "object".	string not required
bytesFormat	The format of binary data returned when querying a blob. The default value is "dataUrl".	string not required

Description

The Sqlite.exec action executes one or more SQL statements on a SQLite database previously opened by Sqlite.open.

The sql argument specifies the SQL statement. If specified as an array of strings, the action will execute each query in the order present in the array.

You can use SQLite callback functions in your query, which allows you to call some core CSPro functionality as well as user-defined functions marked with the sql directive. For example, this SQL query would return a formatted string containing the date and time of the first CSPro release using the timestring logic function:

SELECT cspro_timestring("%c", 957528000);

Further information about executing queries on databases is available on the SQLite website: sqlite3_prepare_v2, sqlite3_bind_*, sqlite3_step, and sqlite3_column_*.

Specifying Bindings

SQL statements can contain bindings, which allow you to dynamically customize the query without having to modify the query text itself. If specifying more than one SQL statement, only one can contain bindings. Two common ways of specifying bindings include:

"?" (by position): The bindings argument should be specified as an array, with each element of the array bound positionally.
"@name" (by name): The bindings argument should be specified as an object, with each the object's key used to bound its value by parameter name.

The difference in specifying bindings is shown below using JavaScript:

// by position using an array; returns 69.12
CS.Sqlite.exec({
db: dbId,
sql: "SELECT ? + ?;",
bindings: [ 12.34, 56.78 ]
});

// by name using an object; returns 69.12
CS.Sqlite.exec({
db: dbId,
sql: "SELECT @arg1 + @arg2;",
bindings: { "@arg1": 12.34, "@arg2": 56.78 }
});

The array or object used for binding can contain strings, numbers, booleans, or objects. Only objects with a key bytes are processed. These are processed for binary data to bind as a blob. If the object has a bytesFormat key, it will be used to decode the string into binary data. For example, this binds the text "天津" as a blob containing its UTF-8 representation:

// bind binary data; returns "data:;base64,5aSp5rSl"
CS.Sqlite.exec({
db: dbId,
sql: "SELECT ?;",
bindings: [ { bytes: "天津", bytesFormat: "text" } ]
});

Specifying Results

The action returns results in a variety of ways; the rowFormat argument allows you to specify how you want results returned:

"object": Return the results as an array of objects, with the key of each object corresponding to the column name of the results.
"array": Return the results as an array of arrays, with each subarray containing each column's data.
"scalarArray": Return the results as a one-dimensional array when the result only contains a single column. If the result contains multiple columns, the result will appear as if "array" were specified.

The rowFormat argument defaults to "object". When explicitly specified, the results are returned as described above. However, if rowFormat is not specified, there is special processing for scalar results, which are results for queries that result in a single value (a single column and a single row). If returning a scalar result, the result is returned as a number or string, rather than as an object or array.

A few examples demonstrate how results are returned:

No result: The following statement does not have results:

BEGIN TRANSACTION;

In all cases, the action returns undefined.

Scalar value: The following statement results in a scalar value:

SELECT `resident_pop` FROM `census2020`
WHERE `name` = 'Virginia';

rowFormat	Result in JSON
(not specified)	8631393
"object"	[ { "resident_pop": 8631393 } ]
"array"	[ [ 8631393 ] ]
"scalarArray"	[ 8631393 ]

Scalar array: The following statement results in one column, multiple rows:

SELECT `state` FROM `census2020`
WHERE `state` LIKE 'A%';

rowFormat	Result in JSON
(not specified)	[ { "state": "AK" }, { "state": "AL" }, { "state": "AR" }, { "state": "AZ" } ]
"object"	[ { "state": "AK" }, { "state": "AL" }, { "state": "AR" }, { "state": "AZ" } ]
"array"	[ [ "AK" ], [ "AL" ], [ "AR" ], [ "AZ" ] ]
"scalarArray"	[ "AK", "AL", "AR", "AZ" ]

Non-scalar array: The following statement results in multiple columns, multiple rows:

SELECT `state`, `resident_pop` FROM `census2020`
WHERE `state` LIKE 'V%';

rowFormat	Result in JSON
(not specified)	[ { "state": "VT", "resident_pop": 643077 }, { "state": "VA", "resident_pop": 8631393 } ]
"object"	[ { "state": "VT", "resident_pop": 643077 }, { "state": "VA", "resident_pop": 8631393 } ]
"array"	[ [ "VT", 643077 ], [ "VA", 8631393 ] ]
"scalarArray"	[ [ "VT", 643077 ], [ "VA", 8631393 ] ]

Return Value

The action returns undefined if the executed query does not end in a result with rows. If there are rows, the action returns a string, number, array, or object based on the value of rowFormat, as described above.

Exceptions

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

The database ID is not valid.
The SQL statement(s) cannot be compiled or if there is an error executing the statement(s).
More than one SQL statement contains parameters to be bound.
A binding could not be processed.

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);

Example (JavaScript)

let dbId;

try {
// open the SQLite database: census2020.db
dbId = CS.Sqlite.open({
path: "census2020.db"
});

// query for the number of residents per apportioned representative
const residentsPerRepresentativeQuery = CS.Sqlite.exec({
db: dbId,
sql: "SELECT `name`, `resident_pop` / `apportioned_reps` AS `residents_per_rep` " +
"FROM `census2020` " +
"WHERE `apportioned_reps` IS NOT NULL " +
"ORDER BY `residents_per_rep`;"
});

// display the results
print("Number of Residents Per Representative");

residentsPerRepresentativeQuery.forEach(query => {
print(`${query.name} has ${query.residents_per_rep} residents per representative`);
});
}
catch(error) {
print("Error interacting with Census 2020 data: " + error);
}
finally {
// close the database, suppressing any errors
try {
CS.Sqlite.close({
db: dbId
});
}
catch {
}
}

Additional Examples