Run query

18.4.0 released Sep 25, 2023

Run query

Purpose: Execute a query and loop through result set.

run-query \
    [ @<database> ] \
    = <query text> \
    [ input <input parameter> [ , ... ] ] \
    [ ( output [ define ] <column name> [ , ... ] ) | unknown-output ] \
    [ no-loop ] \
    [ error [ define ] <error> ] \
    [ error-text [ define ] <error text> ] \
    [ affected-rows [ define ] <affected rows> ] \
    [ row-count [ define ] <row count> ] \
    [ on-error-continue | on-error-exit ] \
    [ name <query name> ] \
    [ column-count [ define ] <column count> ] \
    [ column-names [ define ] <column names> ] \
    [ column-data [ define ] <column data> ]

    <any code>

[ end-query ]

run-prepared-query \
    ... ( the same as run-query ) ...

run-query executes a query specified with <query text>, which is a dynamic query text (i.e. computed at run-time), or it is a constant string value.

Database

Optional <database> is specified in "@" clause and is the name of the database-config-file. It is optional if there is only one database used (see vv), in which case it is the default.

Output

- output clause
The optional "output" clause is a comma-delimited list of the query's output columns. These column names are used in query-result to get the columns values. The column names do not need to match the actual query column names, rather you can name them anyway you want, as long as they positionally correspond.

If optional "define" is used, then string variables with the same name are created for each column and query's output assigned to them, in which case each name must be a valid C identifier name. For example:

run-query @db = "select firstName, lastName from employees" output define first_name, last_name
    ...
end-loop

is the same as:

run-query @db = "select firstName, lastName from employees" output first_name, last_name
    query-result first_name to define first_name
    query-result last_name to define last_name
    ...
end-loop

"define" is useful in "output" clause to quickly and efficiently create query's output variables in very little code.

Note that the result obtained via "define" is always unencoded. If you need different encoding or other details about the result, use query-result.

- unknown-output clause
If for some reason you don't know the number of output columns of the query (for instance in "SELECT * from ..." kind of query), use "unknown-output" clause, in which case you can use "column-data", "column-names" and "column-count" to get the query metadata in order to obtain the results. If you use neither "output" nor "unknown-output" clause, then your query has no output columns, for example it might be an "INSERT" or "DELETE" statement, or a DDL statement like "CREATE TABLE".

Input

The query's input parameters (if any) are specified with '%s' in the <query text> (note that single quotes must be included). The actual input variables are provided after optional "input" clause (you can instead use semicolon, i.e. ":"), in a list separated by a comma. Each input variable is a string regardless of the actual column type, as the database engine will interpret the data according to its usage. Each input variable is trimmed (left and right) before used in a query. Each <input parameter> may contain a comma if it is a string (i.e. quoted) or it is an expression within parenthesis.

Looping through data

"end-query" ends the loop in which query results are available (see query-result). The optional "no-loop" clause includes implicit "end-query", and in that case no "end-query" can be used. This is useful if you don't want to access any output columns, but rather only affected rows (in INSERT or UPDATE for example), row count (in SELECT) or error code. "end-query" is also unnecessary for DDL statements like "CREATE INDEX" for instance.

Affected rows

The optional "affected-rows" clause provides the number of <affected rows> (such as number of rows inserted by INSERT). The number of rows affected is typically used for DML operations such as INSERT, UPDATE or DELETE. For SELECT, it may or may not be the same as "row-count" which returns the number of rows from a query. See your database documentation for more.

<affected rows> can be created with optional "define".

Rows returned

The number of rows returned by a query can be obtained in optional <row count> in "row-count" clause. <row count> can be created with optional "define".

Error handling

The error code is available in <error> variable in optional "error" clause - this code is always "0" if successful. The <error> code may or may not be a number but is always returned as a string value. <error> is allocated memory. In case of error, error text is available in optional "error-text" clause in <error text>, which is allocated memory.

"on-error-continue" clause specifies that request processing will continue in case of error, whereas "on-error-exit" clause specifies that it will exit. This setting overrides database-level on-error for this specific statement only. If you use "on-error-continue", be sure to check the error code.

<error> and <error text> can be created with optional "define".

Note that if database connection was lost, and could not be reestablished, the request will error out (see error-handling).

Naming query

A query can be named with an optional "name" clause by specifying <query name>. By default, a query is assigned a generated name. When a query is named, you can use other statements such as delete-query that reference the name. A query name must be unique and you will receive an error if it is reused with different queries.

run-prepared-query

run-prepared-query is the same as run-query except for a few important differences; see prepared-statements.

