Forum OpenACS Q&A: Bind variable from array

Collapse
Posted by Christian Brechbuehler on
Is there a way to use an array element as a bind variable? (I will take "no" for an answer.) Here's a simple-minded attempt:
array set f {ee 3 ie 10 oe 2}
db_string no_luck {select :f(ie) from dual}
It fails with
ERROR:
undefined variable `f'
    while executing
"ns_ora 0or1row nsdb0 {select :f(ie) from dual}"
Obviously I could set foo $f(ie) in TCL and then use bind variable :foo, but that approach can get repetitive, and it pollutes the top level namespace.
Collapse
Posted by Gustaf Neumann on
The short answer is that you can't use "array references" as bind vars.

The slightly longer answer is: use the bind flag. Here is the variant with arrays:

array set f {ee 3 ie 10 oe 2}
db_string no_luck {select :ie from dual} -bind [array get f]
In many cases, Tcl dicts are the better choice than Tcl arrays: use e.g.
set d [dict create ee 3 ie 10 oe 2]
db_string no_luck {select :ie from dual} -bind $d
Last but not least, one can use also ns_sets:
set s [ns_set create vars ee 3 ie 10 oe 2]
db_string no_luck {select :ie from dual} -bind $s
All this works at least in the PostgreSQL driver, but i would be surprised, when ns_ora would fail.

Hope this helps
-g

Collapse
Posted by Christian Brechbuehler on
Thanks for a comprehensive answer, Gustaf!

I wasn't aware of TCL dicts, will have al look.

I have used ns_set to pass bind variables, and it works with Oracle. After all, that seems to be what the "normal" invocation uses; it just creates an ns_set with the necessary variables for you on the fly. The drawback is that this use of the "-bind" flag masks ordinary TCL variables in the caller's scope, i.e., I can no longer write :varname in the query. As a work-around, I make a copy of the ns_set, splice all needed variables of interest into the copy, and pass that to the db_* call. I need a copy so I don't mess up the original ns_set. All in all, not very elegant.

Thanks again,
/Christian

Collapse
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
or
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
-gustaf

Collapse
Posted by Brian Fenton on
I can confirm that Gustaf's tips work in Oracle too.

Brian