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

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

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. The default behavior (i.e., when no "-local" is specified) depends on the calling environment: when "db_multirow" is called from an ADP file the variables are set in the ADP environment. Otherwise, the default behavior is "-local".

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

Switches:

-local (optional, boolean)

-append (optional, boolean)

-upvar_level (optional, defaults to "1")

-unclobber (optional, boolean)

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.

-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 (optional, defaults to "db_cache_pool")

Override the default db_cache_pool

-subst (optional, defaults to "all")

Perform Tcl substitution in xql-files. Possible values: all, none, vars, commands

Parameters:

var_name (required)

name of the Tcl multirow array

statement_name (required)

name of the SQL query

sql (required)

SQL query to be executed

See Also:

• template::multirow

Partial Call Graph (max 5 caller/called nodes):

Testcases:

db__caching, db__transaction_bug_3440

Source code:

    # Query Dispatcher (OpenACS - ben)
    set full_statement_name [db_qd_get_fullname $statement_name]

    #
    # When this function is called outside ADP, fall back to "-local"
    # behavior.
    #
    set adpLevel [template::adp_level]

    if { $local_p || $adpLevel eq ""} {
        set level_up $upvar_level
    } else {
        set level_up \#$adpLevel
    }

    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