Fossil

Check-in [8e040551]
Login
☰Home Timeline Docs SupportDownload

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Reduced the set of th1-exported SQLITE_xxx constants to those which are actually used in script APIs. Minor doc updates.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | th1-query-api
Files: files | file ages | folders
SHA1: 8e0405513321df912a3abb64d92f6b3767746c0f
User & Date: stephan 2012-07-22 11:55:56
Context
2012-07-22
13:11
Minor usage simplifications in the th1 argv API. (check-in: 24b9fbca user: stephan tags: th1-query-api)
11:55
Reduced the set of th1-exported SQLITE_xxx constants to those which are actually used in script APIs. Minor doc updates. (check-in: 8e040551 user: stephan tags: th1-query-api)
11:39
minor th1 doc and script code cleanups. (check-in: 2b4b2a50 user: stephan tags: th1-query-api)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/th_main.c. 

2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027




2028
2029

2030
2031
2032
2033
2034
2035
2036

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_DONE);
  SET(SQLITE_ERROR);
  SET(SQLITE_FLOAT);
  SET(SQLITE_INTEGER);
  SET(SQLITE_NULL);
  SET(SQLITE_OK);




  SET(SQLITE_ROW);
  SET(SQLITE_TEXT);

#undef SET
  int rc = TH_OK;
  static Th_Command_Reg aCommand[] = {
    {"query",             queryTopLevelCmd,  0},
    {0, 0, 0}
  };
  rc = Th_RegisterCommands( interp, aCommand );








<
<



|
>
>
>
>
|
|
>








2015
2016
2017
2018
2019
2020
2021


2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039

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

Changes to test/th1-query-api-1.th1. 

88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

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

set consts [list SQLITE_BLOB SQLITE_DONE SQLITE_ERROR SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_OK SQLITE_ROW 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"
}









|








88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

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

Changes to www/th1_query.wiki. 

29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46









47
48
49
50
51
52
53
54







55
56
57
58
59
60
61
62
63

64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

132
133

134
135
136
137
138
139
140
...
167
168
169
170
171
172
173














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>











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








<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 {} {0 &lt; [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 int StmtId Index Value</tt>
   *  <tt>bind double StmtId Index Value</tt>
   *  <tt>bind null StmtId Index</tt>
   *  <tt>bind string StmtId 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 {0 &lt; [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 count StmtId</tt> Returns the number of result columns in the statement.
   *  <tt>col isnull StmtId Index</tt> Returns non-0 if the given column contains an SQL NULL value.
   *  <tt>col double StmtId Index</tt>
   *  <tt>col int StmtId Index</tt>
   *  <tt>col string StmtId Index</tt>

   *  <tt>col string StmtId Index Format Modifiers</tt> See below.
   *  <tt>col type StmtId Index</tt> Return value corresponds to one of 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>






















<










>
>
>
>
>
>
>
>
>








>
>
>
>
>
>
>









>











|







 







|
|
|
|







 







|








|
<

|
|
<
<
<
>
|
<
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>

29
30
31
32
33
34
35

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

141
142
143



144
145

146
147
148
149
150
151
152
153
...
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

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

This page was generated in about 0.009s by Fossil 2.12 [85f4869078] 2020-07-10 12:49:00