ACS Documentation : ACS Core Architecture Guide : Database Access API
There were three significant problems with the way ACS previously
used the database (i.e., directly through the ns_db
interface):
begin transaction
really means "turn auto-commit mode
off" and end transaction
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:
This would insert greeble #33 and do all the stuff inproc foo { db args } { ns_db dml $db "begin transaction" ... ns_db dml $db "end transaction" } ns_db dml $db "begin transaction" ns_db dml $db "insert into greeble(bork) values(33)" foo $db ns_db dml $db "insert into greeble(bork) values(50)" ns_db dml $db "end transaction"
foo
transactionally, but the end transaction
in foo
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.
set_variables_after_query
routine,
which relies on an uplevel variable named selection
(likewise for set_variables_after_subquery
and subselection
).
set_variables_after_query
set_variables_after_query
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:
Likedb_1row "select first_names, last_name from users where user_id = [ad_get_user_id]" ns_write "Hello, $first_names $last_name!"
ns_db 1row
, 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:
Selecting a bunch of rows is a lot prettier now:if { [db_0or1row "select first_names, last_name from users where user_id = [ad_get_user_id]"] } { ns_write "Hello, $first_names $last_name!" } else { # Executed if the query returns no rows. ns_write "There's no such user!" }
That's right,db_foreach "select first_names, last_name from users" { ns_write "Say hi to $first_names $last_name for me!<br>" }
db_foreach
is now like ns_db select
plus
a while
loop plus set_variables_after_query
plus
an if
statement (containing code to be
executed if no rows are returned).
db_foreach "select first_names, last_name from users where last_name like 'S%'" { ns_write "Say hi to $first_names $last_name for me!<br>" } if_no_rows { ns_write "There aren't any users with last names beginnings with S!" }
A new handle isn't actually allocated and released for every selection, of course - as a performance optimization, the API keeps old handles around untilad_write "<ul>" db_foreach "select first_names, last_name, user_id from users" { # Automatically allocated a database handle from the main pool. ad_write "<li>User $first_names $last_name\n<ul>" db_foreach "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. ad_write "<li>Member of group #$group_id.\n" } if_no_rows { # Not a member of any groups. ad_write "<li>Not a member of any group.\n" } } ad_write "</ul>" db_release_unused_handles
db_release_unused_handles
is invoked (or the script terminates).
Note that there is no analogue to ns_db gethandle
- the handle
is always automatically allocated the first time it's needed.
ns_db
anymore (including ns_db gethandle
)!
Just start doing stuff, and (if you want) call db_release_unused_handles
when you're done
as a hint to release the database handle.
db_foreach sql code_block [ if_no_rows if_no_rows_block ]
sql
, executing code_block
once for each row with
variables set to column values. If the query returns no rows, executes
if_no_rows_block
(if provided).
Example:
db_foreach "select foo, bar from greeble" { ns_write "<li>foo=$foo; bar=$bar\n" } if_no_rows { ns_write "<li>There are no greebles in the database.\n" }
db_1row sql
sql
, setting variables to column values.
Raises an error if the query does not return exactly 1 row.
Example:
db_1row "select foo, bar from greeble where greeble_id = $greeble_id" # Bombs if there's no such greeble! # Now $foo and $bar are set.
db_0or1row sql
sql
. 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.
db_string [ -default default ] sql
sql
. If sql
doesn't return a row, returns default
(or throws an error if default
is unspecified). Analogous to
database_to_tcl_string
and database_to_tcl_string_or_null
.
db_list sql
sql
.
If sql
doesn't return any rows, returns an empty list.
Analogous to database_to_tcl_list
.
db_list_of_lists sql
sql
.
If sql
doesn't return any rows, returns an empty list.
(Analogous to database_to_tcl_list_list
.)
db_dml [ -blobs blob_list | -clobs clob_list |
-blob_files blob_file_list | -clob_files clob_file_list ] sql
sql
.
If a length-n list of
blobs or clobs is provided, then the SQL should return n blobs or clobs
into the bind variables :1
, :2
, ... :n
.
blobs
or clobs
, if specified, should be a list
of individual BLOBs or CLOBs to insert;
blob_files
or clob_files
, if specified, should be a list
of paths to files containing the data to insert.
Only one of -blobs
, -clobs
, -blob_files
, and
-clob_files
may be provided.
Example:
This inserts a new row into thedb_dml -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"] \ " insert photos(photo_id, image, thumbnail_image) values(photo_id_seq.nextval, empty_blob(), empty_blob()) returning image, thumbnail_image into :1, :2 "
photos
table,
with the contents of the files /var/tmp/the_photo
and
/var/tmp/the_thumbnail
in the image
and
thumbnail
columns, respectively.
db_release_unused_handles
db_transaction code_block
code_block
transactionally. Nested transactions are supported
(end transaction
is transparently ns_db dml
'ed when the outermost
transaction completes). The db_abort_transaction
command can be used to abort all
levels of transactions.
Example:
proc replace_the_foo { col } { db_transaction { db_dml "delete from foo" db_dml "insert into foo(col) values($col)" } } proc print_the_foo {} { ad_write "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)" ... ad_abort_transaction } print_the_foo ; # Writes out "foo is 8"
db_resultrows
db_with_handle var code_block
var
and executes code_block
.
This is useful when you don't want to have to use the new API
(db_foreach
, db_1row
, etc.), but
need to use database handles explicitly.
Example:
proc lookup_the_foo { foo } { db_with_handle db { return [database_to_tcl_string $db "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 } }