The OpenACS Database Access API

+The OpenACS Database Access API

Prev	Chapter�11.�Development Reference	Next

The OpenACS Database Access API

By Pete Su and Jon Salz. Modified by Roberto Mello. -

Overview

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.

More detailed information about the DB api is available at Database Access API. -

DB API Examples

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 @@ -38,17 +38,17 @@ No explicit code for grabbing and releasing handles. Usage of the Database API implicitly deals with all handle management issues.

- The db_transaction command + The db_transaction command makes the scope of a transaction - clear; db_transaction takes the + clear; db_transaction 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 (thats why we build up a list of returned values and call a second proc outside the db_foreach loop).

- The command db_foreach writes + The command db_foreach writes our old while loop for us.

Every SQL query has a name, which is used in conjunction with .XQL files @@ -57,7 +57,7 @@ Finally and most importantly, there API implements bind variables, which we will cover next.

Bind Variables

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 @@ -141,18 +141,18 @@ 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. -

Usage

Every db_* command accepting a SQL command as an argument +

Usage

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 + Specify the -bind switch to provide a set with bind variable values, or
- Specify the -bind switch to explicitly provide a list of + Specify the -bind switch to explicitly provide a list of bind variable names and values, or
Not specify a bind variable list at all, in which case Tcl variables are used as bind variables.

- The default behavior (i.e., if the -bind switch is omitted) is + The default behavior (i.e., if the -bind switch is omitted) is that these procedures expect to find local variables that correspond in name to the referenced bind variables, e.g.:

@@ -172,9 +172,9 @@
 }

- The value of the local Tcl variable user_id (123456) is bound to - the user_id bind variable. -

The -bind switch can takes the name of an ns_set + The value of the local Tcl variable user_id (123456) is bound to + the user_id bind variable. +

The -bind switch can takes the name of an ns_set containing keys for each bind variable named in the query, e.g.:

 
 set bind_vars [ns_set create]
@@ -193,7 +193,7 @@
 }

- Alternatively, as an argument to -bind you can specify a list of + Alternatively, as an argument to -bind you can specify a list of alternating name/value pairs for bind variables:

 	
@@ -208,14 +208,14 @@
     # of "administrator"
 }
 
-

Nulls and Bind Variables

When processing a DML statement, Oracle coerces empty strings into - null. (This coercion does not occur in the - WHERE clause of a query, i.e. - col = '' and - col is null are not equivalent.) + null. (This coercion does not occur in the + WHERE clause of a query, i.e. + col = '' and + col is null are not equivalent.)

As a result, when using bind variables, the only way to make Oracle set a - column value to null is to set the corresponding bind variable + column value to null 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".

These Oracle quirks complicate the process of writing clear and abstract @@ -241,32 +241,32 @@

Since databases other than Oracle do not coerce empty strings into - null, this code has different semantics depending on the + null, 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.

Therefore, the Database Access API provides a database-independent way to - represent null (instead of the Oracle-specific idiom of the - empty string): db_null.

Use it instead of the empty string whenever you want to set a column value - explicitly to null, e.g.:

set bar [db_null]
+	represent null (instead of the Oracle-specific idiom of the
+	empty string): db_null.
Use it instead of the empty string whenever you want to set a column value
+	explicitly to null, e.g.:
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

Sequence Pooling

+# sets the values for both the "bar" and "baz" columns to null

Sequence Pooling

The database library can transparently maintain pools of sequence values, so - that each request for a new sequence value (using db_nextval) + that each request for a new sequence value (using db_nextval) 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 sec_id_seq sequence. To utilize this + values from the sec_id_seq sequence. To utilize this functionality for a particular sequence, register the sequence to be pooled, - either using the db_register_pooled_sequence procedure at server + either using the db_register_pooled_sequence procedure at server startup time, or by including a configuration parameter of the form

 
 PoolSequence.sequence_name_seq=count

- in any configuration section in the yourservername.ini + in any configuration section in the yourservername.ini file, e.g.,

 
@@ -278,49 +278,49 @@
       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
-      PooledSequenceUpdateInterval parameter in the
-      [ns/server/
-      yourservername
-      /acs/database] configuration
+      PooledSequenceUpdateInterval parameter in the
+      [ns/server/
+      yourservername
+      /acs/database] configuration
       section.) 
-

Basic API

The Database API has several functions that wrap familiar parts of the AOLserver database API.

- Note that you never have to use 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 + Note that you never have to use 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_abort_transaction -+

 db_abort_transaction

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 - db_dml "abort" "abort transaction". + db_dml "abort" "abort transaction". -

db_multirow

-db_multirow [ -local ] [ -append ] [ -extend column_list ] \
+

db_multirow

+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 ]

- Performs the SQL query sql, saving results in variables + Performs the SQL query sql, saving results in variables of the form - var_name:1, var_name:2, etc, - setting var_name:rowcount to the total number - of rows, and setting var_name:columns to a + var_name:1, var_name:2, etc, + setting var_name:rowcount to the total number + of rows, and setting var_name:columns to a list of column names.

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.

- If the -local is passed, the variables defined + If the -local 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).

@@ -333,19 +333,19 @@ multirow.

You may also add additional, computed columns to the multirow, using the - -extend { col_1 col_2 ... } switch. This is + -extend { col_1 col_2 ... } switch. This is useful for things like constructing a URL for the object retrieved by the query.

If you're constructing your multirow through multiple queries with the same set of columns, but with different rows, you can use the - -append switch. This causes the rows returned by this query + -append 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.

- Your code block may call continue in order to skip a row - and not include it in the multirow. Or you can call break + Your code block may call continue in order to skip a row + and not include it in the multirow. Or you can call break to skip this row and quit looping.

