Forum OpenACS Q&A: Re: Bind variable from array

Posted by Gustaf Neumann on
Hi Christian,

The question is, where one has the interesting values prior to the db_* call. If one has part of the bind variables in plain Tcl variables (e.g. "v1" and "v2") and part in a Tcl array (e.g. "f"), these can be combined to a flat var/value list using the expand operator of Tcl 8.5.

set v1 1
set v2 2
array set f {ee 3 ie 10 oe 2}
set s [ns_set create vars v1 $v1 v2 $v2 {*}[array get f] ]
db_string ... -bind $s
db_string ... -bind [ns_set create vars v1 $v1 v2 $v2 {*}[array get f] ]
Certainly, the variable collection magic can be put into a helper function such as
proc bind_vars args {
    set s [ns_set create vars]
    foreach var $args {
	if {[uplevel array exists $var]} {
	    foreach {k v} [uplevel array get $var] {ns_set cput $s $k $v}
	} else {
	    ns_set cput $s $var [uplevel set $var]
    return $s
which can be used in turn in a db* command such as
db_string ... -bind [bind_vars f v1 v2]

in this implementation, the earlier variables have higher precedence than the later ones, since "cput" was used.

In general, the "-bind" option has the advantage to make the interface between tcl and the sql query explicit, which is especially useful when xql is used. Otherwise one has to look always into the .xql file to determine, which variables are used, when the Tcl code is changed.

all the best