Forum OpenACS Development: Re: Schedule proc

I just ran into this too. I am surprised db_multirow doesn't work in a schedule proc, but the logic is clear. The ADP level is not prepared so it just doesn't work.

In generaly I don't advise using db_foreach, especially if you need to call any procedure that does a database query within the db_foreach code block, there doesn't seem to be a better solution.

Michael Hinds wrote a version of db_foreach that supports nesting.

ad_proc -public db_foreach_no_handle {{ -dbn "" } statement_name sql args } {

  Like db_foreach, but nest as much as you like
  MJH

} {

    # MJH - ripped out of db_list_of_lists
    ad_arg_parser { bind column_array column_set args } $args

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

    # 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 { ![string equal [lindex $args 1] "if_no_rows"] && ![string equal [lindex $args 1] "else"] } {
            return -code error "Expected if_no_rows as second-to-last argument"
        }
        set code_block [lindex $args 0]
        set if_no_rows_code_block [lindex $args 2]
    } else {
        return -code error "Expected 1 or 3 arguments after switches"
    }

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

    # Can't use db_foreach here, since we need to use the ns_set directly.
    db_with_handle -dbn $dbn db {
        set selection [db_exec select $db $full_statement_name $sql]

        set result [list]

        while { [db_getrow $db $selection] } {
            set this_result [list]
            for { set i 0 } { $i < [ns_set size $selection] } { incr i } {
                lappend this_result [ns_set value $selection $i]
            }
            lappend result $this_result
        }
    }

    set column_names [ad_ns_set_keys $selection]
    set num_columns [llength $column_names]

    for {set i 0} {$i < $num_columns} {incr i} {
        upvar 1 [lindex $column_names $i] column_value$i
    }

    foreach row $result {
        if { [info exists array_val] } {
            unset array_val
        }
        for {set i 0} {$i < $num_columns} {incr i} {
            if { [info exists column_array] } {
                set array_val([lindex $column_names $i]) [lindex $row $i]
            } else {
                set column_value$i [lindex $row $i]
            }
        }
        set errno [catch { uplevel 1 $code_block } error]
        switch $errno {
            0 {
                # TCL_OK
            }
            1 {
                # TCL_ERROR
                global errorInfo errorCode
                error $error $errorInfo $errorCode
            }
            2 {
                # TCL_RETURN
                error "Cannot return from inside a db_foreach_no_handle loop (yet)"
            }
            3 {
                # TCL_BREAK
                #cjke: no db anymore# ns_db flush $db
                break
            }
            4 {
                # TCL_CONTINUE - just ignore and continue looping.
            }
            default {
                error "Unknown return code: $errno"
            }
        }
    }

} ;# end db_foreach_no_handle