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 @@ -15,7 +16,7 @@ an example of the API.

 set count 0
-set tcl_var "foo"
+set tcl_var "foo"
 set sql {
       	SELECT foo, bar, baz
        FROM some_table, some_other_table
@@ -128,8 +129,8 @@
       query, and Tcl style string interpolation does not happen. So you
       cannot do something like:
     
-set table "baz"
-set condition "where foo = bar"
+set table "baz"
+set condition "where foo = bar"
 
 db_foreach my_query { select :table from some_table where :condition }
     

@@ -158,7 +159,7 @@
       
 
 set user_id 123456
-set role "administrator"
+set role "administrator"
 
 db_foreach user_group_memberships_by_role {
     select g.group_id, g.group_name
@@ -168,7 +169,7 @@
     and map.role = :role
 } {
     # do something for each group of which user 123456 is in the role
-    # of "administrator"
+    # of "administrator"
 }
 
       

@@ -179,7 +180,7 @@
 
 set bind_vars [ns_set create]
 ns_set put $bind_vars user_id 123456
-ns_set put $bind_vars role "administrator"
+ns_set put $bind_vars role "administrator"
 
 db_foreach user_group_memberships_by_role {
     select g.group_id, g.group_name
@@ -189,7 +190,7 @@
     and map.role = :role
 } -bind $bind_vars {
     # do something for each group in which user 123456 has the role
-    # of "administrator"
+    # of "administrator"
 }

@@ -203,9 +204,9 @@ where g.group_id = map.user_id and map.user_id = :user_id and map.role = :role -} -bind [list user_id 123456 role "administrator"] { +} -bind [list user_id 123456 role "administrator"] { # do something for each group in which user 123456 has the role - # of "administrator" + # of "administrator" }

Nulls and Bind Variables

@@ -217,8 +218,8 @@

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 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 + "null" will be interpreted as the literal string + "null".

These Oracle quirks complicate the process of writing clear and abstract DML difficult. Here is an example that illustrates why:

 
 #
@@ -230,14 +231,14 @@
 #   );
 #
 
-set bar ""
-set baz ""
+set bar ""
+set baz ""
 
-db_dml foo_create "insert into foo(bar, baz) values(:bar, :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
+# 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
+# numeric column "bar") into null in both cases

Since databases other than Oracle do not coerce empty strings into @@ -250,9 +251,9 @@ 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)"
+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) does not incur a roundtrip to the server. For instance, this functionality is @@ -299,7 +300,7 @@ 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

@@ -417,10 +418,10 @@
 	       (if provided).
 	  
Example:
 
-db_foreach select_foo "select foo, bar from greeble" {
-    doc_body_append "<li>foo=$foo; bar=$bar\n"
+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"
+    doc_body_append "<li>There are no greebles in the database.\n"
 }
 
 	  

@@ -442,7 +443,7 @@
 	    query does not return exactly 1 row.
 	  
Example:
 
-db_1row select_foo "select foo, bar from greeble where greeble_id = $greeble_id"
+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.
 
@@ -525,11 +526,11 @@
 	    insert. Only one of -blobs, -clobs,
 	    -blob_files, and -clob_files may be provided.
Example:
 
-db_dml insert_photos "
+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"] 
+    "  -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"] 
 
 	  

 	    This inserts a new row into the photos table, with the contents
@@ -566,30 +567,30 @@
 
 proc replace_the_foo { col } {
     db_transaction {
-        db_dml "delete from foo"
-        db_dml "insert into foo(col) values($col)"
+        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"
+    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"
+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)"
+    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"
+    doc_body_append "Error in transaction: $errmsg"
 }
     
 
-print_the_foo ; # Writes out "foo is 8"
+print_the_foo ; # Writes out "foo is 8"

db_resultrows

 db_resultrows
@@ -606,13 +607,13 @@
 
 proc lookup_the_foo { foo } {
     db_with_handle db {
-        return [db_string unused "select ..."]
+        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"]
+    set selection [ns_db select $db "select foo from bar"]
     while { [ns_db getrow $db $selection] } {
         set_variables_after_query
 
@@ -633,17 +634,17 @@
 	    [db_null] if its string argument is the empty string
 	    and can be used to encapsulate another Oracle quirk: 
 
-set baz ""
+set baz ""
 
 # Clean out the foo table
 #
-db_dml unused "delete from foo"
+db_dml unused "delete from foo"
 
-db_dml unused "insert into foo(baz) values('$baz')"
+db_dml unused "insert into foo(baz) values('$baz')"
 
-set n_rows [db_string unused "select count(*) from foo where baz is null"]
+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
+# $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)
 
@@ -652,7 +653,7 @@
 	    null by writing: 
 	  
 
-db_dml foo_insert "insert into foo(baz) values(:1)" {[db_nullify_empty_string $baz]}
+db_dml foo_insert "insert into foo(baz) values(:1)" {[db_nullify_empty_string $baz]}