Querying when column names are not known

If you do not know the column names (or even how many of them there are), use optional "column-count" clause to obtain the number of columns in <column count>, "column-names" to obtain the list of column names in <column names>, and "column-data" for the actual query results in <column data>. Use optional "define" to create any of them. To get a column count for instance:

run-query @mydb ="select name, lastName yearOfHire from employee" no-loop column-count define col_count
@Number of columns: <<p-num col_count>><br/>

<column names> is an array of strings containing names of query columns. Column names can be obtained only if "unknown-output" is used. If "define" is used, the array of strings is created, otherwise you need to do it yourself, as in:

char **col_names;
run-query @mydb ="select name, lastName yearOfHire from employee" no-loop column-names col_names

Note that column names are the names of query columns, which may or may not match any actual table column names, since query outputs can have aliases (and they must have them if the output is computed). In the following example, the output will be "employeeFirstName" and "employeeLastName" as they are aliases:

run-query @mydb="select firstName employeeFirstName, lastName employeeLastName from employee" unknown-output \
    column-names to define col_names no-loop
@Column names are <<p-web col_names[0]>>  and <<p-web col_names[1]>>

The array of <column names>, as well as each member of this array, are allocated memory.

"column-data" clause will store a query result into an array of strings <column data>. Typical use of column-data is when a query text is constructed on the fly and the exact list of result columns of a query is unknown (see "unknown-output" clause). In that case, when running the query you can obtain the query metadata, such as number of rows (with "row-count" clause), the number of columns (with "column-count") and the output column names (with "column-names"). This way the query result is described and you can interpret the data obtained with "column-data".

"column-data" gets all the query's data laid out in a single data array, organized by repeating rows. For instance, suppose that table queried has 2 columns and the data is stored into "col_data" array. In that case, "col_data[0]" and "col_data[1]" would be the two columns' values from the first row, "col_data[2]" and "col_data[3]" would be the two columns' values from the second row, "col_data[4]" and "col_data[5]" would be the the columns' values from the third row etc. If "define" is used, then the <column data> variable is created. If it's not used, then you have to define the string array yourself, as in:

char **col_data;
run-query @mydb="select firstName employeeFirstName, lastName employeeLastName from employee" unknown-output \
    column-data to col_data

This example obtains the number of rows and columns, as well as column names for a query. In this case the query is "select * from <table name>", where <table name> is provided at run-time - hence you might not know column names of the result set. Based on the number of rows and columns, all data resulting from the query is displayed in a "for" loop:

void get_table_data (const char *table_name)
{
    //
    // Construct the run-time text of SQL
    //
    (( define qry_txt
    @select * from <<p-out table_name>>
    ))

    //
    // Use unknown-output to demonstrate a solution if
    // output columns of a query are difficult to obtain
    //
    //
    // Run the query and obtain number of rows
    // Obtain number of columns, column names, the actual column data
    // Get actual table data
    //
    run-query @mydb = qry_txt unknown-output row-count define row_count no-loop \
        column-count  define col_count column-names define col_names column-data define col-data


    //
    // In a loop, go through all rows, and for each row, display all column info as well
    // as the actual column data from the table.
    //
    num i;
    num j;
    for (j = 0; j <row_count; j++) {
        //
        // Display columns for each row
        //
        for (i = 0; i <col_count; i++) {
            pf-out "colname %s, coldata %s\n", col_names[i], col_data[j*col_count+i]
        }
    }
}

The array of <column data>, as well as each member of this array, are allocated memory.

Notes

"=" and "@" clauses may or may not have a space before the data that follows. So for example, these are both valid:

// No space after "@" and "="
run-query @db ="select firstName, lastName from employee where employeeId='%s'" output firstName, lastName input empid

// Space after "@" and "="
run-query @ db = "select firstName, lastName from employee where employeeId='%s'" output firstName, lastName input empid

Allocated internals

Internal memory used for a query is allocated memory, which can be released with delete-query.

Examples

Select first and last name (output is firstName and lastName) based on employee ID (specified by input parameter empid):

input-param empid
run-query @db = "select firstName, lastName from employee where employeeId='%s'" output firstName, lastName input empid
    @Employee is <<query-result  firstName>> <<query-result  lastName>>
end-query

Prepared query without a loop and obtain error code and affected rows:

run-prepared-query @db = qry no-loop error define ecode affected-rows define arows input stock_name, stock_price, stock_price

When only a single database is used (a single database-config-file for an application), then "@" clause can be omitted:

run-query =myqry no-loop input stock_name, stock_price, stock_price