db_foreach (public)

 db_foreach [ -dbn dbn ] [ -subst subst ] statement_name sql \
    [ args... ]

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

Usage:

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, executing 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 specified). If the query returns no rows, executes if_no_rows_block (if provided). In place of 'if_no_rows' also the 'else' keyword can be used.

Example:

db_foreach greeble_query "select foo, bar from greeble" {
        ns_write "<li>foo=$foo; bar=$bar\n"
    } if_no_rows {
        # This block is optional.
        ns_write "<li>No greebles!\n"
    }

Switches:

-dbn (optional)

The database name to use. If empty_string, uses the default database.

-subst (optional, defaults to "all")

Parameters:

statement_name (required)

sql (required)

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

Testcases:

db__db_foreach, db__transaction_bug_3440

Source code:

    ad_arg_parser { bind column_array column_set args } $args

    # Do some syntax checking.
    set arglength [llength $args]
    if { $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] ni {"if_no_rows" "else"}} {
            return -code error "Expected if_no_rows or else 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"
    }

    if { [info exists column_array] && [info exists column_set] } {
        return -code error "Can't specify both column_array and column_set"
    }

    if { [info exists column_array] } {
        upvar 1 $column_array array_val
    }

    if { [info exists column_set] } {
        upvar 1 $column_set selection
    }

    set bindArg [expr {[info exists bind] ? [list -bind $bind] : ""}]
    set counter 0
    set result [uplevel [list db_list_of_lists  -with_headers  -dbn $dbn  -subst $subst  $statement_name  $sql  {*}${bindArg}]]
    #ns_log notice "RESULT $result"
    set columns [lindex $result 0]
    #ns_log notice "columns <$columns>"
    foreach tuple [lrange $result 1 end] {
        incr counter

        #
        # Result will be provided in different formats to the code
        # block depending on the flags...
        #
        if { [info exists column_set] } {
            #
            # ns_set
            #
            if { [info exists selection] } {
               ns_set free $selection
            }
            set selection [ns_set create]
            foreach a $columns v $tuple { ns_set put $selection $a $v }
        } elseif { [info exists column_array] } {
            #
            # array
            #
            unset -nocomplain array_val
            array set array_val [concat {*}[lmap a $columns v $tuple {list $a $v}]]
        } else {
            #
            # plain variables
            #
            foreach a $columns v $tuple { uplevel [list set $a $v] }
        }

        set errno [catch { uplevel 1 $code_block } error]

        #
        # Handle or propagate the error.
        #
        switch -- $errno {
            0 {
                # TCL_OK
            }
            1 {
                # TCL_ERROR
                error $error $::errorInfo $::errorCode
            }
            2 {
                # TCL_RETURN
                error "Cannot return from inside a db_foreach loop"
            }
            3 {
                # TCL_BREAK
                break
            }
            4 {
                # TCL_CONTINUE - just ignore and continue looping.
            }
            default {
                error "Unknown return code: $errno"
            }
        }
    }
    # 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