Fossil

int th_register_query(Th_Interp *interp){
  enum { BufLen = 100 };
  char buf[BufLen];
  int i, l;
#define SET(K) l = snprintf(buf, BufLen, "%d", K);      \
  Th_SetVar( interp, #K, strlen(#K), buf, l );
  SET(SQLITE_BLOB);


  SET(SQLITE_FLOAT);
  SET(SQLITE_INTEGER);
  SET(SQLITE_NULL);
  SET(SQLITE_TEXT);
#if 0
  /* so far we don't need these in script code */
  SET(SQLITE_ERROR);
  SET(SQLITE_DONE);
  SET(SQLITE_OK);
  SET(SQLITE_ROW);
#endif
#undef SET
  int rc = TH_OK;
  static Th_Command_Reg aCommand[] = {
    {"query",             queryTopLevelCmd,  0},
    {0, 0, 0}
  };
  rc = Th_RegisterCommands( interp, aCommand );

    return 0
} rc
query finalize $stmt
if { 0 != $rc } {
        puts "ERROR: $rc\n"
}

set consts [list SQLITE_BLOB SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_TEXT]
#set consts $SQLITE_CONSTANTS
puts consts = $consts "\n"
for {set i 0} {$i < [llength $consts]} {incr i} {
    set x [lindex $consts $i]
    puts \$$x = [expr \$$x] "\n"
}

either right after the "query" command or right after the final subcommand.
The following examples demonstrate this:

<nowiki><pre>
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"
</pre></nowiki>

The "prefered" form is:

<nowiki><pre>
query StmtId command ...
</pre></nowiki>

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


<h2>prepare</h2>

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 <tt>finalize</tt>d when they have outlived
their usefulness.

<nowiki><pre>
set stmt [query prepare {SELECT ...}]
...
query $stmt finalize
</pre></nowiki>


<h2>finalize</h2>

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

<nowiki><pre>
set stmt [query prepare "SELECT ..."]
...
query $stmt finalize
</pre></nowiki>


<h2>step</h2>

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.

<nowiki><pre>
for {} {[query $stmt step]} {} {
   puts [query $stmt col string 0] "\n"
}
</pre></nowiki>


<h2>reset</h2>

<h2>bind xxx</h2>

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

   *  <tt>bind StmtId int Index Value</tt>
   *  <tt>bind StmtId double Index Value</tt>
   *  <tt>bind StmtId null Index</tt>
   *  <tt>bind StmtId string Index Value</tt>

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


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

<nowiki><pre>
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
</pre></nowiki>


<h2>col xxx</h2>

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


   *  <tt>col StmtId count</tt> Returns the number of result columns in the statement.
   *  <tt>col StmtId isnull Index</tt> Returns non-0 if the given column contains an SQL NULL value.
   *  <tt>col StmtId (double|int|string) Index</tt> Fetches a column's value as either a number or string.


   *  <tt>col StmtId time Index Format Modifiers</tt> Formats a time value. See below.
   *  <tt>col StmtId type Index</tt> Returns the given column's type as a value from the <tt>SQLITE_TYPENAME</tt> family of constants.

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

value from an arbitrary source specified by the 3rd argument.

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

<h2>Global Variables</h2>

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

   *  <tt>SQLITE_BLOB</tt>
   *  <tt>SQLITE_FLOAT</tt>
   *  <tt>SQLITE_INTEGER</tt>
   *  <tt>SQLITE_NULL</tt>
   *  <tt>SQLITE_TEXT</tt>

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