nsdbi - Commands for querying an SQL database.
nsdbi provides an easy to use interface to databases.
Execute a query which is expected to return exactly one row. If no rows are returned then an error is raised.
The column values of the row are set as variables in the current stack frame using the column names as Tcl variable names. If the -array option is given then an array of that name is created instead with the column names corresponding to keys in the array.
dbi_1row {select first, last from peeps where id = 1}
set mysteryMan "$first $last"
Array-name syntax may be used to build an array result:
dbi_1row -array name {select first, last from peeps where id = 1}
set mysteryMan "$name(first) $name(last)"
dbi_1row {select first as 'name(first)', last as 'name(last)' from peeps where id = 1}
set mysteryMan "$name(first) $name(last)"
Execute a query which is expected to return either zero rows or exactly one row. If no rows are returned the result is 0 (false), otherwise it is 1 (true). If more than 1 row is returned an error is raised.
if {[dbi_0or1row {select first, last from peeps where id = 1}]} {
set mysteryMan "$first $last"
} else {
set mysteryMan "Benny Hill"
}
Execute a query which is expected to return zero or more rows. If no rows are returned then the result is the empty string. Otherwise a single, flat list of values is returned.
foreach {first last} [dbi_rows {select first, last from peeps}] {
append result "<li>$first $last</li>\n"
}
set result
<li>Dirty Harry</li>
<li>Jolly Roger</li>
If template is given then for each row returned by the query, template variables are substituted for the values of the corresponding columns. The templated rows are concatenated and returned as a string.
dbi_rows {select first, last from peeps} {<li>$first $last</li>\n}
<li>Dirty Harry</li>
<li>Jolly Roger</li>
If a template variable name does not correspond to a column name in the result then a Tcl variable by the same name is substituted.
proc greet_the_people {{greeting "Hello"}} {
dbi_rows {select first, last from peeps} {
$greeting, $first $last!
}
}
greet_the_people "Yo"
Yo, Dirty Harry!
Yo, Jolly Roger!
There are also three special variables:
The zero-based row index. The rows are numbered 0, 1, 2 etc.
The 1 based row number. The rows are numbered 1, 2, 3 etc.
The string "even" or "odd", based on the rowidx.
dbi_rows {select first, last from peeps} {
<tr class="$dbi(parity)"><th>$dbi(rownum)</th><td>$first $last</td></tr>
}
<tr class="even"><th>1</th><td>Dirty Harry</td></tr>
<tr class="odd"><th>2</th><td>Jolly Roger</td></tr>
If a template variable does not correspond to a column name, one of the three special variables, or an existing Tcl variable, an error is raised.
If no rows are returned then an error is raised, unless the default argument is given in which case it becomes the complete result.
dbi_rows {select leader from government} {${leader}! \n} {nobody here but us chickens}
nobody here but us chickens
Execute a DML query: insert, update, delete; or DDL query: create table etc. The result will be the number of rows affected by the query, for example the number of rows updated during an update query. If the query is not DML or DDL, an error will be thrown (the query is not run).
Get a database handle and then evaluate script, keeping the same handle open for the duration. Any dbi commands within the script will use the same handle if the handle the command needs would have come from the same db.
If the -transaction option is given then the commands are run in a transaction -- either all the commands succeed or they all fail. You may nest transactions.
The following transaction isolation-levels are supported. See your specific database for details.
Control various aspects of a configured database.
A query containing database specific SQL. The query may include bind variables of the form :variable - a name preceded by a single colon. Do not quote bind variables. Always use bind variables in preference to substituting Tcl variables into the query string.
set x 1
dbi_1row {select column from table where key = :x}
Bind variables automatically handle binary values. Simply pass a Tcl byte array type object and the raw bytes will be passed directly to the database. All other types must be in a string representation understood by the underlying database.
If a bind variable is the empty string "" then a NULL is passed to the database. Simillarly, NULLs in result sets become the empty string.
To use the nsdbi commands you must first load a driver, e.g. the postgreSQL driver nsdbipg. Drivers can be loaded more than once to create multiple pools of database handles, each with it's own configuration options.
ns_section "ns/modules" ns_param global1 nsdbipg.so ns_param global2 nsdbipg.so ... ns_section "ns/server/server1/modules" ns_param db3 nsdbipg.so ns_param db4 nsdbipg.so ... ns_section "ns/server/server1/module/db4" # The following parameters can be changed at runtime using dbi_ctl # and are explained above. ns_param default true ns_param maxhandles 0 ns_param timeout 10 ns_param maxidle 0 ns_param maxopen 0 ns_param maxqueries 0 ns_param maxrows 1000 # The following parameters are configured at server-startup. ns_param user dbusername ns_param password dbpassword ns_param database dbname ns_param cachesize [expr 1024*1024] ns_param checkinterval 600
Standard configuration options which are available with every driver:
The user name with which to log in to the database.
The password with which to log in to the database.
Which database to connect to.
The number of bytes used to cache the query text of prepared statements. The default is 1MB. There is one cache per-handle.
Check for idle handles every checkinterval seconds. The default is 600 seconds.
Each driver may also takes driver-specific parameters.
Select rows from a table using db x. Only rows in which column a matches the bind variable will be returned:
set a "someValue"
set rows [dbi_rows -db x {
select a, b
from table
where a = :a
}]
The following example shows how to bind a Tcl variable in a query and also how to change the name of the result variable, which in this case is an array.
set a 1
dbi_1row {select foo as 'array(foo)' from table where a = :a}
ns_log notice "foo: $array(foo)"
nsdb, subst
NaviServer, adp, database, query, sql