<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type">
<title>Database Access API</title>
<meta name="generator" content="DocBook XSL Stylesheets V1.45">
<link rel="home" href="index.html" title="OpenACS Documentation">
<link rel="up" href="kernel-doc.html" title="Chapter 7. Kernel Documentation">
<link rel="previous" href="rp-design.html" title="OpenACS 4 Request Processor Design">
<link rel="next" href="tcl-doc.html" title="Documenting Tcl Files: Page Contracts and Libraries">
<link rel="stylesheet" href="openacs.css" type="text/css">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<a href="http://openacs.org"><img src="images/alex.jpg" border="0"></a><table width="100%" summary="Navigation header" border="0"><tr>
<td width="20%" align="left">
<a accesskey="p" href="rp-design.html">Prev</a> </td>
<th width="60%" align="center">Chapter 7. Kernel Documentation</th>
<td width="20%" align="right"> <a accesskey="n" href="tcl-doc.html">Next</a>
</td>
</tr></table>
<hr>
</div>
<div class="sect1">
<div class="titlepage"><div><h2 class="title" style="clear: both">
<a name="db-api-detailed"></a>Database Access API</h2></div></div>
<div class="authorblurb"><p>
by <a href="mailto:jsalz@mit.edu" target="_top">Jon Salz</a>
</p></div>
<div class="itemizedlist"><ul>
<li><p>Tcl procedures: /packages/acs-kernel/10-database-procs.tcl</p></li>
<li><p>Tcl initialization: /packages/acs-kernel/database-init.tcl</p></li>
</ul></div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-bigpicture"></a>The Big Picture</h3></div></div>
<p>
One of OpenACS's great strengths is that code written for it is very close to
the database. It is very easy to interact with the database from anywhere
within OpenACS. Our goal is to develop a coherent API for database access which
makes this even easier.
</p>
<p>There were four significant problems with the way OpenACS previously used the
database (i.e., directly through the <tt>ns_db</tt> interface):</p>
<div class="orderedlist"><ol type="1">
<li><p>
<span class="strong"><i>Handle management</i></span>. We required code to pass database
handles around, and for routines which needed to perform database access but
didn't receive a database handle as input, it was difficult to know from
which of the three "magic pools" (main, subquery, and log) to
allocate a new handle.
</p></li>
<li>
<p>
<span class="strong"><i>Nested transactions</i></span>. In our Oracle driver, <tt>begin
transaction</tt> really means "turn auto-commit mode off" and
<tt>end transaction</tt> means "commit the current transaction and
turn auto-commit mode on." Thus if transactional code needed to call a
routine which needed to operate transactionally, the semantics were
non-obvious. Consider: </p>
<pre class="programlisting">
proc foo { db args } {
db_transaction {
...
}
}
db_transaction {
db_dml unused "insert into greeble(bork) values(33)"
foo $db
db_dml unused "insert into greeble(bork) values(50)"
}
</pre>
<p>
This would insert greeble #33 and do all the stuff in <tt>foo</tt>
transactionally, but the <tt>end transaction</tt> in <tt>foo</tt>
would actually cause a commit, and greeble #50 would later be inserted in
auto-commit mode. This could cause subtle bugs: e.g., in the case that the
insert for greeble #50 failed, part of the "transaction" would have
already have been committed!. This is not a good thing.
</p>
</li>
<li><p>
<span class="strong"><i>Unorthodox use of variables</i></span>. The standard mechanism for
mapping column values into variables involved the use of the
<tt>set_variables_after_query</tt> routine, which relies on an uplevel
variable named <tt>selection</tt> (likewise for
<tt>set_variables_after_subquery</tt> and <tt>subselection</tt>).
</p></li>
<li><p>
<span class="strong"><i>Hard-coded reliance on Oracle</i></span>. It's difficult to
write code supporting various different databases (dynamically using the
appropriate dialect based on the type of database being used, e.g., using
<tt>DECODE</tt> on Oracle and <tt>CASE ... WHEN</tt> on
Postgres).</p></li>
</ol></div>
<p>
The Database Access API addresses the first three problems by:
</p>
<div class="orderedlist"><ol type="1">
<li><p>making use of database handles transparent</p></li>
<li><p>wrapping common database operations (including transaction management) in
Tcl control structures (this is, after all, what Tcl is good at!)</p></li>
</ol></div>
<p>
It lays the groundwork for addressing the fourth problem by assigning each
SQL statement a logical name. In a future version of the OpenACS Core, this API
will translate logical statement names into actual SQL, based on the type of
database in use. (To smooth the learning curve, we provide a facility for
writing SQL inline for a "default SQL dialect", which we assume to
be Oracle for now.)
</p>
<p>To be clear, SQL abstraction is <span class="emphasis"><i>not</i></span> fully implemented in OpenACS
3.3.1. The statement names supplied to each call are not used by the API at
all. The API's design for SQL abstraction is in fact incomplete;
unresolved issues include:</p>
<div class="itemizedlist"><ul>
<li><p>how to add <tt>WHERE</tt> clause criteria dynamically</p></li>
<li><p>how to build a dynamic <tt>ORDER BY</tt> clause (Ben Adida has a
proposed solution for this)</p></li>
<li><p>how to define a statement's formal interface (i.e., what bind
variables it expects, what columns its <tt>SELECT</tt> clause must
contain if it's a query) without actually implementing the statement in a
specific SQL dialect</p></li>
</ul></div>
<p>
So why is the incremental change of adding statement naming to the API worth
the effort? It is worth the effort because we know that giving each SQL
statement a logical name will be required by the complete SQL abstraction
design. Therefore, we know that the effort will not be wasted, and taking
advantage of the new support for bind variables will already require code
that uses 3.3.0 version of the API to be updated.
</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-set-var-aft-query"></a>The Bell Tolls for <tt>set_variables_after_query</tt></h3></div></div>
<p>
<tt>set_variables_after_query</tt> is gone! (Well, it's still there,
but you'll never need to use it.) The new API routines set local
variables automatically. For instance:
</p>
<pre class="programlisting">
db_1row select_names "select first_names, last_name from users where user_id = [ad_get_user_id]"
doc_body_append "Hello, $first_names $last_name!"
</pre>
<p>
Like <tt>ns_db 1row</tt>, this will bomb if the query doesn't return
any rows (no such user exists). If this isn't what you want, you can
write:
</p>
<pre class="programlisting">
if { [db_0or1row select_names "select first_names, last_name from users where user_id = [ad_get_user_id]"] } {
doc_body_append "Hello, $first_names $last_name!"
} else {
# Executed if the query returns no rows.
doc_body_append "There's no such user!"
}
</pre>
<p>
Selecting a bunch of rows is a lot prettier now:
</p>
<pre class="programlisting">
db_foreach select_names "select first_names, last_name from users" {
doc_body_append "Say hi to $first_names $last_name for me!<br>"
}
</pre>
<p>
That's right, <tt>db_foreach</tt> is now like <tt>ns_db
select</tt> plus a <tt>while</tt> loop plus
<tt>set_variables_after_query</tt> plus an <tt>if</tt> statement
(containing code to be executed if no rows are returned).
</p>
<pre class="programlisting">
db_foreach select_names "select first_names, last_name from users where last_name like 'S%'" {
doc_body_append "Say hi to $first_names $last_name for me!<br>"
} if_no_rows {
doc_body_append "There aren't any users with last names beginnings with S!"
}
</pre>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-handles"></a>Handle Management</h3></div></div>
<p>
The new API keeps track of which handles are in use, and automatically
allocates new handles when they are necessary (e.g., to perform subqueries
while a select is active). For example:
</p>
<pre class="programlisting">
doc_body_append "<ul>"
db_foreach select_names "select first_names, last_name, user_id from users" {
# Automatically allocated a database handle from the main pool.
doc_body_append "<li>User $first_names $last_name\n<ul>"
db_foreach select_groups "select group_id from user_group_map where user_id = $user_id" {
# There's a selection in progress, so we allocated a database handle
# from the subquery pool for this selection.
doc_body_append "<li>Member of group #$group_id.\n"
} if_no_rows {
# Not a member of any groups.
doc_body_append "<li>Not a member of any group.\n"
}
}
doc_body_append "</ul>"
db_release_unused_handles
</pre>
<p>
A new handle isn't actually allocated and released for every selection,
of course - as a performance optimization, the API keeps old handles around
until <tt>db_release_unused_handles</tt> is invoked (or the script
terminates).
</p>
<p>Note that there is no analogue to <tt>ns_db gethandle</tt> - the
handle is always automatically allocated the first time it's needed.</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-bindvars"></a>Bind Variables</h3></div></div>
<p><span class="strong"><i>Introduction</i></span></p>
<p>
Most SQL statements require that the code invoking the statement pass along
data associated with that statement, usually obtained from the user. For
instance, in order to delete a WimpyPoint presentation, a Tcl script might
use the SQL statement
</p>
<pre class="programlisting">
delete from wp_presentations where presentation_id = <span class="emphasis"><i>some_presentation_id</i></span>
</pre>
<p>
where <span class="emphasis"><i><tt>some_presentation_id</tt></i></span> is a number which is a valid
presentation ID of the presentation I want to delete. It's easy to write
code handling situations like this since SQL statements can include
<span class="strong"><i>bind variables</i></span>, which represent placeholders for actual
data. A bind variable is specified as a colon followed by an identifier, so
the statement above can be coded as:
</p>
<pre class="programlisting">
db_dml presentation_delete {
delete from wp_presentations where presentation_id = :some_presentation_id
}
</pre>
<p>
When this SQL statement is invoked, the value for the bind variable
<tt>:some_presentation_id</tt> is pulled from the Tcl variable
<tt>$some_presentation_id</tt> (in the caller's environment). Note
that bind variables are not limited to one per statement; you can use an
arbitrary number, and each will pull from the correspondingly named Tcl
variable. (Alternatively, you can also specify an list or <tt>ns_set</tt>
providing bind variables' values; see <span class="emphasis"><i>Usage</i></span>.)
</p>
<p>The value of a bind variable is taken literally by the database driver, so
there is never any need to put single-quotes around the value for a bind
variable, or to use <tt>db_quote</tt> to escape single-quotes contained
in the value. The following works fine, despite the apostrophe:</p>
<pre class="programlisting">
set exclamation "That's all, folks!"
db_dml exclamation_insert { insert into exclamations(exclamation) values(:exclamation) }
</pre>
<p>Note that you can use a bind variable in a SQL statement only where you
could use a literal (a number or single-quoted string). Bind variables cannot
be placeholders for things like SQL keywords, table names, or column names,
so the following will not work, even if <tt>$table_name</tt> is set
properly:</p>
<pre class="programlisting">
select * from :table_name
</pre>
<p><span class="strong"><i>Why Bind Variables Are Useful</i></span></p>
<p>
Why bother with bind variables at all - why not just write the Tcl statement
above like this:
</p>
<pre class="programlisting">
db_dml presentation_delete "
delete from wp_presentations where presentation_id = $some_presentation_id
"
</pre>
<p>
(Note the use of double-quotes to allow the variable reference to
<tt>$some_presentation_id</tt> to be interpolated in.) This will work,
but consider the case where some devious user causes
<tt>some_presentation_id</tt> to be set to something like <tt>'3 or
1 = 1'</tt>, which would result in the following statement being
executed:
</p>
<pre class="programlisting">
delete from wp_presentations where presentation_id = 3 or 1 = 1
</pre>
<p>
This deletes every presentation in the database! Using bind variables
eliminates this gaping security hole: since bind variable values are taken
literally. Oracle will attempt to delete presentations whose presentation ID
is literally <tt>'3 or 1 = 1'</tt> (i.e., no presentations, since
<tt>'3 or 1 = 1'</tt> can't possibly be a valid integer
primary key for <tt>wp_presentations</tt>. In general, since Oracle
always considers the values of bind variables to be literals, it becomes more
difficult for users to perform URL surgery to trick scripts into running
dangerous queries and DML.
</p>
<p><span class="strong"><i>Usage</i></span></p>
<p>Every <tt>db_*</tt> command accepting a SQL command as an argument
supports bind variables. You can either</p>
<div class="itemizedlist"><ul>
<li><p>specify the <tt>-bind</tt> switch to provide a set with bind variable
values, or</p></li>
<li><p>specify the <tt>-bind</tt> switch to explicitly provide a list of
bind variable names and values, or</p></li>
<li><p>not specify a bind variable list at all, in which case Tcl variables are
used as bind variables.</p></li>
</ul></div>
<p>
The default behavior (i.e., if the <tt>-bind</tt> switch is omitted) is
that these procedures expect to find local variables that correspond in name
to the referenced bind variables, e.g.:
</p>
<pre class="programlisting">
set user_id 123456
set role "administrator"
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} {
# do something for each group of which user 123456 is in the role
# of "administrator"
}
</pre>
<p>
The value of the local Tcl variable <tt>user_id</tt> (123456) is bound to
the <tt>user_id</tt> bind variable.
</p>
<p>The <tt>-bind</tt> switch can takes the name of an <tt>ns_set</tt>
containing keys for each bind variable named in the query, e.g.:</p>
<pre class="programlisting">
set bind_vars [ns_set create]
ns_set put $bind_vars user_id 123456
ns_set put $bind_vars role "administrator"
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} -bind $bind_vars {
# do something for each group in which user 123456 has the role
# of "administrator"
}
</pre>
<p>
Alternatively, as an argument to <tt>-bind</tt> you can specify a list of
alternating name/value pairs for bind variables:
</p>
<pre class="programlisting">
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} -bind [list user_id 123456 role "administrator"] {
# do something for each group in which user 123456 has the role
# of "administrator"
}
</pre>
<p><span class="strong"><i><a name="kernel.dbapi_nulls_and_bind_vars"></a>Nulls and Bind Variables</i></span></p>
<p>
When processing a DML statement, Oracle coerces empty strings into
<tt>null</tt>. (This coercion does <span class="emphasis"><i>not</i></span> occur in the
<tt>WHERE</tt> clause of a query, i.e.
<tt>col = ''</tt> and
<tt>col is null</tt> are not equivalent.)
</p>
<p>As a result, when using bind variables, the only way to make Oracle set a
column value to <tt>null</tt> is to set the corresponding bind variable
to the empty string, since a bind variable whose value is the string
"null" will be interpreted as the literal string
"null".</p>
<p>These Oracle quirks complicate the process of writing clear and abstract
DML difficult. Here is an example that illustrates why:</p>
<pre class="programlisting">
#
# Given the table:
#
# create table foo (
# bar integer,
# baz varchar(10)
# );
#
set bar ""
set baz ""
db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# the values of the "bar" and "baz" columns in the new row are both
# null, because Oracle has coerced the empty string (even for the
# numeric column "bar") into null in both cases
</pre>
<p>
Since databases other than Oracle do not coerce empty strings into
<tt>null</tt>, this code has different semantics depending on the
underlying database (i.e., the row that gets inserted may not have null as
its column values), which defeats the purpose of SQL abstraction.
</p>
<p>Therefore, the Database Access API provides a database-independent way to
represent <tt>null</tt> (instead of the Oracle-specific idiom of the
empty string): <span class="strong"><i><tt>db_null</tt></i></span>.</p>
<p>Use it instead of the empty string whenever you want to set a column value
explicitly to <tt>null</tt>, e.g.:</p>
<pre class="programlisting">
set bar [db_null]
set baz [db_null]
db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# sets the values for both the "bar" and "baz" columns to null
</pre>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-sql-abstraction"></a>SQL Abstraction</h3></div></div>
<p>
We now require that each SQL statement be assigned a logical name for the
statement that is unique to the procedure or page in which it is defined.
This is so that (eventually) we can implement logically named statements with
alternative SQL for non-Oracle databases (e.g., Postgres). More on this
later.
</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-placing-values"></a>Placing Column Values in Arrays and Sets</h3></div></div>
<p>
Normally, <tt>db_foreach</tt>, <tt>db_0or1row</tt>, and
<tt>db_1row</tt> places the results of queries in Tcl variables, so you
can say:
</p>
<pre class="programlisting">
db_foreach users_select "select first_names, last_name from users" {
doc_body_append "<li>$first_names $last_name\n"
}
</pre>
<p>
However, sometimes this is not sufficient: you may need to examine the rows
returned, to dynamically determine the set of columns returned by the query,
or to avoid collisions with existing variables. You can use the
<tt>-column_array</tt> and <tt>-column_set</tt> switches to
<tt>db_foreach</tt>, <tt>db_0or1row</tt>, and <tt>db_1row</tt> to
instruct the database routines to place the results in a Tcl array or
<tt>ns_set</tt>, respectively, where the keys are the column names and
the values are the column values. For example:
</p>
<pre class="programlisting">
db_foreach users_select "select first_names, last_name from users" -column_set columns {
# Now $columns is an ns_set.
doc_body_append "<li>"
for { set i 0 } { $i < [ns_set size $columns] } { incr i } {
doc_body_append "[ns_set key $columns $i] is [ns_set value $columns $i]. \n"
}
}
</pre>
<p>
will write something like:
</p>
<div class="itemizedlist"><ul>
<li><p>first_names is Jon. last_name is Salz.</p></li>
<li><p>first_names is Lars. last_name is Pind.</p></li>
<li><p>first_names is Michael. last_name is Yoon.</p></li>
</ul></div>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-detailed-pooling"></a>Sequence Pooling</h3></div></div>
<p>
The database library can transparently maintain pools of sequence values, so
that each request for a new sequence value (using <tt>db_nextval</tt>)
does not incur a roundtrip to the server. For instance, this functionality is
very useful in the security/sessions library, which very frequently allocates
values from the <tt>sec_id_seq</tt> sequence. To utilize this
functionality for a particular sequence, register the sequence to be pooled,
either using the <tt>db_register_pooled_sequence</tt> procedure at server
startup time, or by including a configuration parameter of the form
</p>
<pre class="programlisting">
PoolSequence.<span class="emphasis"><i>sequence_name_seq</i></span>=<span class="emphasis"><i>count</i></span>
</pre>
<p>
in <span class="emphasis"><i>any</i></span> configuration section in the <tt>yourservername.ini</tt>
file, e.g., e.g.,
</p>
<pre class="programlisting">
[ns/server/<span class="emphasis"><i>yourservername</i></span>/acs/security]
PoolSequence.sec_id_seq=20
</pre>
<p>
The database library will allocate this number of sequence values at server
startup. It will periodically scan pools and allocate new values for
sequences which are less than half-full. (This normally occurs every 60
seconds, and is configurable via the
<tt>PooledSequenceUpdateInterval</tt> parameter in the
<tt>[ns/server/</tt><span class="emphasis"><i><tt>yourservername</tt></i></span><tt>/acs/database]</tt> configuration
section.)
</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="dp-api-detailed-api"></a>API</h3></div></div>
<p>
Note that you never have to use <tt>ns_db</tt> anymore (including
<tt>ns_db gethandle</tt>)! Just start doing stuff, and (if you want) call
<tt>db_release_unused_handles</tt> when you're done as a hint to
release the database handle.
</p>
<div class="variablelist"><dl>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_abort_transaction"></a>db_abort_transaction</tt></i></span>
</span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_abort_transaction</i></span>
</pre>
<p>Aborts all levels of a transaction. That is if this is called within
several nested transactions, all of them are terminated. Use this insetead of
<tt>db_dml "abort" "abort transaction"</tt>.
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_null"></a>db_null</tt></i></span>
</span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i><tt>db_null</tt></i></span>
</pre>
<p>Returns a value which can be used in a bind variable to represent the SQL
value <tt>null</tt>. See <a href="db-api.html#dbapi_nulls_and_bind_vars" title="Nulls and Bind Variables">Nulls and Bind Variables</a>
above.</p>
</dd>
<dt><span class="term">
<span class="strong"><i><tt><a name="kernel.dbapi_db_foreach"></a>db_foreach</tt></i></span>
</span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_foreach</i></span> <span class="emphasis"><i>statement-name sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ] \
[ -column_array <span class="emphasis"><i>array_name</i></span> | -column_set <span class="emphasis"><i>set_name</i></span> ] \
<span class="emphasis"><i>code_block</i></span> [ if_no_rows <span class="emphasis"><i>if_no_rows_block ]</i></span>
</pre>
<p>Performs the SQL query <span class="emphasis"><i><tt>sql</tt></i></span>, executing
<span class="emphasis"><i><tt>code_block</tt></i></span> once for each row with variables set to
column values (or a set or array populated if <tt>-column_array</tt> or
<tt>column_set</tt> is specified). If the query returns no rows, executes
<span class="emphasis"><i><tt>if_no_rows_block</tt></i></span> (if provided). </p>
<p>Example:</p>
<pre class="programlisting">
db_foreach select_foo "select foo, bar from greeble" {
doc_body_append "<li>foo=$foo; bar=$bar\n"
} if_no_rows {
doc_body_append "<li>There are no greebles in the database.\n"
}
</pre>
<p>
The code block may contain <tt>break</tt> statements (which terminate the
loop and flush the database handle) and <tt>continue</tt> statements
(which continue to the next row of the loop). </p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_1row"></a>db_1row</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_1row</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ] \
[ -column_array <span class="emphasis"><i>array_name</i></span> | -column_set <span class="emphasis"><i>set_name</i></span> ]
</pre>
<p>Performs the SQL query <span class="emphasis"><i><tt>sql</tt></i></span>, setting variables to
column values. Raises an error if the query does not return exactly 1 row. </p>
<p>Example:</p>
<pre class="programlisting">
db_1row select_foo "select foo, bar from greeble where greeble_id = $greeble_id"
# Bombs if there's no such greeble!
# Now $foo and $bar are set.
</pre>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_0or1row"></a>db_0or1row</tt></i></span> </span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_0or1row</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ] \
[ -column_array <span class="emphasis"><i>array_name</i></span> | -column_set <span class="emphasis"><i>set_name</i></span> ]
</pre>
<p>Performs the SQL query <span class="emphasis"><i><tt>sql</tt></i></span>. If a row is returned,
sets variables to column values and returns 1. If no rows are returned,
returns 0. If more than one row is returned, throws an error. </p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_nextval"></a>db_nextval</tt></i></span> </span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_nextval</i></span> <span class="emphasis"><i>sequence-name</i></span>
</pre>
<p>Returns the next value for the sequence <span class="emphasis"><i>sequence-name</i></span> (using a
SQL statement like <tt>SELECT</tt> <span class="emphasis"><i><tt>sequence-name</tt></i></span><tt>.nextval FROM
DUAL</tt>). If sequence pooling is enabled for the sequence, transparently
uses a value from the pool if available to save a round-trip to the database
(see <span class="emphasis"><i><a href="db-api-detailed.html#db-api-detailed-pooling">Sequence Pooling</a></i></span>).
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_register_pooled_sequence"></a>db_register_pooled_sequence</tt></i></span> </span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_register_pooled_sequence</i></span> <span class="emphasis"><i>sequence-name</i></span> <span class="emphasis"><i>pool-size</i></span>
</pre>
<p>Registers the sequence <span class="emphasis"><i>sequence-name</i></span> to be pooled, with a pool
size of <span class="emphasis"><i>pool-size</i></span> sequence values (see <span class="emphasis"><i><a href="db-api-detailed.html#db-api-detailed-pooling">Sequence Pooling</a></i></span>).
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_string"></a>db_string</tt></i></span> </span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_string</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -default <span class="emphasis"><i>default</i></span> ] [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
</pre>
<p>Returns the first column of the result of SQL query
<span class="emphasis"><i><tt>sql</tt></i></span>. If <span class="emphasis"><i><tt>sql</tt></i></span> doesn't return a
row, returns <span class="emphasis"><i><tt>default</tt></i></span> (or throws an error if
<span class="emphasis"><i><tt>default</tt></i></span> is unspecified). Analogous to
<tt>database_to_tcl_string</tt> and
<tt>database_to_tcl_string_or_null</tt>.
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_list"></a>db_list</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_list</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
</pre>
<p>Returns a Tcl list of the values in the first column of the result of SQL
query <span class="emphasis"><i><tt>sql</tt></i></span>. If <span class="emphasis"><i><tt>sql</tt></i></span> doesn't
return any rows, returns an empty list. Analogous to
<tt>database_to_tcl_list</tt>.
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_list_of_lists"></a>db_list_of_lists</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_list_of_lists</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
</pre>
<p>Returns a Tcl list, each element of which is a list of all column values
in a row of the result of SQL query <span class="emphasis"><i><tt>sql</tt></i></span>. If
<span class="emphasis"><i><tt>sql</tt></i></span> doesn't return any rows, returns an empty list.
(Analogous to <tt>database_to_tcl_list_list</tt>.)
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_dml"></a>db_dml</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_dml</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> \
[ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ] \
[ -blobs <span class="emphasis"><i>blob_list</i></span> | -clobs <span class="emphasis"><i>clob_list</i></span> |
-blob_files <span class="emphasis"><i>blob_file_list</i></span> | -clob_files <span class="emphasis"><i>clob_file_list</i></span> ]
</pre>
<p>Performs the DML or DDL statement <span class="emphasis"><i><tt>sql</tt></i></span>. </p>
<p>If a length-<span class="emphasis"><i>n</i></span> list of blobs or clobs is provided, then the SQL
should return <span class="emphasis"><i>n</i></span> blobs or clobs into the bind variables
<tt>:1</tt>, <tt>:2</tt>, ... :<span class="emphasis"><i><tt>n</tt></i></span>.
<span class="emphasis"><i><tt>blobs</tt></i></span> or <span class="emphasis"><i><tt>clobs</tt></i></span>, if specified,
should be a list of individual BLOBs or CLOBs to insert;
<span class="emphasis"><i><tt>blob_files</tt></i></span> or <span class="emphasis"><i><tt>clob_files</tt></i></span>, if
specified, should be a list of <span class="emphasis"><i>paths to files</i></span> containing the data to
insert. Only one of <tt>-blobs</tt>, <tt>-clobs</tt>,
<tt>-blob_files</tt>, and <tt>-clob_files</tt> may be provided.</p>
<p>Example:</p>
<pre class="programlisting">
db_dml insert_photos "
insert photos(photo_id, image, thumbnail_image)
values(photo_id_seq.nextval, empty_blob(), empty_blob())
returning image, thumbnail_image into :1, :2
" -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"]
</pre>
<p>
This inserts a new row into the <tt>photos</tt> table, with the contents
of the files <tt>/var/tmp/the_photo</tt> and
<tt>/var/tmp/the_thumbnail</tt> in the <tt>image</tt> and
<tt>thumbnail</tt> columns, respectively.
</p>
</dd>
<dt><span class="term">
<span class="strong"><i><tt><a name="kernel.dbapi_db_write_clob"></a>db_write_clob</tt></i></span>,
<span class="strong"><i><tt><a name="kernel.dbapi_db_write_blob"></a>db_write_blob</tt></i></span>,
<span class="strong"><i><tt><a name="kernel.dbapi_db_blob_get_file"></a>db_blob_get_file</tt></i></span>
</span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_write_clob</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
<span class="strong"><i>db_write_blob</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
<span class="strong"><i>db_blob_get_file</i></span> <span class="emphasis"><i>statement-name</i></span> <span class="emphasis"><i>sql</i></span> [ -bind <span class="emphasis"><i>bind_set_id</i></span> | -bind <span class="emphasis"><i>bind_value_list</i></span> ]
</pre>
<p>Analagous to <tt>ns_ora write_clob/write_blob/blob_get_file</tt>.
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_release_unused_handles"></a>db_release_unused_handles</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_release_unused_handles</i></span>
</pre>
<p>Releases any allocated, unused database handles. </p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_transaction"></a>db_transaction</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_transaction</i></span> <span class="emphasis"><i>code_block</i></span> [ on_error { <span class="emphasis"><i>code_block</i></span> } ]
</pre>
<p>Executes <span class="emphasis"><i><tt>code_block</tt></i></span> transactionally. Nested
transactions are supported (<tt>end transaction</tt> is transparently
<tt>ns_db dml</tt>'ed when the outermost transaction completes). The
<tt>db_abort_transaction</tt> command can be used to abort all levels of
transactions. It is possible to specify an optional <tt>on_error</tt>
code block that will be executed if some code in <span class="emphasis"><i>code_block</i></span> throws
an exception. The variable <tt>errmsg</tt> will be bound in that scope.
If there is no <tt>on_error</tt> code, any errors will be propagated. </p>
<p>Example:</p>
<pre class="programlisting">
proc replace_the_foo { col } {
db_transaction {
db_dml "delete from foo"
db_dml "insert into foo(col) values($col)"
}
}
proc print_the_foo {} {
doc_body_append "foo is [db_string "select col from foo"]<br>\n"
}
replace_the_foo 8
print_the_foo ; # Writes out "foo is 8"
db_transaction {
replace_the_foo 14
print_the_foo ; # Writes out "foo is 14"
db_dml "insert into some_other_table(col) values(999)"
...
db_abort_transaction
} on_error {
doc_body_append "Error in transaction: $errmsg"
}
print_the_foo ; # Writes out "foo is 8"
</pre>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_resultrows"></a>db_resultrows</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_resultrows</i></span>
</pre>
<p>Returns the number of rows affected or returned by the previous
statement.
</p>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_with_handle"></a>db_with_handle</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_with_handle</i></span> <span class="emphasis"><i>var</i></span> <span class="emphasis"><i>code_block</i></span>
</pre>
<p>Places a database handle into the variable <span class="emphasis"><i><tt>var</tt></i></span> and
executes <span class="emphasis"><i><tt>code_block</tt></i></span>. This is useful when you don't
want to have to use the new API (<tt>db_foreach</tt>,
<tt>db_1row</tt>, etc.), but need to use database handles explicitly. </p>
<p>Example:</p>
<pre class="programlisting">
proc lookup_the_foo { foo } {
db_with_handle db {
return [db_string unused "select ..."]
}
}
db_with_handle db {
# Now there's a database handle in $db.
set selection [ns_db select $db "select foo from bar"]
while { [ns_db getrow $db $selection] } {
set_variables_after_query
lookup_the_foo $foo
}
}
</pre>
</dd>
<dt><span class="term"><span class="strong"><i><tt><a name="kernel.dbapi_db_nullify_empty_string"></a>db_nullify_empty_string</tt></i></span></span></dt>
<dd>
<pre class="programlisting">
<span class="strong"><i>db_nullify_empty_string</i></span> <span class="emphasis"><i>string</i></span>
</pre>
<p>For true SQL purists, we provide the convenience function
<span class="strong"><i><tt>db_nullify_empty_string</tt></i></span>, which returns
[db_null] if its <span class="emphasis"><i><tt>string</tt></i></span> argument is the empty string
and can be used to encapsulate another Oracle quirk: </p>
<pre class="programlisting">
set baz ""
# Clean out the foo table
#
db_dml unused "delete from foo"
db_dml unused "insert into foo(baz) values('$baz')"
set n_rows [db_string unused "select count(*) from foo where baz is null"]
#
# $n_rows is 1; in effect, the "baz is null" criterion is matching
# the empty string we just inserted (because of Oracle's coercion
# quirk)
</pre>
<p>
To balance out this asymmetry, you can explicitly set <tt>baz</tt> to
<tt>null</tt> by writing:
</p>
<pre class="programlisting">
db_dml foo_insert "insert into foo(baz) values(:1)" {[db_nullify_empty_string $baz]}
</pre>
</dd>
</dl></div>
<p><div class="cvstag">($Id: db-api-detailed.html,v 1.1 2002/07/09 17:34:57 rmello Exp $)</div></p>
</div>
</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left">
<a accesskey="p" href="rp-design.html">Prev</a> </td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right"> <a accesskey="n" href="tcl-doc.html">Next</a>
</td>
</tr>
<tr>
<td width="40%" align="left">OpenACS 4 Request Processor Design </td>
<td width="20%" align="center"><a accesskey="u" href="kernel-doc.html">Up</a></td>
<td width="40%" align="right"> Documenting Tcl Files: Page Contracts and Libraries</td>
</tr>
</table>
<hr>
<address>
rmello at fslc.usu.edu
</address>
<address><a href="mailto:vinod@kurup.com">
vinod@kurup.com
</a></address>
</div>
</body>
</html>