• <GetStart>
  • CSPro User's Guide
    • The CSPro System
    • Data Dictionary Module
    • The CSPro Language
    • Data Entry Module
    • Batch Editing Applications
    • Tabulation Applications
    • Data Sources
    • CSPro Statements and Functions
      • Statement Format Symbols
      • Alphabetical List of Functions and Statements
      • List of Reserved Words
      • Deprecated Features
      • Declaration Statements
      • Symbol Functions
      • Item Functions
      • Array Object
      • Audio Object
      • Barcode and QR Codes
      • Case Object
      • Document Object
      • File Object
      • Freq Object
      • Geometry Object
      • HashMap Object
      • Image Object
      • List Object
      • Map Object
      • Path
      • Pff Object
      • SystemApp Object
      • ValueSet Object
      • Program Control Statements
      • Assignment Statements
      • Data Entry Statements and Functions
      • Batch Edit Statements
      • Numeric Functions
      • String Functions
      • Multiple Occurrence Functions
      • General Functions
        • Compress Function
        • Decompress Function
        • diagnostics Function
        • Encode Function
        • ErrMsg Function
        • ExecSystem Function (Desktop)
        • ExecSystem Function (Mobile)
        • ExecPFF Function
        • GetProperty Function
        • GetLabel Function
        • GetLanguage Function
        • GetSymbol Function
        • GetValueLabel Function
        • hash Function
        • htmldialog Function
        • InValueSet Function
        • Invoke Function
        • IsChecked Function
        • loadsetting Function
        • LogText Function
        • MaxValue Function
        • MinValue Function
        • paradata Function
        • PathConcat Function
        • PathName Function
        • savesetting Function
        • SetLanguage Function
        • SetProperty Function
        • SetValueSet Function
        • SetValueSets Function
        • Special Function
        • sqlquery Function
        • Stop Function
        • SysParm Function
        • tr Function
        • Trace Function
        • UUID Function
        • View Function
        • Warning Function
      • Date and Time Functions
      • External File Functions
      • Synchronization Functions
    • Templated Reporting System
    • HTML and JavaScript Integration
    • Action Invoker
    • Appendix
  • <CSEntry>
  • <CSBatch>
  • <CSTab>
  • <DataViewer>
  • <TextView>
  • <TblView>
  • <CSFreq>
  • <CSDeploy>
  • <CSPack>
  • <CSDiff>
  • <CSConcat>
  • <Excel2CSPro>
  • <CSExport>
  • <CSIndex>
  • <CSReFmt>
  • <CSSort>
  • <ParadataConcat>
  • <ParadataViewer>
  • <CSCode>
  • <CSDocument>
  • <CSView>
  • <CSWeb>

sqlquery Function

Format
d = sqlquery(sqlite_databaseʃ, result_setʅ, sql_query);
Description
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_databaseDescription
paradataThe query will be executed on the currently open paradata log. This is the same as: paradata(query, ...).
dictionary_nameThe 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_nameThe 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_setDescription
record_nameThe 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_nameBecause 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_nameYou 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.
Return Value
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.
Example - Scalar Query on CSPro DB File
numeric numberVerifiedCases = sqlquery(SURVEY_DICT, "SELECT COUNT(*) FROM cases WHERE cases.verified != 0;");
errmsg("%d cases have been verified", numberVerifiedCases);
Example - Query on CSPro DB File, Storing Results in List
List string verifiedCaseKeys;

sqlquery(DICT_NAME, verifiedCaseKeys, "SELECT cases.key FROM cases WHERE cases.verified != 0;");

do numeric ctr = 1 while ctr <= verifiedCaseKeys.length()
    errmsg("Verified case key: %s", verifiedCaseKeys(ctr));
enddo;
Example - Query on Paradata Log, Storing Results in Array
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;
Example - Query on Paradata Log, Storing Results in Working Storage Record
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;
Example - Using Callback Functions
Adding the directive sql after the function keyword when defining a user-defined function indicates that the query can call back into CSPro logic. In the following example, the same results are displayed as in the previous two examples but without having to store the result set.
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);
See also: Sqlite.exec Action, SQLite Callback Functions, CSPro DB File Format, paradata Function