db-api.xml
Delivered as text/xml
[ hide source ] | [ make this the default ]
File Contents
<?xml version='1.0' ?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.4//EN"
"http://www.oasis-open.org/docbook/xml/4.4/docbookx.dtd" [
<!ENTITY % myvars SYSTEM "../variables.ent">
%myvars;
]>
<sect1 id="db-api" xreflabel="The OpenACS Database Access API">
<title>The OpenACS Database Access API</title>
<para>
By Pete Su and Jon Salz. Modified by Roberto Mello.
</para>
<sect2 id="db-api-overview">
<title>Overview</title>
<para>
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, and we have a
coherent API for database access which makes this even easier.
</para>
<para>
More detailed information about the DB API is available at
<xref linkend="db-api-detailed"/>.
</para>
</sect2>
<sect2 id="db-api-examples"><title>DB API Examples</title>
<para>
The OpenACS database API is meant to save developers from making
common mistakes and to provide a more structured syntax for
specifying database operations, including transactions. Here's
an example of the API.
</para>
<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 {
lappend rows [list $foo $bar $baz]
incr count
}
foreach row $rows {
call_some_proc $foo $bar $baz
}
}</programlisting>
<para>
There are several things to note here:
<orderedlist numeration="arabic">
<listitem>
<para>
No explicit code for grabbing and releasing handles. Usage of the
Database API implicitly deals with all handle management issues.
</para>
</listitem>
<listitem>
<para>
The <computeroutput>db_transaction</computeroutput> command
makes the scope of a transaction
clear; <computeroutput>db_transaction</computeroutput> takes the
code block argument and automatically runs it in the context of a
transaction. If you use something like db_foreach though, you need to
make sure that there are no calls in the code block which would take
a second db handle since the transaction is only valid for
one handle (that's why we build up a list of returned values and call
a second proc outside the db_foreach loop).
</para>
</listitem>
<listitem>
<para>
The command <computeroutput>db_foreach</computeroutput> writes
our old while loop for us.
</para>
</listitem>
<listitem>
<para>
Every SQL query has a name, which is used in conjunction with .XQL files
to support multiple databases.
</para>
</listitem>
<listitem>
<para>
Finally and most importantly, there API implements bind variables, which we will cover next.
</para>
</listitem>
</orderedlist>
</para>
</sect2>
<sect2 id="db-api-bindvariables"><title>Bind Variables</title>
<para>
Bind variables are placeholders for literal values in an SQL
query being sent to the server. In the old way, data was
generally passed to directly to the DB backend, via Tcl string
interpolation. In the example above, the query would look like:
</para>
<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'</programlisting>
<para>
There are a few problems with this:
</para>
<orderedlist>
<listitem>
<para>
If the value of $foo is a huge string, then we waste a lot
of time in the database server doing useless parsing.
</para>
</listitem>
<listitem>
<para>
Second, if the literal value contains characters like single
quotes, we have to be careful to properly escape them,
because not quoting them will lead to surprising errors.
</para>
</listitem>
<listitem>
<para>
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.
This type of attack,
called <emphasis>SQL smuggling</emphasis>, can be very
damaging - entire tables can be
exposed or have their contents deleted, for example.
</para>
<para>
Another very important reason for using bind variables is
performance. Oracle can cache 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 the
query cache considerably, which can make the server much
more efficient.
</para>
</listitem>
</orderedlist>
<para>
What the DB API (in conjunction with the database drivers
implemented for AOLserver) do is send the SQL statement to the
server for parsing, then <emphasis>bind</emphasis> values to the
variables and sends those values along separately as a second
step. This separate binding step is where the term
<emphasis>bind variable</emphasis> comes from.
</para>
<para>
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. The database API makes bind
variables easy to use by hooking them smoothly into the Tcl
run time so you simply provide :tclvar and the value of $tclvar
is sent to the backend to actually execute the query.
</para>
<para>
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
run time environment of the script, and passes them to the database.
</para>
<para>
Note that while this looks like a simple syntactic change, it really
is very different from how interpolated text queries work. 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:
</para>
<programlisting>
set table "baz"
set condition "where foo = bar"
db_foreach my_query { select :table from some_table where :condition }
</programlisting>
<para>
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 <emphasis>not the same thing at all</emphasis>.
</para>
<para>
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.
</para>
<sect3 id="db-api-bind-vars-usage" xreflabel="Bind Variables Usage">
<title>Usage</title>
<para>Every <computeroutput>db_*</computeroutput> command accepting a SQL command as an argument
supports bind variables. You can either</para>
<itemizedlist>
<listitem>
<para>
Specify the <computeroutput>-bind</computeroutput> switch to provide a set with bind variable
values, or
</para>
</listitem>
<listitem>
<para>
Specify the <computeroutput>-bind</computeroutput> switch to explicitly provide a list of
bind variable names and values, or
</para>
</listitem>
<listitem>
<para>
Not specify a bind variable list at all, in which case Tcl variables are
used as bind variables.
</para>
</listitem>
</itemizedlist>
<para>
The default behavior (i.e., if the <computeroutput>-bind</computeroutput> switch is omitted) is
that these procedures expect to find local variables that correspond in name
to the referenced bind variables, e.g.:
</para>
<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"
}
</programlisting>
<para>
The value of the local Tcl variable <computeroutput>user_id</computeroutput> (123456) is bound to
the <computeroutput>user_id</computeroutput> bind variable.
</para>
<para>The <computeroutput>-bind</computeroutput> switch can takes the name of an <computeroutput>ns_set</computeroutput>
containing keys for each bind variable named in the query, e.g.:</para>
<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"
}
</programlisting>
<para>
Alternatively, as an argument to <computeroutput>-bind</computeroutput> you can specify a list of
alternating name/value pairs for bind variables:
</para>
<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"
}
</programlisting>
</sect3>
<sect3 id="dbapi_nulls_and_bind_vars" xreflabel="Nulls and Bind Variables">
<title>Nulls and Bind Variables</title>
<para>
When processing a DML statement, Oracle coerces empty strings into
<computeroutput>null</computeroutput>. (This coercion does <emphasis>not</emphasis> occur in the
<computeroutput>WHERE</computeroutput> clause of a query, i.e.
<computeroutput>col = ''</computeroutput> and
<computeroutput>col is null</computeroutput> are not equivalent.)
</para>
<para>As a result, when using bind variables, the only way to make Oracle set a
column value to <computeroutput>null</computeroutput> 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".</para>
<para>These Oracle quirks complicate the process of writing clear and abstract
DML difficult. Here is an example that illustrates why:</para>
<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
</programlisting>
</sect3>
</sect2>
<sect2 id="db-api-pooling" xreflabel="Sequence Pooling">
<title>Sequence Pooling</title>
<para>
The database library can transparently maintain pools of sequence values, so
that each request for a new sequence value (using <computeroutput>db_nextval</computeroutput>)
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 <computeroutput>sec_id_seq</computeroutput> sequence. To utilize this
functionality for a particular sequence, register the sequence to be pooled,
either using the <computeroutput>db_register_pooled_sequence</computeroutput> procedure at server
startup time, or by including a configuration parameter of the form
</para>
<programlisting>
PoolSequence.<emphasis>sequence_name_seq</emphasis>=<emphasis>count</emphasis>
</programlisting>
<para>
in <emphasis>any</emphasis> configuration section in the <computeroutput>yourservername.ini</computeroutput>
file, e.g.,
</para>
<programlisting>
[ns/server/<emphasis>yourservername</emphasis>/acs/security]
PoolSequence.sec_id_seq=20
</programlisting>
<para>
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
<computeroutput>PooledSequenceUpdateInterval</computeroutput> parameter in the
<computeroutput>[ns/server/</computeroutput>
<emphasis><computeroutput>yourservername</computeroutput></emphasis>
<computeroutput>/acs/database]</computeroutput> configuration
section.)
</para>
</sect2>
<sect2 id="db-api-basicapi"><title>Basic API</title>
<para>
The Database API has several functions that wrap familiar parts of the
AOLserver database API.
</para>
<para>
Note that you never have to use <computeroutput>ns_db</computeroutput> anymore (including
<computeroutput>ns_db gethandle</computeroutput>)! Just start doing stuff, and (if you want) call
<computeroutput>db_release_unused_handles</computeroutput> when you're done as a hint to
release the database handle.
</para>
<variablelist>
<varlistentry>
<term>
<computeroutput>
<anchor
id="devguide.dbapi_db_abort_transaction"/>db_abort_transaction
</computeroutput>
</term>
<listitem>
<programlisting>
db_abort_transaction
</programlisting>
<para>Aborts all levels of a transaction. That is if this is called within
several nested transactions, all of them are terminated. Use this instead of
<computeroutput>db_dml "abort" "abort transaction"</computeroutput>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><emphasis role="strong"><computeroutput><anchor id="devguide.dbapi_db_multirow"/>db_multirow</computeroutput></emphasis></term>
<listitem>
<programlisting>
<emphasis role="strong">db_multirow</emphasis> [ -local ] [ -append ] [ -extend <emphasis>column_list</emphasis> ] \
<emphasis>var-name</emphasis> <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> \
[ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ] \
<emphasis>code_block</emphasis> [ if_no_rows <emphasis>if_no_rows_block ]</emphasis>
</programlisting>
<para>
Performs the SQL query <computeroutput>sql</computeroutput>, saving results in variables
of the form
<computeroutput><replaceable>var_name</replaceable>:1</computeroutput>, <computeroutput><replaceable>var_name</replaceable>:2</computeroutput>, etc,
setting <computeroutput><replaceable>var_name</replaceable>:rowcount</computeroutput> to the total number
of rows, and setting <computeroutput><replaceable>var_name</replaceable>:columns</computeroutput> to a
list of column names.
</para>
<para>
Each row also has a column, rownum, automatically
added and set to the row number, starting with 1. Note that this will
override any column in the SQL statement named 'rownum', also if you're
using the Oracle rownum pseudo-column.
</para>
<para>
If the <computeroutput>-local</computeroutput> is passed, the variables defined
by db_multirow will be set locally (useful if you're compiling dynamic templates
in a function or similar situations).
</para>
<para>
You may supply a code block, which will be executed for each row in
the loop. This is very useful if you need to make computations that
are better done in Tcl than in SQL, for example using ns_urlencode
or ns_quotehtml, etc. When the Tcl code is executed, all the columns
from the SQL query will be set as local variables in that code. Any
changes made to these local variables will be copied back into the
multirow.
</para>
<para>
You may also add additional, computed columns to the multirow, using the
<computeroutput>-extend { <replaceable>col_1</replaceable> <replaceable>col_2</replaceable> ... }</computeroutput> switch. This is
useful for things like constructing a URL for the object retrieved by
the query.
</para>
<para>
If you're constructing your multirow through multiple queries with the
same set of columns, but with different rows, you can use the
<computeroutput>-append</computeroutput> switch. This causes the rows returned by this query
to be appended to the rows already in the multirow, instead of starting
a clean multirow, as is the normal behavior. The columns must match the
columns in the original multirow, or an error will be thrown.
</para>
<para>
Your code block may call <computeroutput>continue</computeroutput> in order to skip a row
and not include it in the multirow. Or you can call <computeroutput>break</computeroutput>
to skip this row and quit looping.
</para>
<para>
Notice the nonstandard numbering (everything
else in Tcl starts at 0); the reason is that the graphics designer, a non
programmer, may wish to work with row numbers.
</para>
<para>
Example:
</para>
<programlisting>
db_multirow -extend { user_url } users users_query {
select user_id first_names, last_name, email from cc_users
} {
set user_url [acs_community_member_url -user_id $user_id]
}
</programlisting>
<para>
You can also iterate over a multirow after it has been
created - check the documentation for
template::multirow</para>
<para>
For example,
</para>
<programlisting>
db_multirow assets assets {
select asset_id,
from ...
}
..
set asset_id_l [list]
multirow foreach assets {
lappend asset_id_l $asset_id
}
</programlisting>
<para>Technically it's equivalent to using a code block on
the end of your db_multirow.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
<computeroutput>
<anchor id="devguide.dbapi_db_foreach"/>db_foreach
</computeroutput>
</term>
<listitem>
<programlisting>
db_foreach <emphasis>statement-name sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ] \
[ -column_array <emphasis>array_name</emphasis> | -column_set <emphasis>set_name</emphasis> ] \
<emphasis>code_block</emphasis> [ if_no_rows <emphasis>if_no_rows_block ]</emphasis>
</programlisting>
<para>
Performs the SQL query <emphasis>
<computeroutput>sql</computeroutput>
</emphasis>, executing
<emphasis><computeroutput>code_block
</computeroutput></emphasis> once for each row
with variables set to column values (or a set or array
populated if
<computeroutput>-column_array</computeroutput> or
<computeroutput>column_set</computeroutput> is
specified). If the query returns no rows, executes
<emphasis><computeroutput>if_no_rows_block
</computeroutput></emphasis> (if provided).
</para>
<para>Example:</para>
<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"
}
</programlisting>
<para>
The code block may contain <computeroutput>break</computeroutput> statements (which terminate the
loop and flush the database handle) and <computeroutput>continue</computeroutput> statements
(which continue to the next row of the loop). </para>
</listitem>
</varlistentry>
<varlistentry>
<term>
<computeroutput>
<anchor id="devguide.dbapi_db_1row"/>db_1row
</computeroutput>
</term>
<listitem>
<programlisting>
db_1row <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ] \
[ -column_array <emphasis>array_name</emphasis> | -column_set <emphasis>set_name</emphasis> ]
</programlisting>
<para>
Performs the SQL query <emphasis>
<computeroutput>sql</computeroutput></emphasis>,
setting variables to column values. Raises an error if the
query does not return exactly 1 row.
</para>
<para>Example:</para>
<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.
</programlisting>
</listitem>
</varlistentry>
<varlistentry>
<term>
<computeroutput>
<anchor id="devguide.dbapi_db_0or1row"/>db_0or1row
</computeroutput>
</term>
<listitem>
<programlisting>
db_0or1row <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ] \
[ -column_array <emphasis>array_name</emphasis> | -column_set <emphasis>set_name</emphasis> ]
</programlisting>
<para>
Performs the SQL query
<emphasis><computeroutput>sql</computeroutput></emphasis>.
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.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_nextval"/>db_nextval</computeroutput> </term>
<listitem>
<programlisting>
db_nextval <emphasis>sequence-name</emphasis>
</programlisting>
<para>
Returns the next value for the sequence <emphasis>sequence-name</emphasis> (using a
SQL statement like <computeroutput>SELECT</computeroutput>
<emphasis><computeroutput>sequence-name</computeroutput></emphasis><computeroutput>.nextval FROM
DUAL</computeroutput>). 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 <emphasis><xref linkend="db-api-pooling"/></emphasis>).
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
<computeroutput>
<anchor
id="devguide.dbapi_db_register_pooled_sequence"/>db_register_pooled_sequence
</computeroutput>
</term>
<listitem>
<programlisting>
db_register_pooled_sequence <emphasis>sequence-name</emphasis> <emphasis>pool-size</emphasis>
</programlisting>
<para>Registers the sequence <emphasis>sequence-name</emphasis> to be pooled, with a pool
size of <emphasis>pool-size</emphasis> sequence values
(see <emphasis><xref linkend="db-api-pooling"/></emphasis>).
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_string"/>db_string</computeroutput> </term>
<listitem>
<programlisting>
db_string <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -default <emphasis>default</emphasis> ] [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
</programlisting>
<para>Returns the first column of the result of SQL query
<emphasis><computeroutput>sql</computeroutput></emphasis>.
If <emphasis><computeroutput>sql</computeroutput></emphasis> doesn't return a
row, returns
<emphasis><computeroutput>default</computeroutput></emphasis>
(or throws an error if
<emphasis><computeroutput>default</computeroutput></emphasis> is unspecified). Analogous to
<computeroutput>database_to_tcl_string</computeroutput> and
<computeroutput>database_to_tcl_string_or_null</computeroutput>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_list"/>db_list</computeroutput></term>
<listitem>
<programlisting>
db_list <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
</programlisting>
<para>Returns a Tcl list of the values in the first column of the result of SQL
query
<emphasis><computeroutput>sql</computeroutput></emphasis>.
If <emphasis><computeroutput>sql</computeroutput></emphasis> doesn't
return any rows, returns an empty list. Analogous to
<computeroutput>database_to_tcl_list</computeroutput>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_list_of_lists"/>db_list_of_lists</computeroutput></term>
<listitem>
<programlisting>
db_list_of_lists <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
</programlisting>
<para>Returns a Tcl list, each element of which is a list of all column values
in a row of the result of SQL query <emphasis><computeroutput>sql</computeroutput></emphasis>. If
<emphasis><computeroutput>sql</computeroutput></emphasis> doesn't return any rows, returns an empty list.
(Analogous to <computeroutput>database_to_tcl_list_list</computeroutput>.)
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_dml"/>db_dml</computeroutput></term>
<listitem>
<programlisting>
db_dml <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> \
[ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ] \
[ -blobs <emphasis>blob_list</emphasis> | -clobs <emphasis>clob_list</emphasis> |
-blob_files <emphasis>blob_file_list</emphasis> | -clob_files <emphasis>clob_file_list</emphasis> ]
</programlisting>
<para>Performs the DML or DDL statement <emphasis><computeroutput>sql</computeroutput></emphasis>. </para>
<para>If a length-<emphasis>n</emphasis> list of blobs or clobs is provided, then the SQL
should return <emphasis>n</emphasis> blobs or clobs into the bind variables
<computeroutput>:1</computeroutput>, <computeroutput>:2</computeroutput>, ... :<emphasis><computeroutput>n</computeroutput></emphasis>.
<emphasis><computeroutput>blobs</computeroutput></emphasis> or <emphasis><computeroutput>clobs</computeroutput></emphasis>, if specified,
should be a list of individual BLOBs or CLOBs to insert;
<emphasis><computeroutput>blob_files</computeroutput></emphasis> or <emphasis><computeroutput>clob_files</computeroutput></emphasis>, if
specified, should be a list of <emphasis>paths to files</emphasis> containing the data to
insert. Only one of <computeroutput>-blobs</computeroutput>, <computeroutput>-clobs</computeroutput>,
<computeroutput>-blob_files</computeroutput>, and <computeroutput>-clob_files</computeroutput> may be provided.</para>
<para>Example:</para>
<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"]
</programlisting>
<para>
This inserts a new row into the <computeroutput>photos</computeroutput> table, with the contents
of the files <computeroutput>/var/tmp/the_photo</computeroutput> and
<computeroutput>/var/tmp/the_thumbnail</computeroutput> in the <computeroutput>image</computeroutput> and
<computeroutput>thumbnail</computeroutput> columns, respectively.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
<computeroutput><anchor id="devguide.dbapi_db_write_clob"/>db_write_clob</computeroutput>,
<computeroutput><anchor id="devguide.dbapi_db_write_blob"/>db_write_blob</computeroutput>,
<computeroutput><anchor id="devguide.dbapi_db_blob_get_file"/>db_blob_get_file</computeroutput>
</term>
<listitem>
<programlisting>
db_write_clob <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
db_write_blob <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
db_blob_get_file <emphasis>statement-name</emphasis> <emphasis>sql</emphasis> [ -bind <emphasis>bind_set_id</emphasis> | -bind <emphasis>bind_value_list</emphasis> ]
</programlisting>
<para>Analogous to <computeroutput>ns_ora write_clob/write_blob/blob_get_file</computeroutput>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_release_unused_handles"/>db_release_unused_handles</computeroutput></term>
<listitem>
<programlisting>
db_release_unused_handles
</programlisting>
<para>Releases any allocated, unused database handles. </para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_transaction"/>db_transaction</computeroutput></term>
<listitem>
<programlisting>
db_transaction <emphasis>code_block</emphasis> [ on_error { <emphasis>code_block</emphasis> } ]
</programlisting>
<para>Executes <emphasis><computeroutput>code_block</computeroutput></emphasis> transactionally. Nested
transactions are supported (<computeroutput>end transaction</computeroutput> is transparently
<computeroutput>ns_db dml</computeroutput>'ed when the outermost transaction completes). The
<computeroutput>db_abort_transaction</computeroutput> command can be used to abort all levels of
transactions. It is possible to specify an optional <computeroutput>on_error</computeroutput>
code block that will be executed if some code in <emphasis>code_block</emphasis> throws
an exception. The variable <computeroutput>errmsg</computeroutput> will be bound in that scope.
If there is no <computeroutput>on_error</computeroutput> code, any errors will be propagated. </para>
<para>Example:</para>
<programlisting>
proc replace_the_foo { col } {
db_transaction {
db_dml delete {delete from foo}
db_dml insert {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_foo {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"
</programlisting>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_resultrows"/>db_resultrows</computeroutput></term>
<listitem>
<programlisting>
db_resultrows
</programlisting>
<para>Returns the number of rows affected or returned by the previous
statement.
</para></listitem>
</varlistentry>
<varlistentry>
<term><computeroutput><anchor id="devguide.dbapi_db_with_handle"/>db_with_handle</computeroutput></term>
<listitem>
<programlisting>
db_with_handle <emphasis>var</emphasis> <emphasis>code_block</emphasis>
</programlisting>
<para>Places a database handle into the variable <emphasis><computeroutput>var</computeroutput></emphasis> and
executes <emphasis><computeroutput>code_block</computeroutput></emphasis>. This is useful when you don't
want to have to use the new API (<computeroutput>db_foreach</computeroutput>,
<computeroutput>db_1row</computeroutput>, etc.), but need to use database handles explicitly. </para>
<para>Example:</para>
<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
}
}
</programlisting>
</listitem>
</varlistentry>
</variablelist>
<para>
<phrase role="cvstag">($Id: db-api.xml,v 1.17.2.4 2023/07/10 08:36:09 gustafn Exp $)</phrase>
</para>
</sect2>
<sect2 id="db-api-caching"><title>Caching Database API Results</title>
<para>The database API allows for direct caching of query results. Repeated calls will
return the cached value until it is either explicitly flushed using db_flush_cache,
times out (configured the ns_cache is called to create the cache), or another cached
query fills the cache, causing older entries to be flushed.
</para>
<para>Values returned by a query are cached if you pass the "-cache_key" switch
to the database procedure. The switch value will be used as the key in the
ns_cache eval call used to execute the query and processing code. The
db_flush proc should be called to flush the cache when appropriate. The
"-cache_pool" parameter can be used to specify the cache pool to be used,
and defaults to db_cache_pool. The size of the default cache is governed
by the kernel parameter "DBCacheSize" in the "caching" section.
</para>
<para>
Currently db_string, db_list, db_list_of_lists, db_1row, db_0or1row, and db_multirow support
caching.
</para>
<para>For caching to be effective, one must carefully design a cache_pool and cache_key
strategy that uniquely identifies a query within the system, including the relevant
objects being referenced by the query. Typically a cache_key should include one or
more object_ids and a name that identifies the operation being done.
</para>
<para>Here is an example from the layout-manager package:</para>
<programlisting>
# Query to return the elements of a page as a list. The prefix "page_" is used to denote
# that this is a page-related query, page_id is used to uniquely identify the query
# by object, and the suffix uniquely defines the operation being performed on the
# page object.
db_list -cache_key page_${page_id}_get_elements get_elements {}
# When the contents of a page are changed, we flush all page-related queries for the given
# page object using db_flush_cache.
db_flush_cache -cache_key_pattern page_${page_id}_*
</programlisting>
</sect2>
</sect1>