Notice the nonstandard numbering (everything @@ -379,42 +379,42 @@

Technically it's equivalent to using a code block on the end of your db_multirow.

- + db_null -+

-db_null
+db_null

Returns a value which can be used in a bind variable to represent the SQL value - null. + null. See Nulls and Bind Variables above.

- + db_foreach -+

 db_foreach statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ] \
     code_block [ if_no_rows if_no_rows_block ]

Performs the SQL query - sql + sql , executing - code_block - once for each row + code_block + once for each row with variables set to column values (or a set or array populated if - -column_array or - column_set is + -column_array or + column_set is specified). If the query returns no rows, executes - if_no_rows_block - (if provided). + if_no_rows_block + (if provided).

Example:

 
 db_foreach select_foo "select foo, bar from greeble" {
@@ -424,20 +424,20 @@
 }

- The code block may contain break statements (which terminate the - loop and flush the database handle) and continue statements + The code block may contain break statements (which terminate the + loop and flush the database handle) and continue statements (which continue to the next row of the loop).

- + db_1row -+

 db_1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]

Performs the SQL query - sql, + sql, setting variables to column values. Raises an error if the query does not return exactly 1 row.

Example:

@@ -448,82 +448,82 @@

- + db_0or1row -+

 db_0or1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]

Performs the SQL query - 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_nextval

 db_nextval sequence-name

Returns the next value for the sequence sequence-name (using a - SQL statement like SELECT - sequence-name.nextval FROM - DUAL). If sequence pooling is enabled for the sequence, transparently + SQL statement like SELECT + sequence-name.nextval FROM + DUAL). 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 Sequence Pooling).

- + db_register_pooled_sequence -+

 db_register_pooled_sequence sequence-name pool-size

Registers the sequence sequence-name to be pooled, with a pool size of pool-size sequence values (see Sequence Pooling). -

db_string

 db_string statement-name sql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]

Returns the first column of the result of SQL query - sql. - If sql doesn't return a + sql. + If sql doesn't return a row, returns - default + default (or throws an error if - default is unspecified). Analogous to - database_to_tcl_string and - database_to_tcl_string_or_null. + default is unspecified). Analogous to + database_to_tcl_string and + database_to_tcl_string_or_null. -

db_list

 db_list statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

Returns a Tcl list of the values in the first column of the result of SQL query - sql. - If sql doesn't + sql. + If sql doesn't return any rows, returns an empty list. Analogous to - database_to_tcl_list. + database_to_tcl_list. -

db_list_of_lists

 db_list_of_lists statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

Returns a Tcl list, each element of which is a list of all column values - in a row of the result of SQL query sql. If - sql doesn't return any rows, returns an empty list. - (Analogous to database_to_tcl_list_list.) + in a row of the result of SQL query sql. If + sql doesn't return any rows, returns an empty list. + (Analogous to database_to_tcl_list_list.) -

db_dml

 db_dml statement-name sql \
     [ -bind bind_set_id | -bind bind_value_list ] \
     [ -blobs blob_list | -clobs clob_list |
       -blob_files blob_file_list | -clob_files clob_file_list ]
-

Performs the DML or DDL statement sql.

If a length-n list of blobs or clobs is provided, then the SQL +

Performs the DML or DDL statement 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, + :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 + 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:

+	    insert. Only one of -blobs, -clobs,
+	    -blob_files, and -clob_files may be provided.
Example:
 
 db_dml insert_photos "
         insert photos(photo_id, image, thumbnail_image)
@@ -532,37 +532,37 @@
     "  -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"] 
 
 	  

-	    This inserts a new row into the photos table, with the contents
-	    of the files /var/tmp/the_photo and
-	    /var/tmp/the_thumbnail in the image and
-	    thumbnail columns, respectively. 
+	    This inserts a new row into the 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_write_clob, - db_write_blob, - db_blob_get_file + db_write_clob, + db_write_blob, + db_blob_get_file

 db_write_clob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
 
 db_write_blob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
 
 db_blob_get_file statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
-

Analagous to ns_ora write_clob/write_blob/blob_get_file. +

Analagous to ns_ora write_clob/write_blob/blob_get_file. -

db_release_unused_handles

 	    db_release_unused_handles
-

Releases any allocated, unused database handles.

db_transaction

Releases any allocated, unused database handles.

db_transaction

 db_transaction code_block [ on_error { code_block } ]
-

Executes 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. It is possible to specify an optional on_error +

Executes 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. It is possible to specify an optional on_error code block that will be executed if some code in code_block throws - an exception. The variable errmsg will be bound in that scope. - If there is no on_error code, any errors will be propagated.

Example:

+	    an exception. The variable errmsg will be bound in that scope.
+	    If there is no on_error code, any errors will be propagated. 
Example:
 
 proc replace_the_foo { col } {
     db_transaction {
@@ -591,18 +591,18 @@
 
 print_the_foo ; # Writes out "foo is 8"
 
-

db_resultrows

 db_resultrows

Returns the number of rows affected or returned by the previous statement. -

db_with_handle

 db_with_handle var code_block
-

Places a database handle into the variable 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:

Places a database handle into the variable 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 {
@@ -622,15 +622,15 @@

- + db_nullify_empty_string -+

 db_nullify_empty_string string

For true SQL purists, we provide the convenience function - db_nullify_empty_string, which returns - [db_null] if its string argument is the empty string + db_nullify_empty_string, which returns + [db_null] if its string argument is the empty string and can be used to encapsulate another Oracle quirk:

 
 set baz ""
@@ -648,8 +648,8 @@
 # quirk)

- To balance out this asymmetry, you can explicitly set baz to - null by writing: + To balance out this asymmetry, you can explicitly set baz to + null by writing:

 
 db_dml foo_insert "insert into foo(baz) values(:1)" {[db_nullify_empty_string $baz]}