Index: openacs-4/packages/acs-core-docs/www/db-api.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/db-api.adp,v diff -u -r1.5.2.8 -r1.5.2.9 --- openacs-4/packages/acs-core-docs/www/db-api.adp 1 Aug 2024 08:03:39 -0000 1.5.2.8 +++ openacs-4/packages/acs-core-docs/www/db-api.adp 2 Sep 2024 09:40:21 -0000 1.5.2.9 @@ -2,10 +2,14 @@ {/doc/acs-core-docs/ {ACS Core Documentation}} {The OpenACS Database Access API} The OpenACS Database Access API +

@@ -39,8 +43,7 @@ foreach row $rows { call_some_proc $foo $bar $baz } -} -

There are several things to note here:

    +}

    There are several things to note here:

    1. No explicit code for grabbing and releasing handles. Usage of the Database API implicitly deals with all handle management issues.

    2. The db_transaction command @@ -64,8 +67,7 @@ select foo, bar, baz from some_table, some_other_table where some_table.id=some_other_table.id -and some_table.condition_p = '$foo' -

      There are a few problems with this:

        +and some_table.condition_p = '$foo'

        There are a few problems with this:

        1. If the value of $foo is a huge string, then we waste a lot of time in the database server doing useless parsing.

        2. Second, if the literal value contains characters like single quotes, we have to be careful to properly escape them, because not @@ -115,8 +117,7 @@ set condition "where foo = bar" db_foreach my_query { select :table from some_table where :condition } - -

          SQL will not allow a literal to occur where we've put the +

          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 not @@ -149,8 +150,7 @@ # of "administrator" } - -

          The value of the local Tcl variable user_id (123456) is bound to the +

          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, @@ -171,8 +171,7 @@ # of "administrator" } - -

          Alternatively, as an argument to -bind you can specify a list of alternating +

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

                   
 db_foreach user_group_memberships_by_role {
@@ -186,8 +185,7 @@
     # of "administrator"
 }
 
-      
-
          +

        Nulls and Bind Variables

        When processing a DML statement, Oracle coerces empty strings @@ -222,8 +220,7 @@ # null, because Oracle has coerced the empty string (even for the # numeric column "bar") into null in both cases - - +

      @@ -245,8 +242,7 @@ [ns/server/yourservername/acs/security] PoolSequence.sec_id_seq=20 - -

      The database library will allocate this number of sequence +

      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 @@ -260,9 +256,7 @@ 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 +

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

      @@ -312,8 +306,7 @@ } { set user_url [acs_community_member_url -user_id $user_id] } - -

      You can also iterate over a multirow after it has been created - +

      You can also iterate over a multirow after it has been created - check the documentation for template::multirow

      For example,

       db_multirow assets assets {
   select asset_id,
@@ -326,8 +319,7 @@
 multirow foreach assets {
   lappend asset_id_l $asset_id
 }
-          
-

      Technically it's equivalent to using a code block on the end +

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

      db_foreach
      @@ -347,33 +339,29 @@ doc_body_append "<li>There are no greebles in the database.\n" } - -

      The code block may contain break statements (which terminate the loop +

      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-namesql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]
-          
-

      Performs the SQL query sql, setting variables to +

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

      Example:

       
 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.
 
-          
-
      +
      db_0or1row
       db_0or1row statement-namesql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]
-          
-

      Performs the SQL query sql. If a row is returned, sets +

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

      @@ -396,8 +384,7 @@ db_string
       db_string statement-namesql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]
-          
-

      Returns the first column of the result of SQL query sql. +

      Returns the first column of the result of SQL query sql. If sql doesn't return a row, returns default (or throws an error if default is unspecified). @@ -406,17 +393,15 @@ db_list

       db_list statement-namesql [ -bind bind_set_id | -bind bind_value_list ]
-          
-

      Returns a Tcl list of the values in the first column of the +

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

      db_list_of_lists
       db_list_of_lists statement-namesql [ -bind bind_set_id | -bind bind_value_list ]
-          
-

      Returns a Tcl list, each element of which is a list of all +

      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.)

      @@ -427,8 +412,7 @@ [ -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 +

      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, should be @@ -443,8 +427,7 @@ returning image, thumbnail_image into :1, :2 } -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 +

      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.

      @@ -462,21 +445,18 @@ db_write_blob statement-namesql [ -bind bind_set_id | -bind bind_value_list ] db_blob_get_file statement-namesql [ -bind bind_set_id | -bind bind_value_list ] - -

      Analogous to ns_ora +

      Analogous to ns_ora write_clob/write_blob/blob_get_file.

      db_release_unused_handles
                   db_release_unused_handles
-          
-

      Releases any allocated, unused database handles.

      +

      Releases any allocated, unused database handles.

      db_transaction
       db_transaction code_block [ on_error { code_block } ]
-          
-

      Executes code_block transactionally. +

      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 @@ -514,14 +494,12 @@ print_the_foo ; # Writes out "foo is 8" - - +

      db_resultrows
       db_resultrows
-          
-

      Returns the number of rows affected or returned by the previous +

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

      db_with_handle
      @@ -548,8 +526,7 @@ } } - - +
      ($‌Id: db-api.xml,v 1.17.2.6 2024/02/08 18:45:01 gustafn Exp $)
      @@ -586,8 +563,7 @@ db_flush_cache -cache_key_pattern page_${page_id}_* - - +