Run query

Purpose: Execute a query and loop through result set.

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

     <any code>

 [ end-query ]

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

run-query executes a query specified with string <query text>.

Database

<database> is specified in "@" clause and is the name of the database-config-file. If ommited, your program must use exactly one database (see --db option in gg).

Output

- output clause
"output" clause is a comma-delimited list of the query's output columns. 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. String variables with the same name are created for each column name and query's output assigned to them. For example:

 run-query @db = "select firstName, lastName from employees" output first_name, last_name
     @First name <<p-out first_name>>
     @Last name <<p-out last_name>>
 end-loop

Note that the output is by default web-encoded. You can set the encoding of column output by using either "noencode" (for no encoding), "urlencode" (for URL-encoding) or "webencode" (for web-encoding) clause right after column name (see encode-web, encode-url for description of encodings). For example, here the first output column will not be encoded, and the second will be URL-encoded:

 run-query @db = "select firstName, lastName from employees" output first_name noencode, last_name urlencode
     @First name <<p-out first_name>>
     @Last name <<p-out last_name>>
 end-loop

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 parameters are provided after "input" clause (you can instead use semicolon, i.e. ":"), in a comma-separated list. 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.

Looping through data

"end-query" statement ends the loop in which query results are available through "output" clause. "no-loop" clause includes implicit "end-query", and in that case no "end-query" statement can be used. This is useful if you don't want to access any output columns (or there aren't any), 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

"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.

Rows returned

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

Error handling

The error code is available in <error> variable in "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. In case of error, error text is available in "error-text" clause in <error text> string.

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

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

Space before = and @

"=" 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

run-prepared-query

run-prepared-query is the same as run-query except that a <query> is prepared. That means it is pre-compiled and its execution plan is created once, instead of each time a query executes. The statement is cached going forward for the life of the process (with the rare exception of re-establishing a lost database connection). It means effectively an unlimited number of requests will be reusing the query statement, which generally implies higher performance. Note that databases do not allow prepared queries for DDL (Data Definition Language), as there is not much benefit in general, hence only DML queries (such as INSERT, DELETE etc.) and SELECT can be prepared.

In order for database to cache a query statement, Gliimly will save query text that actually executes the very first time it runs. Then, regardless of what query text you supply in the following executions, it will not mutate anymore. It means from that moment onward, the query will always execute that very same query text, just with different input parameters. In practicallity it means that <query> should be a string constant if you are using a prepared query (fortunately that is usually the case).

In some cases, you might not want to use prepared statements. Some reasons may be:

your statements are often changing and dynamically constructed to the point where managing a great many equivalent prepared statements may be impractical - for example there may be a part of your query text that comes from outside your code,
your dynamic statements do not execute as many times, which makes prepared statements slower, since they require two trips to the database server to begin with,
your query cannot be written as a prepared statement due to database restrictions,
in some cases prepared statements are slower because the execution plan depends on the actual data used, in which case non-prepared statement may be a better choice,
in some cases the database support for prepared statements may still have issues compared to non-prepared,
typically prepared statements do not use database query cache, so repeating identical queries with identical input data may be faster without them.

Note that in Postgres, with prepared statements you may get an error like "could not determine data type of parameter $N". This is an issue with Postgres server. In this case you can use "::<type>" qualifier, such as for instance to tell Postgres the input parameter is text:

select col1 from test where someId>='%s' and col1 like concat( '%s'::text ,'%')

Examples

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

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

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

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