TH1 "query" API

The "query" API provides limited access to the fossil database. It restricts usage to queries which return result columns (i.e. SELECT and friends). Example usage:

<th1>
catch {
    set stmt [query prepare "SELECT login, cap FROM user"]
    puts "stmt ID=$stmt\n"
    for {} {[query $stmt step]} {} {
        puts [query $stmt col string 0] " " [query $stmt col string 1] \n
    }
    query $stmt finalize
    return 0
} rc
if {0 != $rc} {
    puts "ERROR: $rc\n"
}
<th1>

The various subcommands are summarized in the following subsections, and here are some notes regarding calling conventions:

The (bind, col, step, finalize) functions accept their statement ID argument either right after the "query" command or right after the final subcommand. The following examples demonstrate this:

query $stmt step query step $stmt

query $stmt finalize query finalize $stmt

query col string $stmt 1 query $stmt col string 1

query bind string $stmt 1 "foo" query $stmt bind string 1 "foo"

The "prefered" form is:

query StmtId command ...

(Why, then, are both forms accepted? Because the "preferred" form only evolved only after using the first form in script code.)

prepare

This subcommand prepares a query for execution. It returns a statement handle ID which must be passed to any other functions using the API.

All prepared statements must be finalized when they have outlived their usefulness.

set stmt [query prepare {SELECT ...}]
...
query $stmt finalize

finalize

Releases all resources associated with the statement. Note that future calls to prepare might re-use the same statement statement ID.

set stmt [query prepare "SELECT ..."]
...
query $stmt finalize

step

This subcommand steps the result set by one row. It returns 0 at the end of the set, a positive value if a new row is available, and throws for any other condition.

for {} {[query $stmt step]} {} {
   puts [query $stmt col string 0] "\n"
}

reset

Resets a query so that it can be executed again. This is only needed when binding parameters in a loop - call it at the end of each loop iteration.

query $stmt reset
query reset $stmt

bind xxx

The bind xxx family of subcommands attach values to queries before stepping through them. The subcommands include:

bind StmtId int Index Value
bind StmtId double Index Value
bind StmtId null Index
bind StmtId string Index Value

Note that all of those optionally accept the statement handle directly after the "query" command (before the "col" subcommand). e.g. query bind null $stmt 1 and query $stmt bind null 1 are equivalent. They also accept the column index either before or after the type name, e.g. query $stmt bind 1 string ... and query $stmt bind string 1 ... are equivalent.

Achtung: the bind API uses 1-based indexes, just like SQL does.

set stmt [query prepare "SELECT ... WHERE user=?"]
query $stmt bind int 1 drh
if {[query $stmt step]} {
   puts [query $stmt col string 0] "\n"
}
query $stmt finalize

col xxx

The col xxx familys of subcommands are for fetching values and metadata from result rows.

col StmtId count Returns the number of result columns in the statement.
col StmtId isnull Index Returns non-0 if the given column contains an SQL NULL value.
col StmtId (double|int|string) Index Fetches a column's value as either a number or string.
col StmtId time Index Format Modifiers Formats a time value. See below.
col StmtId type Index Returns the given column's type as a value from the SQLITE_TYPENAME family of constants.

Note that all of those optionally accept the statement handle directly after the "query" command (before the "col" subcommand). e.g. query $stmt col count and query col count $stmt are equivalent. They also accept the column index either before or after the type name, e.g. query $stmt col 1 string and query $stmt col string 1 are equivalent.

Achtung: the col API uses 0-based indexes, just like SQL does.

col time

This function is a proxy for sqlite3's strftime() function. It is used like this:

query $stmt col time $index {%Y%m%d @ %H:%M:%S}

Any remaining arguments are treated as "modifiers" and passed as-is to strfmtime. For example:

query $stmt col time $index {%Y%m%d @ %H:%M:%S} {+5 years}
query $stmt col time $index %s unixepoch

strftime

This works like col time (described below) but takes its value from an arbitrary source specified by the 3rd argument.

query strftime %s 1319211587 unixepoch
query strftime {%Y%m%d @ %H:%M:%S} [query $stmt col string 2] {+10 years}]

Global Variables

This API installs the following global variables, all of which correspond to SQLITE_xxx constant values:

SQLITE_BLOB
SQLITE_FLOAT
SQLITE_INTEGER
SQLITE_NULL
SQLITE_TEXT

These values are used only by the col type function. They can be accessed from script code via $::SQLITE_xxx.

Fossil SCM