| Prev | Chapter 6. OpenACS Developer's Guide | Next |
|---|
+
By Pete Su and Jon Salz. Modified by Roberto Mello. -
+
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.
More detailed information about the DB api is available at Database Access API. -
- Here's a typical block of code from an OpenACS 3.x dynamic page: -
-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
- - Writing code like this had the following annoyances: - -
- It was repetitive, tedious and error prone to write the same type of - loops over and over again. -
- 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. -
- Magic like set_variables_after_query made code confusing. -
- The scope of transactions is not clear from reading the code. -
- 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. -
- -
+
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. -
- Here is how you would code up the example above using the new API. + Here's an example of the API.
set count 0 @@ -113,7 +61,7 @@ from a Tcl variable to the database, which we'll cover next.
-
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 @@ -235,7 +183,7 @@ 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. -
Every db_* command accepting a SQL command as an argument +
Every db_* command accepting a SQL command as an argument supports bind variables. You can either
Specify the -bind switch to provide a set with bind variable values, or @@ -302,7 +250,7 @@ # of "administrator" } -
The database library can transparently maintain pools of sequence values, so that each request for a new sequence value (using db_nextval) does not incur a roundtrip to the server. For instance, this functionality is @@ -381,7 +329,7 @@ yourservername /acs/database] configuration section.) -
The Database API has several functions that wrap familiar parts of the AOLserver database API.
@@ -400,8 +348,8 @@ db_dml "abort" "abort transaction". -
-db_multirow [ -local ] [ -append ] [ -extend column_list ] \ +
+db_multirow [ -local ] [ -append ] [ -extend column_list ] \ var-name statement-name sql \ [ -bind bind_set_id | -bind bind_value_list ] \ code_block [ if_no_rows if_no_rows_block ] @@ -737,4 +685,4 @@
-