<!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>The OpenACS 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="dev-guide.html" title="Chapter 4. OpenACS Developer's Guide">
<link rel="previous" href="request-processor.html" title="The Request Processor">
<link rel="next" href="templates.html" title="Using Templates in OpenACS 4.5">
<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="request-processor.html">Prev</a> </td>
<th width="60%" align="center">Chapter 4. OpenACS Developer's Guide</th>
<td width="20%" align="right"> <a accesskey="n" href="templates.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"></a>The OpenACS Database Access API</h2></div></div>
<p>
By <a href="mailto:psu@arsdigita.com" target="_top">Pete Su</a> and
<a href="mailto:jsalz@mit.edu" target="_top">Jon Salz</a>. Modified
by Roberto Mello.
</p>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-overview"></a>Overview</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>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-theoldway"></a>The Old Way</h3></div></div>
<p>
Here's a typical block of code from an OpenACS 3.x dynamic page:
</p>
<pre class="programlisting">
set tcl_var "foo"
set db [ns_db gethandle]
ns_db dml $db "begin transaction"
set sql "select
foo,
bar,
baz
from some_table,
some_other_table
where some_table.id = some_other_table.id
and some_table.condition_p = '$tcl_var'
"
set selection [ns_db select $db $sql]
set count 0
while { [ns_db getrow $db $selection] } {
set_variables_after_query
...
call_some_proc $foo $bar $baz
incr count
}
ns_db releasehandle $db
</pre>
<p>
Writing code like this had the following annoyances:
<div class="itemizedlist"><ul>
<li style="list-style-type: opencircle"><p>
It was repetitive, tedious and error prone to write the same type of
loops over and over again.
</p></li>
<li style="list-style-type: opencircle"><p>
Using Tcl variable interpolation in a literal string, to pass values
from the page to the database, is error prone, relatively inefficient,
and a good way to compromise the security of a web site.
</p></li>
<li style="list-style-type: opencircle"><p>
Magic like <tt>set_variables_after_query</tt> made code confusing.
</p></li>
<li style="list-style-type: opencircle"><p>
The scope of transactions is not clear from reading the code.
</p></li>
<li style="list-style-type: opencircle"><p>
Passing handles around explicitly made it easy to use them in bad
ways, like holding a handle for too long while returning data to a
user's browser.
</p></li>
</ul></div>
</p>
<p>
Introduced in ACS 3.4, the new Database API is meant to save
developers from the above tedium and provide a more structured syntax
for specifying database operations, including transactions.
</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-thenewway"></a>The New Way</h3></div></div>
<p>
Here is how you would code up the example above using the new API.
</p>
<pre class="programlisting">
set count 0
set tcl_var "foo"
set sql {
select
foo,
bar,
baz
from some_table, some_other_table
where some_table.id = some_other_table.id
and some_table.condition_p = :tcl_var
}
db_transaction {
db_foreach my_example_query_name $sql {
...
call_some_proc $foo $bar $baz
incr count
}
}
</pre>
<p>
There are several things to note here:
<div class="orderedlist"><ol type="1">
<li><p>
No explicit code for grabbing and releasing handles. Usage of the
Database API implicitly deals with all handle management issues.
</p></li>
<li><p>
The new command <tt>db_transaction</tt>
makes the scope of a transaction
clear. <tt>db_transaction</tt> takes the
code block argument and automatically runs it in the context of a
transaction.
</p></li>
<li><p>
The new command <tt>db_foreach</tt> writes
our old while loop for us.
</p></li>
<li><p>
Every SQL query has a name, meant to be unique within the server
instance (though this is not enforced).
</p></li>
<li><p>
Finally and most importantly, there is a new scheme for passing data
from a Tcl variable to the database, which we'll cover next.
</p></li>
</ol></div>
</p>
</div>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-bindvariables"></a>Bind Variables</h3></div></div>
<p>
Bind variables are placeholders for literal values in an SQL query
being sent to the server. Take the example query above: in the old
way, data was generally passed to Oracle directly, via Tcl string
interpolation. So in the example above, the actual query we send would
look like this:
</p>
<pre class="programlisting">
select
foo,
bar,
baz
from some_table, some_other_table
where some_table.id=some_other_table.id
and some_table.condition_p = 'foo'
</pre>
<p>
There are a few problems with this:
</p>
<div class="orderedlist"><ol type="1">
<li>
If the literal value is a
huge string, then we waste a lot of time in the database server doing
useless parsing.
</li>
<li>
Second, if the literal value contains characters like
single quotes, we have to be careful to double-quote them, because not
quoting them will lead to surprising errors.
</li>
<li>
<p>
Third, no type checking
occurs on the literal value. Finally, if the Tcl variable is passed in
or between web forms or otherwise subject to external modification,
there is nothing keeping malicious users from setting the Tcl variable
to some string that changes the query textually.
</p>
<p>
This type of attack,
called <span class="emphasis"><i>SQL smuggling</i></span>, can be very
damaging - entire tables can be
exposed or have their contents deleted, for example. Another very
important reason for using bind variables is performance. Oracle caches
all previously parsed queries. If there are values in the where clause,
that is how the query is cached. It also performs bind variable
susbstitution after parsing the SQL statement. This means that SQL
statements that use bind variables will always match (assuming all else is
the same) while SQL statements that do not use bind variables will not
match unless the values in the statement are exactly the same. This will
improve performance considerably.
</p>
</li>
</ol></div>
<p>
To fix all these problems, we replace literal values in the query with
a placeholder character, and then send the data along after. So the
query looks like this:
</p>
<pre class="programlisting">
select
foo,
bar,
baz
from some_table, some_other_table
where some_table.id = some_other_table.id
and some_table.condition_p = ?
</pre>
<p>
The '?' character means "This will be filled in later with literal
data". In use, you might write code that looks like this:
</p>
<pre class="programlisting">
set statement [prepare_query "
select
foo,
bar,
baz
from some_table, some_other_table
where some_table.id = some_other_table.id
and some_table.condition_p = ?
"]
[bind_param $statement 1 $tcl_var]
</pre>
<p>
The above example is meant to be psuedo-Tcl - no API like this
actually exists. What happens is that we first send the SQL statement
to the server for parsing, then later we <span class="emphasis"><i>bind</i></span> values to the
placeholders, and send those values along seperately. This seperate
binding step is where the term <span class="emphasis"><i>bind variable</i></span> comes from.
</p>
<p>
This split has several advantages. First, type checking happens on the
literal. If the column we are comparing against holds numbers, and we
send a string, we get a nice error. Second, since string literals are
no longer in the query, no extra quoting is required. Third,
substitution of bind variables cannot change the actual text of the
query, only the literal values in the placeholders.
</p>
<p>
The database API makes bind variables easy to use by hooking them
smoothly into the Tcl runtime. Rather than using a '?' as a generic
placeholder, you use a colon followed by the name of the Tcl variable
that you wish to pass as a literal. So here's the final, real-life
form of the example query:
</p>
<pre class="programlisting">
select
foo,
bar,
baz
from some_table, some_other_table
where some_table.id = some_other_table.id
and some_table.condition_p = :tcl_var
</pre>
<p>
The database API parses the query and pulls out all the bind variable
specifications and replaces them with generic placeholders. It then
automatically pulls the values of the named Tcl vars out of the
runtime environment of the script, and passes them to the database.
</p>
<p>
Note that while this looks like a simple syntactic change, it really
is very different from how we've written queries in the past. You use
bind variables to replace what would otherwise be a literal value in a
query, and Tcl style string interpolation does not happen. So you
cannot do something like:
</p>
<pre class="programlisting">
set table "baz"
set condition "where foo = bar"
db_foreach my_query { select :table from some_table where :condition }
</pre>
<p>
SQL will not allow a literal to occur where we've put the bind
variables, so the query is syntactically incorrect. You have to
remember that while the bind variable syntax looks similar to variable
interpolation in Tcl, it is <span class="emphasis"><i>not the same thing at all</i></span>.
</p>
<p>
Finally, the DB API has several different styles for passing bind
variable values to queries. In general, use the style presented here
because it is the most convenient.
</p>
<div class="sect3">
<div class="titlepage"><div><h4 class="title">
<a name="db-api-bind-vars-usage"></a>Usage</h4></div></div>
<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>
</div>
<div class="sect3">
<div class="titlepage"><div><h4 class="title">
<a name="dbapi_nulls_and_bind_vars"></a>Nulls and Bind Variables</h4></div></div>
<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): <tt>db_null</tt>.</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>
<div class="sect2">
<div class="titlepage"><div><h3 class="title">
<a name="db-api-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="db-api-basicapi"></a>Basic API</h3></div></div>
<p>
The Database API has several functions that wrap familiar parts of the
AOLserver database API.
</p>
<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">
<tt>
<a name="devguide.dbapi_db_abort_transaction"></a>db_abort_transaction
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_abort_transaction
</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">
<tt>
<a name="devguide.dbapi_db_null"></a>db_null
</tt>
</span></dt>
<dd>
<pre class="programlisting">
<tt>db_null</tt>
</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">
<tt>
<a name="devguide.dbapi_db_foreach"></a>db_foreach
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_foreach <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">
<tt>
<a name="devguide.dbapi_db_1row"></a>db_1row
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_1row <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">
<tt>
<a name="devguide.dbapi_db_0or1row"></a>db_0or1row
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_0or1row <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"><tt><a name="devguide.dbapi_db_nextval"></a>db_nextval</tt> </span></dt>
<dd>
<pre class="programlisting">
db_nextval <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.html#db-api-pooling">Sequence Pooling</a></i></span>).
</p>
</dd>
<dt><span class="term">
<tt>
<a name="devguide.dbapi_db_register_pooled_sequence"></a>db_register_pooled_sequence
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_register_pooled_sequence <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.html#db-api-pooling">Sequence Pooling</a></i></span>).
</p>
</dd>
<dt><span class="term"><tt><a name="devguide.dbapi_db_string"></a>db_string</tt> </span></dt>
<dd>
<pre class="programlisting">
db_string <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"><tt><a name="devguide.dbapi_db_list"></a>db_list</tt></span></dt>
<dd>
<pre class="programlisting">
db_list <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"><tt><a name="devguide.dbapi_db_list_of_lists"></a>db_list_of_lists</tt></span></dt>
<dd>
<pre class="programlisting">
db_list_of_lists <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"><tt><a name="devguide.dbapi_db_dml"></a>db_dml</tt></span></dt>
<dd>
<pre class="programlisting">
db_dml <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">
<tt><a name="devguide.dbapi_db_write_clob"></a>db_write_clob</tt>,
<tt><a name="devguide.dbapi_db_write_blob"></a>db_write_blob</tt>,
<tt><a name="devguide.dbapi_db_blob_get_file"></a>db_blob_get_file</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_write_clob <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> ]
db_write_blob <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> ]
db_blob_get_file <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"><tt><a name="devguide.dbapi_db_release_unused_handles"></a>db_release_unused_handles</tt></span></dt>
<dd>
<pre class="programlisting">
db_release_unused_handles
</pre>
<p>Releases any allocated, unused database handles. </p>
</dd>
<dt><span class="term"><tt><a name="devguide.dbapi_db_transaction"></a>db_transaction</tt></span></dt>
<dd>
<pre class="programlisting">
db_transaction <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"><tt><a name="devguide.dbapi_db_resultrows"></a>db_resultrows</tt></span></dt>
<dd>
<pre class="programlisting">
db_resultrows
</pre>
<p>Returns the number of rows affected or returned by the previous
statement.
</p>
</dd>
<dt><span class="term"><tt><a name="devguide.dbapi_db_with_handle"></a>db_with_handle</tt></span></dt>
<dd>
<pre class="programlisting">
db_with_handle <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">
<tt>
<a name="devguide.dbapi_db_nullify_empty_string"></a>db_nullify_empty_string
</tt>
</span></dt>
<dd>
<pre class="programlisting">
db_nullify_empty_string <span class="emphasis"><i>string</i></span>
</pre>
<p>For true SQL purists, we provide the convenience function
<tt>db_nullify_empty_string</tt>, 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.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="request-processor.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="templates.html">Next</a>
</td>
</tr>
<tr>
<td width="40%" align="left">The Request Processor </td>
<td width="20%" align="center"><a accesskey="u" href="dev-guide.html">Up</a></td>
<td width="40%" align="right"> Using Templates in OpenACS 4.5</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>