Ubuntu Manpage: run-query

NAME

       run-query -  (database)

PURPOSE

       Execute a query and loop through result set.

SYNTAX

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

DESCRIPTION

       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 omitted, 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 <<print-out first_name>>
               @Last name <<print-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 <<print-out first_name>>
               @Last name <<print-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, Golf 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 (which 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 ,'%')

       Note that SQL statements in SQLite are always prepared regardless of whether you use "run-query" or "run-
       prepared-query" due to how SQLite native interface works.

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 <<print-out  firstName>> <<print-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

NAME

PURPOSE

SYNTAX

DESCRIPTION

EXAMPLES

SEE ALSO