1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
|
<HTML><HEAD>
<TITLE>Tcl API Reference -- ns_db </TITLE>
<LINK rel=Previous href="tapi-c43.htm">
<LINK rel=ToC href="toc.htm">
<LINK rel=Index href="master.htm">
<LINK rel=Next href="tapi-c45.htm">
</HEAD><BODY BGCOLOR="#ffffff"><A NAME="topofpage"></A>
<TABLE WIDTH=100%>
<TR>
<TD ALIGN=LEFT>
<A NAME="topofpage"></A> <IMG SRC="as-c-sm.gif">
</TD>
<TD ALIGN=RIGHT>
<A href="tapi-c43.htm"><IMG BORDER="0" src=navbprev.gif alt="[ Previous ]"></A>
<A href=toc.htm> <IMG BORDER="0" src=navbhome.gif alt="[ Contents ]"></A>
<A href=master.htm> <IMG BORDER="0" src=navbhelp.gif alt="[ Index ]"></A>
<A href="tapi-c45.htm"> <IMG BORDER="0" src=navbnext.gif alt="[ Next ]"></A>
<A name="7983"> </A>
</TD>
</TR>
</TABLE>
<a name="67537">
</a><h3>ns_db </h3>
<a name="67538">
</a><h4>Overview</h4>
<p><a name="67542">
</a>Access the AOLserver database</p>
<a name="67543">
</a><h4>Syntax</h4>
<p><a name="75840">
</a><b>ns_db bindrow</b> <i>dbhandle</i></p>
<p><a name="154643">
</a><b>ns_db bouncepool</b> <i>dbhandle</i></p>
<p><a name="75881">
</a><b>ns_db cancel</b> <i>dbhandle</i></p>
<p><a name="75872">
</a><b>ns_db close</b> <i>dbhandle</i></p>
<p><a name="118694">
</a><b>ns_db connected</b> <i>dbhandle</i></p>
<p><a name="134762">
</a><b>ns_db datasource</b> <i>dbhandle</i></p>
<p><a name="118695">
</a><b>ns_db dbtype</b> <i>dbhandle</i></p>
<p><a name="75885">
</a><b>ns_db dml</b> <i>dbhandle</i> <i>sql</i></p>
<p><a name="75873">
</a><b>ns_db driver</b> <i>dbhandle</i></p>
<p><a name="120981">
</a><b>ns_db exception</b> <i>dbhandle</i></p>
<p><a name="75844">
</a><b>ns_db exec</b> <i>dbhandle sqlcommand</i></p>
<p><a name="75877">
</a><b>ns_db flush</b> <i>dbhandle</i></p>
<p><a name="75859">
</a><b>ns_db gethandle</b> ?-timeout <i>timeout</i>? [<i>poolname</i>] <i>nhandles</i></p>
<p><a name="75889">
</a><b>ns_db getrow </b><i>dbhandle</i> <i>setId</i></p>
<p><a name="75867">
</a><b>ns_db open</b> <i>driver datasource user password</i></p>
<p><a name="75868">
</a><b>ns_db poolname</b> <i>dbhandle</i></p>
<p><a name="67544">
</a><b>ns_db pools</b></p>
<p><a name="67548">
</a><b>ns_db releasehandle</b> <i>dbhandle</i></p>
<p><a name="67554">
</a><b>ns_db 1row</b> <i>dbhandle</i> <i>sql</i></p>
<p><a name="67555">
</a><b>ns_db 0or1row</b> <i>dbhandle</i> <i>sql</i></p>
<p><a name="116408">
</a><b>ns_db password</b> <i>dbhandle</i></p>
<p><a name="67556">
</a><b>ns_db select</b> <i>dbhandle</i> <i>sql</i></p>
<p><a name="67558">
</a><b>ns_db setexception</b> <i>dbhandle</i> <i>code message</i></p>
<p><a name="301075">
</a><b>ns_db sp_exec </b><i>dbhandle</i></p>
<p><a name="301076">
</a><b>ns_db sp_getparams</b></p>
<p><a name="301077">
</a><b>ns_db sp_returncode </b><i>dbhandle</i></p>
<p><a name="301078">
</a><b>ns_db sp_setparam </b><i>dbhandle varname vartype inout value</i></p>
<p><a name="301079">
</a><b>ns_db sp_start </b><i>dbhandle procname</i></p>
<p><a name="114107">
</a><b>ns_db user</b> <i>dbhandle</i></p>
<p><a name="114108">
</a><b>ns_db verbose</b> <i>dbhandle</i> ?on | off?</p>
<a name="67559">
</a><h4>Description</h4>
<p><a name="75842">
</a><b>ns_db bindrow</b> returns an ns_set structure whose key names are the column names of the rows returned by the SQL command previously-executed with <b>ns_db exec</b>. If the database is not currently returning rows (i.e., a status other than NS_ROWS was returned by <b>ns_db exec</b>), an error is thrown. The <i>dbhandle</i> argument is a database handle (obtained with <b>ns_db gethandle</b>).</p>
<p><a name="154644">
</a><b>ns_db bouncepool</b> marks all database handles for the specified database pool as stale. When any database handle currently open is put back into the pool, its connection to the database will be reset.</p>
<p><a name="75912">
</a><b>ns_db cancel</b> cancels the current operation.</p>
<p><a name="75903">
</a><b>ns_db close</b> closes the connection. Use this function only on handles that were obtained by the ns_db open function. However, the server automatically close handles when the operation is complete, so you don't normally have to call this function.</p>
<p><a name="121204">
</a><b>ns_db connected</b> returns a boolean value indicating whether the connection to the database pool is made.</p>
<p><a name="134763">
</a><b>ns_db datasource</b> returns the data source for the database pool (e.g., default:mydb).</p>
<p><a name="121207">
</a><b>ns_db dbtype</b> returns the database type of the database pool.</p>
<p><a name="75916">
</a><b>ns_db dml</b> executes SQL that should be data manipulation language such as an insert or update, or data definition language such as a create table.</p>
<p><a name="75904">
</a><b>ns_db driver</b> returns the name of the driver of the handle (e.g., "SOLID").</p>
<p><a name="121201">
</a><b>ns_db exception</b> returns the most recent exception for the database pool.</p>
<p><a name="75846">
</a><b>ns_db exec </b>executes the specified SQL command. It returns either NS_DML (if the SQL command is a DML or DDL command) or NS_ROWS (if the SQL command returns rows, such as a SELECT). If an error occurs executing the SQL command, an error is thrown. The <i>dbhandle</i> argument is a database handle (obtained with <b>ns_db gethandle</b>). This function can be used for true ad hoc querying, where you don't know what kind of SQL command will be executed.</p>
<p><a name="75908">
</a><b>ns_db flush</b> flushes the results of an SQL select so you do not need to use ns_db getrow to get all the rows and throw them away.</p>
<p><a name="172554">
</a><b>ns_db gethandle</b> returns the specified number of handles from the specified pool. If <i>poolname</i> is not specified, the Defaultpool from the configuration file is used. If <i>nhandles</i> is not specified, 1 handle is returned. (Note that if you specify <i>nhandles</i>, you must also specify a <i>poolname</i>.) If not enough handles are available to fulfill the request, it waits until they are available. <i>You must request all the handles you will need for a specific pool with one call to </i><b>ns_db gethandle</b><i>. </i>You must release all your database handles explicitly (with <b>ns_db releasehandle</b>) before acquiring more. If you request multiple handles from the database, this function returns a Tcl list of database handles (space delimited). In this case, each handle must be released with a separate call to <b>ns_db releasehandle</b>.</p>
<p><a name="200280">
</a>If a <i>timeout</i> is not specified or <i>timeout</i> is zero, <b>ns_db gethandle</b> will wait indefinitely (perhaps forever) for the requested number of handles to become available. If <i>timeout</i> is greater than zero, <b>ns_db gethandle</b> will either return with the handles within that time period, or return "" if the time period was exceeded, or generate a Tcl error of the form "could not allocate n handle(s) from pool 'poolname'. If <i>timeout</i> is less than zero, <b>ns_db gethandle</b> will not block. It will either return with the handles, or generate the above Tcl error. See the examples for <b>ns_db gethandle</b>, below.</p>
<p><a name="75899">
</a><b>ns_db getrow</b> fetches the next row waiting to be retrieved after an ns_db select. The data is dumped right into the set associated with SETID, which should be the set returned by the ns_db select. It returns "1" if there are more rows waiting and returns "0" otherwise. If you call <b>ns_db getrow</b> again after already receiving "0" on the previous call, an error is returned.</p>
<p><a name="67562">
</a><b>ns_db open</b> returns a handle at a lower level, circumventing the pools.</p>
<p><a name="67563">
</a><b>ns_db poolname</b> returns the database pool that this handle came from.</p>
<p><a name="75920">
</a><b>ns_db pools </b>returns a list of all database pools.</p>
<p><a name="67564">
</a><b>ns_db releasehandle</b> puts the handle back in the pool. When your operation has finished running, the server will automatically return any handles to their pools, so you don't normally have to call this function.</p>
<p><a name="67570">
</a><b>ns_db 1row</b> expects the SQL to be a select statement that returns exactly one row and returns that row as an ns_set.</p>
<p><a name="67571">
</a><b>ns_db 0or1row </b>expects the SQL to be a select statement that returns exactly zero or one row. On zero rows, a null string is returned. On one row, a newly allocated ns_set is returned.</p>
<p><a name="121198">
</a><b>ns_db password</b> returns the password of the user for the database pool.</p>
<p><a name="67572">
</a><b>ns_db select</b> executes the SQL statement on the database server. It returns an ns_set with the keys set to the column names that were selected. Use ns_db getrow to retrieve rows. You cannot perform nested <code>select</code> statements. Before you start a new <code>select</code> statement, you must first either retrieve all the rows from the first <code>select</code> or use the <code>ns_db flush</code> statement to flush any rows not yet retrieved.</p>
<p><a name="67574">
</a><b>ns_db setexception</b> returns the specified status code and message to the client.</p>
<p><a name="301182">
</a><b>ns_db sp_exec </b>executes a stored procedure that has been initialized with <b>ns_db</b> <b>sp_start</b> and <b>ns_db</b> <b>sp_setparam</b>. It returns "NS_DML" if the command was succsesfully executed but did not return rows, or it returns "NS_ROWS" if the command was successfully executed and did return rows (which can then be fetched with <b>ns_db</b> <b>bindrow</b> and <b>ns_db</b> <b>getrow</b>). It throws an error if the command failed. This function is implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.</p>
<p><a name="301219">
</a><b>ns_db sp_getparams </b>gets any output parameters set after executing a stored procedure with <b>ns_db sp_exec</b>. It returns an ns_set or throws an error on failure. This function is implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.</p>
<p><a name="301206">
</a><b>ns_db sp_returncode </b>gets the return code from a stored procedure. It must be called after <b>ns_db sp_exec</b>. This function is implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.</p>
<p><a name="301141">
</a><b>ns_db sp_setparam </b>sets a parameter for a call to a stored procedure. The <i>varname </i>is the name of the variable, for example "@name". The <i>vartype</i> is the data type of this parameter, for example "varchar". The <i>inout</i> argument indicates whether it's an input or output parameter. It must be set to either "in" or "out". The <i>value</i> is the paramter value to send. This function returns 1 on success and throws an error on failure. This function is implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.</p>
<p><a name="301132">
</a><b>ns_db sp_start </b>begins execution of the stored procedure called <i>procname</i>. It returns 0 on success and throws an error on failure. This function is implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.</p>
<p><a name="121194">
</a><b>ns_db user</b> returns the user (as specified for the User parameter of the configuration file) for the database pool.</p>
<p><a name="121195">
</a><b>ns_db verbose</b> changes the verbose setting (the Verbose parameter in the configuration file) for the database pool.</p>
<a name="199858">
</a><h4>Examples</h4>
<ol>
<li>Here's an example ADP that runs a stored procedure called "p_TestProc" in a pool called "sybase":
<a name="301562">
</a><p>
<pre> <a name="301575"></a><HTML>
<a name="301576"></a><HEAD>
<a name="301577"></a></HEAD>
<a name="301578"></a>
<a name="301579"></a><BODY>
<a name="301580"></a>
<a name="301581"></a><%
<a name="301582"></a>#
<a name="301583"></a># The new stored procedure commands that I've implemented are:
<a name="301584"></a>#
<a name="301585"></a># ns_db sp_start $dbhandle $procname
<a name="301586"></a># ns_db sp_setparam $dbhandle $varname, $vartype, in|out,
$value
<a name="301587"></a># ns_db sp_exec $dbhandle
<a name="301588"></a># ns_db sp_returncode $dbhandle
<a name="301589"></a># ns_db sp_getparams $dbhandle
<a name="301590"></a>#
<a name="301591"></a># Look for them and their usage below...
<a name="301592"></a>#
<a name="301593"></a>
<a name="301594"></a>set formdata [ns_conn form $conn]
<a name="301595"></a>#set pool [ns_set get $formdata "pool"]
<a name="301596"></a>#set pool "sybdoug"
<a name="301597"></a>set pool "sybase"
<a name="301598"></a>
<a name="301599"></a>if {$pool == ""} {
<a name="301600"></a> ns_puts "<P>No database pool specified.</P>"
<a name="301601"></a> ns_adp_break
<a name="301602"></a>}
<a name="301603"></a>
<a name="301604"></a>set db [ns_db gethandle $pool]
<a name="301605"></a>set datasource [ns_db datasource $db]
<a name="301606"></a>set dbtype [ns_db dbtype $db]
<a name="301607"></a>set driver [ns_db driver $db]
<a name="301608"></a>set poolname [ns_db poolname $db]
<a name="301609"></a>set user [ns_db user $db]
<a name="301610"></a>set password [ns_db password $db]
<a name="301611"></a>
<a name="301612"></a>#
<a name="301613"></a># Show some information about this connection to the database.
<a name="301614"></a>#
<a name="301615"></a>ns_puts "<TABLE>"
<a name="301616"></a>ns_puts "<TR><TD>datasource</TD><TD>$datasource</TD></TR>"
<a name="301617"></a>ns_puts "<TR><TD>dbtype</TD><TD>$dbtype</TD></TR>"
<a name="301618"></a>ns_puts "<TR><TD>driver</TD><TD>$driver</TD></TR>"
<a name="301619"></a>ns_puts "<TR><TD>poolname</TD><TD>$poolname</TD></TR>"
<a name="301620"></a>ns_puts "<TR><TD>user</TD><TD>$user</TD></TR>"
<a name="301621"></a>ns_puts "<TR><TD>password</TD><TD>$password</TD></TR>"
<a name="301622"></a>ns_puts "</TABLE>"
<a name="301623"></a>ns_puts "<BR><BR>"
<a name="301624"></a>
<a name="301625"></a>#
<a name="301626"></a># Turn on server tracing for this handle -- only do this if
you need debug
<a name="301627"></a># output on the server side.
<a name="301628"></a>#
<a name="301629"></a>ns_ext traceon $db "/tmp/sybtrace"
<a name="301630"></a>
<a name="301631"></a>#
<a name="301632"></a># Create a stored procedure.
<a name="301633"></a>#
<a name="301634"></a>ns_puts "<P>Calling ns_db sp_start...</P>"
<a name="301635"></a>set ret [ns_db sp_start $db "p_TestProc"]
<a name="301636"></a>ns_puts "<P>ns_db sp_start db p_TestProc returned $ret</P>"
<a name="301637"></a>
<a name="301638"></a>#
<a name="301639"></a># Set the parameters for this stored procedure. The SQL definition
of this
<a name="301640"></a># procedure is:
<a name="301641"></a>#
<a name="301642"></a># CREATE PROCEDURE p_TestProc(@x int, @y varchar(16) out, @z
int out)
<a name="301643"></a>#
<a name="301644"></a># The arguments to ns_db sp_setparam are like this:
<a name="301645"></a>#
<a name="301646"></a># ns_db setparam $dbhandle $varname, $vartype, in|out, $value
<a name="301647"></a>#
<a name="301648"></a>set ret [ns_db sp_setparam $db "@x" int in 4]
<a name="301649"></a>ns_puts "<P>ns_db sp_setparam @x returned $ret</P>"
<a name="301650"></a>set ret [ns_db sp_setparam $db "@y" varchar out "varchar val"]
<a name="301651"></a>ns_puts "<P>ns_db sp_setparam @y returned $ret</P>"
<a name="301652"></a>set ret [ns_db sp_setparam $db "@z" int out 231]
<a name="301653"></a>ns_puts "<P>ns_db sp_setparam @z returned $ret</P>"
<a name="301654"></a>
<a name="301655"></a>#
<a name="301656"></a># Execute the stored procedure now
<a name="301657"></a>#
<a name="301658"></a>set ret [ns_db sp_exec $db]
<a name="301659"></a>ns_puts "<P>ns_db sp_exec returned $ret</P>"
<a name="301660"></a>
<a name="301661"></a>#
<a name="301662"></a># Bind an ns_set to the rows returned from this procedure
<a name="301663"></a>#
<a name="301664"></a>set row [ns_db bindrow $db]
<a name="301665"></a>if {$row == ""} {
<a name="301666"></a> ns_puts "<P>No row data returned from bindrow.</P>
<a name="301667"></a> ns_adp_break
<a name="301668"></a>}
<a name="301669"></a>
<a name="301670"></a>#
<a name="301671"></a># Display all of the rows returned in a generic way
<a name="301672"></a>#
<a name="301673"></a>set numcols [ns_set size $row]
<a name="301674"></a>ns_puts "<TABLE BORDER>"
<a name="301675"></a>ns_puts "<TR>"
<a name="301676"></a>for {set i 0} {$i < $numcols} {incr i} {
<a name="301677"></a> ns_puts "<TH>[ns_set key $row $i]</TH>"
<a name="301678"></a>}
<a name="301679"></a>while {[ns_db getrow $db $row]} {
<a name="301680"></a> ns_puts "<TR>"
<a name="301681"></a> for {set i 0} {$i < $numcols} {incr i} {
<a name="301682"></a> ns_puts "<TD VALIGN=\"TOP\">[ns_set value $row $i]</TD>"
<a name="301683"></a> }
<a name="301684"></a> ns_puts "</TR>"
<a name="301685"></a>}
<a name="301686"></a>ns_puts "</TABLE>"
<a name="301687"></a>
<a name="301688"></a>#
<a name="301689"></a># Show the return code
<a name="301690"></a>#
<a name="301691"></a>ns_puts "<P>Return code: [ns_db sp_returncode $db]</P>"
<a name="301692"></a>
<a name="301693"></a>#
<a name="301694"></a># Process output parameters. The output parameters are returned
in an ns_set
<a name="301695"></a># with the keys set to the parameter names from the above setparam
calls.
<a name="301696"></a># All of the parameters that were specified as an "out" parameter
will be
<a name="301697"></a># represented here if they were defined as output values in
the stored
<a name="301698"></a># procedure itself.
<a name="301699"></a>#
<a name="301700"></a>catch {
<a name="301701"></a> ns_puts "<P>Output parameters:</P>"
<a name="301702"></a> set paramset [ns_db sp_getparams $db]
<a name="301703"></a> if {$paramset == "" || $paramset == 0} {
<a name="301704"></a> ns_puts "<P>sp_getparams failed</P>"
<a name="301705"></a> }
<a name="301706"></a>} paramerr
<a name="301707"></a>if {$paramerr != ""} {
<a name="301708"></a> ns_puts "<P>getparams generated a database exception</P>"
<a name="301709"></a> ns_adp_break;
<a name="301710"></a>}
<a name="301711"></a>
<a name="301712"></a>#
<a name="301713"></a># Display the output parameters
<a name="301714"></a>#
<a name="301715"></a>set numparams [ns_set size $paramset]
<a name="301716"></a>for {set i 0} {$i < $numcols} {incr i} {
<a name="301717"></a> set key [ns_set key $paramset $i]
<a name="301718"></a> ns_puts "<P>Param $key: [ns_set get $paramset $key]</P>"
<a name="301719"></a>}
<a name="301720"></a>%>
<a name="301721"></a>
<a name="301722"></a></BODY>
<a name="301723"></a></HTML>
</pre><p><li>These are valid uses of <b>ns_db gethandle</b>:
<a name="200181">
</a><p>
<pre> <a name="199861"></a>ns_db gethandle
<a name="200216"></a># 1 handle from default pool
<a name="199862"></a>ns_db gethandle -timeout 23
<a name="200217"></a># 1 handle from default pool, 23 sec timeout
<a name="200263"></a>ns_db gethandle -timeout -1 poolname
<a name="200274"></a># 1 handle from poolname, error if not available
<a name="199863"></a>ns_db gethandle poolname
<a name="200218"></a># 1 handle from poolname
<a name="199864"></a>ns_db gethandle -timeout 23 poolname
<a name="200219"></a># 1 handle from poolname, 23 sec timeout
<a name="199865"></a>ns_db gethandle poolname 5
<a name="200220"></a># 5 handles from poolname
<a name="200247"></a>ns_db gethandle -timeout 23 poolname 5
<a name="200221"></a># 5 handles from poolname, 23 sec timeout
</pre><p></ol>
<p><a name="199867">
</a>This is not valid, because <i>nhandles</i> is specified without a <i>poolname</i>:</p>
<pre> <a name="199868"></a>ns_db gethandle 5
<a name="200258"></a># it thinks 5 is the pool name
</pre><p><p><a name="199859">
</a></p>
<TABLE BORDER="2" CELLPADDING="1" width="100%">
<TR><TD COLSPAN=3><P ALIGN=Center>
<IMG SRC="bluebult.gif">
<A HREF="#topofpage">
<FONT SIZE=-1>Top of Page</FONT></A>
<IMG SRC="bluebult.gif">
</TD></TR>
<TR><TD COLSPAN=3><P ALIGN=Center>
<A href="tapi-c43.htm">
<IMG BORDER="0" src=navbprev.gif alt="[ Previous ]"></A>
<A href=toc.htm>
<IMG BORDER="0" src=navbhome.gif alt="[ Contents ]"></A>
<A href=master.htm>
<IMG BORDER="0" src=navbhelp.gif alt="[ Index ]"></A>
<A href="tapi-c45.htm">
<IMG BORDER="0" src=navbnext.gif alt="[ Next ]"></A>
<BR align=center>
<FONT size=-1>Copyright © 1998-99 America Online,
Inc.</FONT>
</TD></TR></TABLE></BODY></HTML>
|
