d = sqlquery(sqlite_databaseʃ, result_setʅ, sql_query);
The
sqlquery function executes a query on a
SQLite database and returns a result set in a variety of formats. This function is intended for advanced users who are familiar with writing SQL expressions. While many files used by CSPro applications are text files, there are some files that are stored as SQLite databases, including
CSPro DB data files and
paradata logs. You can query these files with this function.
You can also query SQLite databases using the
Sqlite.exec action, which has more functionality than this function.
The first argument, sqlite_database, must be one of the following:
sqlite_database | Description |
paradata | The query will be executed on the currently open paradata log. This is the same as: paradata(query, ...). |
dictionary_name | The data source pointed to by dictionary_name must be of type CSPro DB, Encrypted CSPro DB, JSON, or Text. The query will be executed on the file associated with this data source. If working with JSON or Text data sources, you can query the file's index. |
file_name | The string expression file_name gives the file name of a SQLite database. The database will be opened, the query executed, and then the database will be closed. This allows you to work with databases maintained outside of CSPro. You can query encrypted databases by specifying a password in a connection string or by having an operator enter the password manually. |
The optional second argument, result_set, indicates the destination for the result set generated following the execution of the query. If you do not provide a result set for your query, the query must be a scalar query (one that returns a single result such as "SELECT COUNT(*) FROM ..."). The result set must be one of the following:
result_set | Description |
record_name | The results of a query can fill a CSPro dictionary's record. The record_name must point to a record in a working storage dictionary. The function will look at the column names of the result set to determine which columns of the results get stored in which items of the record. The results cannot be stored in subitems or multiply occurring items. |
list_name | Because List objects are only one-dimensional, only the first column of results will be stored in list_name, which can be of type numeric or string. Because the size of a List is dynamic, the length of the List will be equal to the number of rows in the result set (up to a maximum of 10,000 rows). |
array_name | You can use one- or two-dimensional Array objects of type numeric or string. When using a one-dimensional array, only the first column of results will be stored in array_name. When using a two-dimensional array, each column of results will be stored in a separate column of array_name. |
Using a working storage record for the results is advantageous because the results are stored in their proper type. Because Lists and Array objects are all of one type (
numeric or
string), some results may be converted to a invalid type (for example, a string value may be stored in a numeric array).
The final argument, sql_query, is a string expression containing the SQL query.
If executing a scalar query, the function returns the queried value. Otherwise, the function returns the number of rows stored in the result set. If there was an error executing the query, then the function returns
default.
numeric numberVerifiedCases = sqlquery(SURVEY_DICT, "SELECT COUNT(*) FROM cases WHERE cases.verified != 0;");
errmsg("%d cases have been verified", numberVerifiedCases);
string SessionQuery =
"SELECT operatorid_info.operatorid, event_start.time, event_end.time "
"FROM session_event session_event_start "
"JOIN event event_start ON session_event_start.id = event_start.id "
"JOIN session_event session_event_end "
"JOIN event event_end ON session_event_end.id = event_end.id AND event_start.session_instance = event_end.session_instance "
"JOIN session_instance ON event_start.session_instance = session_instance.id "
"JOIN session_info ON session_instance.session_info = session_info.id "
"JOIN operatorid_info ON session_info.operatorid_info = operatorid_info.id "
"WHERE session_event_start.action = 1 AND session_event_end.action = 0 "
"ORDER BY event_start.time;";
Array string entrySessions(500, 3);
numeric numberSessions = sqlquery(paradata, entrySessions, SessionQuery);
do numeric ctr = 1 while ctr <= numberSessions
// columns two and three are numeric values, but because they are stored in a
// string array, we must convert them before working with them
numeric startTimestamp = tonumber(entrySessions(ctr, 2));
numeric endTimestamp = tonumber(entrySessions(ctr, 3));
errmsg("Operator '%s' worked on %s at %s for %0.2f minutes",
entrySessions(ctr, 1),
edit("9999-99-99", sysdate("YYYYMMDD", startTimestamp)),
edit("99:99", systime("HHMM", startTimestamp)),
( endTimestamp - startTimestamp ) / 60);
enddo;
This example displays the same results as the previous example but instead stores the information in a working storage record. This multiply occurring record, WS_ENTRY_SESSIONS_REC, contains three items: OPERATOR_NAME, START_TIMESTAMP, and END_TIMESTAMP, with only the first item being alphanumeric. Note that the result set's column names are specified using "AS".
string SessionQuery =
"SELECT operatorid_info.operatorid AS OPERATOR_NAME, event_start.time AS START_TIMESTAMP, event_end.time AS END_TIMESTAMP "
"FROM session_event session_event_start "
"JOIN event event_start ON session_event_start.id = event_start.id "
"JOIN session_event session_event_end "
"JOIN event event_end ON session_event_end.id = event_end.id AND event_start.session_instance = event_end.session_instance "
"JOIN session_instance ON event_start.session_instance = session_instance.id "
"JOIN session_info ON session_instance.session_info = session_info.id "
"JOIN operatorid_info ON session_info.operatorid_info = operatorid_info.id "
"WHERE session_event_start.action = 1 AND session_event_end.action = 0 "
"ORDER BY event_start.time;";
sqlquery(paradata, WS_ENTRY_SESSIONS_REC, SessionQuery);
do numeric ctr = 1 while ctr <= count(WS_ENTRY_SESSIONS_REC)
errmsg("Operator '%s' worked on %s at %s for %0.2f minutes",
strip(OPERATOR_NAME(ctr)),
edit("9999-99-99", sysdate("YYYYMMDD", START_TIMESTAMP(ctr))),
edit("99:99", systime("HHMM", END_TIMESTAMP(ctr))),
( END_TIMESTAMP(ctr) - START_TIMESTAMP(ctr) ) / 60);
enddo;
PROC GLOBAL
function sql DisplayEntrySession(string operatorName, numeric startTime, numeric endTime)
errmsg("Operator '%s' worked on %s at %s for %0.2f minutes",
operatorName,
edit("9999-99-99", sysdate("YYYYMMDD",startTime)),
edit("99:99", systime("HHMM",endTime)),
( endTime - startTime ) / 60);
end;
PROC EXAMPLE
string SessionQuery =
"SELECT DisplayEntrySession(operatorid_info.operatorid, event_start.time, event_end.time) "
"FROM session_event session_event_start "
"JOIN event event_start ON session_event_start.id = event_start.id "
"JOIN session_event session_event_end "
"JOIN event event_end ON session_event_end.id = event_end.id AND event_start.session_instance = event_end.session_instance "
"JOIN session_instance ON event_start.session_instance = session_instance.id "
"JOIN session_info ON session_instance.session_info = session_info.id "
"JOIN operatorid_info ON session_info.operatorid_info = operatorid_info.id "
"WHERE session_event_start.action = 1 AND session_event_end.action = 0 "
"ORDER BY event_start.time;";
sqlquery(paradata, SessionQuery);