db_multirow (public)

 db_multirow [ -local ] [ -append ] [ -upvar_level upvar_level ] \
    [ -unclobber ] [ -extend extend ] [ -dbn dbn ] \
    [ -cache_key cache_key ] [ -cache_pool cache_pool ] \
    [ -subst subst ] var_name statement_name sql [ args... ]

Defined in packages/acs-tcl/tcl/01-database-procs.tcl

Switches:
-local
(boolean) (optional)
-append
(boolean) (optional)
-upvar_level
(defaults to "1") (optional)
-unclobber
(boolean) (optional)
If set, will cause the proc to not overwrite local variables. Actually, what happens is that the local variables will be overwritten, so you can access them within the code block. However, if you specify -unclobber, we will revert them to their original state after execution of this proc. Usage:
db_multirow [ -local ] [ -upvar_level n_levels_up ] [ -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 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 list of column names.

If "cache_key" is set, cache the array that results from the query *and* any code block for future use. When this result is returned from cache, THE CODE BLOCK IS NOT EXECUTED. Therefore, any values calculated by the code block that aren't listed as arguments to "extend" will not be created. In practice this impacts relatively few queries, but do take care.

You can not simultaneously append to and cache a nonempty multirow.

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 by db_multirow will be set locally (useful if you're compiling dynamic templates in a function or similar situations). Use the -upvar_level switch to specify how many levels up the variable should be set.

You may supply a code block, which will be executed for each row in the loop. This is very useful if you need to make computations that are better done in Tcl than in SQL, for example using ns_urlencode or ns_quotehtml, etc. When the Tcl code is executed, all the columns from the SQL query will be set as local variables in that code. Any changes made to these local variables will be copied back into the multirow.

You may also add additional, computed columns to the multirow, using the -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 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 to skip this row and quit looping.

Notice the nonstandard numbering (everything else in Tcl starts at 0); the reason is that the graphics designer, a non programmer, may wish to work with row numbers.

Example:

db_multirow -extend { user_url } users users_query {
        select user_id first_names, last_name, email from cc_users
    } {
        set user_url [acs_community_member_url -user_id $user_id]
    }
-extend
(optional)
-dbn
(optional)
The database name to use. If empty_string, uses the default database.
-cache_key
(optional)
Cache the result using given value as the key. Default is to not cache.
-cache_pool
(defaults to "db_cache_pool") (optional)
Override the default db_cache_pool
-subst
(defaults to "all") (optional)
Perform Tcl substitution in xql-files. Possible values: all, none, vars, commands
Parameters:
var_name
statement_name
sql
See Also:

Partial Call Graph (max 5 caller/called nodes):
%3 test_db__caching db__caching (test acs-tcl) db_multirow db_multirow test_db__caching->db_multirow test_db__transaction_bug_3440 db__transaction_bug_3440 (test acs-tcl) test_db__transaction_bug_3440->db_multirow ad_arg_parser ad_arg_parser (public) db_multirow->ad_arg_parser db_multirow_helper db_multirow_helper (private) db_multirow->db_multirow_helper db_qd_get_fullname db_qd_get_fullname (public) db_multirow->db_qd_get_fullname template::adp_level template::adp_level (public) db_multirow->template::adp_level acs_user::demote_user acs_user::demote_user (public) acs_user::demote_user->db_multirow bm_folder_selection bm_folder_selection (public) bm_folder_selection->db_multirow bug_tracker::bug::get_multirow bug_tracker::bug::get_multirow (public) bug_tracker::bug::get_multirow->db_multirow category::get_mapped_categories_multirow category::get_mapped_categories_multirow (public) category::get_mapped_categories_multirow->db_multirow doc::func_multirow doc::func_multirow (private, deprecated) doc::func_multirow->db_multirow

Testcases:
db__caching, db__transaction_bug_3440
Source code:
    # Query Dispatcher (OpenACS - ben)
    set full_statement_name [db_qd_get_fullname $statement_name]

    if { $local_p } {
        set level_up $upvar_level
    } else {
        set level_up \#[template::adp_level]
    }

    ad_arg_parser { bind args } $args

    # Do some syntax checking.
    set arglength [llength $args]
    if { $arglength == 0 } {
        # No code block.
        set code_block ""
    } elseif$arglength == 1 } {
        # Have only a code block.
        set code_block [lindex $args 0]
    } elseif$arglength == 3 } {
        # Should have code block + if_no_rows + code block.
        if { [lindex $args 1] ne "if_no_rows"
             && [lindex $args 1] ne "else"
         } {
            return -code error "Expected if_no_rows as second-to-last argument"
        }
        lassign $args code_block . if_no_rows_code_block
    } else {
        return -code error "Expected 1 or 3 arguments after switches"
    }

    upvar $level_up "$var_name:rowcount" counter
    upvar $level_up "$var_name:columns" columns

    if { [info exists cache_key]
         && $append_p
         && [info exists counter] && $counter > 0
     } {
        return -code error "Can't append and cache a nonempty multirow datasource simultaneously"
    }

    if { [info exists cache_key] } {
        #
        # Call helper with cache key
        #
        set value [ns_cache eval $cache_pool $cache_key {
            db_multirow_helper

            set values [list]

            for { set count 1 } { $count <= $counter } { incr count } {
                upvar $level_up "$var_name:[expr {$count}]" array_val
                lappend values [array get array_val]
            }

            return [list $counter $columns $values]
        }]

        lassign $value counter columns values

        set count 1
        foreach value $values {
            upvar $level_up "$var_name:[expr {$count}]" array_val
            array set array_val $value
            incr count
        }
    } else {
        #
        # Call helper without cache key
        #
        db_multirow_helper
    }

    # If the if_no_rows_code is defined, go ahead and run it.
    if { $counter == 0 && [info exists if_no_rows_code_block] } {
        uplevel 1 $if_no_rows_code_block
    }
XQL Not present:
Generic, PostgreSQL, Oracle
[ hide source ] | [ make this the default ]
Show another procedure: