Forum OpenACS Development: Response to Database API & Query Replacement - Bind vars

Posted by Dan Wickstrom on
I've been looking at the bind vars and it seems that we should be able to fake it either in a tcl proc or in the pg driver. It appears that all of the new db api calls go through db_exec which is shown below:
ad_proc -private db_exec { type db statement_name sql args } {

    A helper procedure to execute a SQL statement, potentially binding
    depending on the value of the $bind variable in the calling environment
    (if set).

} {
    set start_time [clock clicks]

    set errno [catch {
	upvar bind bind
	if { [info exists bind] && [llength $bind] != 0 } {
	    if { [llength $bind] == 1 } {
                # case 1 -> already an ns_set - pass it to the oracle driver with the -bind flag
		return [eval [list ns_ora $type $db -bind $bind $sql] $args]
	    } else {
                # case 2 -> we have a list - so convert to ns_set and pass to oracle driver
		set bind_vars [ns_set create]
		foreach { name value } $bind {
		    ns_set put $bind_vars $name $value
		return [eval [list ns_ora $type $db -bind $bind_vars $sql] $args]
	} else {
            # case 3 -> go up to the calling scope and let the driver access the variables directly
	    return [uplevel 2 [list ns_ora $type $db $sql] $args]
    } error]

    ad_call_proc_if_exists ds_collect_db_call $db $type $statement_name $sql $start_time $errno $error
    if { $errno == 2 } {
	return $error

    global errorInfo errorCode
    return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error

As far as I can tell there are three methods for using bind variables:

  1. Pass an ns_set with the -bind flag.
  2. Pass an array with key/value pairs and the -bind flag.
  3. Use tcl variables in the calling scope that match the bind variable names.
To get the porting effort going we should be able to modify the db_exec proc to handle any of these three cases and substitute in any bind variables that are required. It also seems that we should be able to modify the pg driver at a later date to handle the bind variable substitution.

I haven't looked at clob/blob with bind varibles, but it probably can be handled in a similar manner.

Has anybody seen any situations where we might run into trouble doing this?