Forum OpenACS Development: Re: db_transaction hanging a thread how to detect, how to kill...

Any user of db_list_of_lists must assume that the column values are provided in a particular order, but the proper particular order to assume is defined elsewhere, in the query. So if you ever change the query, you have to change any code using the list generated by db_list_of_lists as well. Effectively, you have the specific column order defined in (at least) two independent places, which can lead to bugs.

If the result of query is used only in one place, then you're probably using it right after the db_list_of_lists call that generates it, and so this source of bugs, while still present, is minor. You are unlikely to forget to change both places when the two places are only three lines apart in the code.

If the query needs to be called from several different places, what is probably really desired is something like a keyed list, as found in R and (I think) TclX as well. Probably you could play with Tcl Arrays or ns_sets in some useful way here.

But instead, I've sometimes handled the problem like so:

Embed the query into a Tcl proc. Since you need to call it from several different places, you probably did this allready anyway. Add an optional "-cols_var" parameter to your proc:

ad_proc atp_foo_query {{ 
   -cols_var {} 
} foo_id } { 
      Returns info about the foo. 
} { 
   if { ![empty_string_p $cols_var] } { 
      upvar 1 $cols_var cols 
   } 
   # Order of the columns here and in the query MUST match: 
   set cols [list a b c d e f g] 
   return [db_list_of_lists big_nasty_query { 
   -- Big nasty query goes here.
   }] 
} 

Now, the caller of that proc does this:

set cols [list]
set data_lol [atp_foo_query -cols_var {cols} $foo_id] 
foreach row $data_lol { 
   # This sets local variables for all columns:
   foreach $cols $row { break } 
   # Do more processing here...
}

Note that the caller still has to know the name of every column he's interested in, but he does not need to care exactly what order the columns appear in. So, you can add a new column to the query and everything should (will probably) be fine. (Naturally, if the new column is named such that it smashes over a local variable, you will lose; but given halfway decent choice of variable names this is unlikely.)