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 (defaults to "all") (optional)
Parameters:
statement_name
sql

Partial Call Graph (max 5 caller/called nodes):
%3 test_db__db_foreach db__db_foreach (test acs-tcl) db_foreach db_foreach test_db__db_foreach->db_foreach test_db__transaction_bug_3440 db__transaction_bug_3440 (test acs-tcl) test_db__transaction_bug_3440->db_foreach aa_test::get_test_doc aa_test::get_test_doc (private) aa_test::get_test_doc->db_foreach acs_mail_lite::send_immediately acs_mail_lite::send_immediately (private) acs_mail_lite::send_immediately->db_foreach acs_mail_lite::sweeper acs_mail_lite::sweeper (private) acs_mail_lite::sweeper->db_foreach acs_messaging_process_queue acs_messaging_process_queue (private) acs_messaging_process_queue->db_foreach acs_object_type_hierarchy acs_object_type_hierarchy (public) acs_object_type_hierarchy->db_foreach

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
    foreach selection [uplevel [list db_list_of_ns_sets -dbn $dbn -subst $subst $statement_name $sql {*}${bindArg}]] {
        incr counter
        if { ![info exists column_set] } {
            set set_array [ns_set array $selection]
            if { [info exists column_array] } {
                unset -nocomplain array_val
                array set array_val $set_array
            } else {
                foreach {a v} $set_array { 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
[ hide source ] | [ make this the default ]
Show another procedure: