Forum OpenACS Development: Add -arr_p switch to db_list_of_lists

I propose adding an "-arr_p" switch to the db_list_of_lists proc. It would default to "-arr_p 0", which is the current behavior. With "-arr_p 1", it instead returns a list of lists, where each interior list is in "array get" form, with the column name as each key.

The implementation change (see below) is trivial, just a two lines or so, but this seems quite useful to me. Comments?

Posted by Andrew Piskorski on
Here's the whole function including the small change, based on that shipped with OpenaCS 5.1.4:

ad_proc -public db_list_of_lists {{
   -dbn ""
   -arr_p 0
} statement_name sql args } {

    Usage: <b>db_list_of_lists</b> <i>statement-name sql</i> [ <tt>-bind</tt> <i>bind_set_id</i> | <tt>-bind</tt> <i>bind_value_list</i> ]

    @return a Tcl list, each element of which is a list of all column
    values in a row of the result of the SQL query<tt>sql</tt>. If
    <tt>sql</tt> doesn't return any rows, returns an empty list.
    Analogous to <tt>database_to_tcl_list_list</tt>.

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

    @param arr_p If true, returns each interior list (on the of lists)
    in the Tcl "array get" key/value form.  If false, returns a simple
    Tcl list without the keys.
} {
    ad_arg_parser { bind } $args

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

    # 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 } {
                if { $arr_p } {
                    lappend this_result [ns_set key $selection $i] [ns_set value $selection $i]
                } else {
                    lappend this_result [ns_set value $selection $i]
            lappend result $this_result
    return $result
Posted by Don Baccus on
Why not add a db_list_of_arrays API command? This is more in the spirit of the db_* API. The fact that arrays happen to be shuffled around in a particular list format is just a Tcl implementation artifact ...