Forum OpenACS Development: Bind variable emulation in Tcl?

Posted by Andrew Piskorski on
Does anyone have any Tcl code hanging around what that does bind variable emulation in Tcl? I know you folks have long since added bind variable emulation to the Postgres driver now, but maybe before you got to that someone did it in Tcl?

Reason I ask, is I've just hacked together support for multiple databases in my ACS 4.2 DB API, principally so I can use it with the nsodbc driver (, which I'm pretty sure does not support bind variables. And right now my "replacement" for the 'ns_ora $type $db -bind $bind $sql' type calls in db_exec is 'error "Bind variables not supported for this database."'.

Posted by Don Baccus on
Ripping the emulation code out of the PG driver and installing it in nsodbc shouldn't be difficult.

The only requirement is that expressions like:

foo_id = '123'

work, i.e. that integers can be quoted and will be interpreted as numbers.  Oracle and PG both allow this (though the Oracle driver does
real bind vars, of course) and I think it's standard SQL 92.

Doing it at the Tcl level wouldn't be that hard - check out the regexps in the PG or Oracle driver that identify :tcl_var constructs.

But doing it right in nsodbc would be more efficient.

Posted by Dan Wickstrom on
00-database-procs-postgresql.tcl has a bind variable emulation routine that is used for the pg version of db_exec_plsql and for handling content stored as files.  The routine is called db_bind_var_substitution.  It's regexp based and it works ok as long as tcl vars are not mixed with bind variables.  Tcl vars can contain strings that will fool the regexp parsing and cause errors.  It should probably be rewritten to sequentially parse the query string instead of using regexps that way it would be more robust regardless of how the queries are constructed.  As Don suggested, lifting the bind variable emulation from the pg driver would be the best approach, and it would probably be quite simple to implement in the nsodbc driver.
Posted by Andrew Piskorski on
Don, you were right. Adding the bind variable emulation stuff into the nsodbc driver was pretty easy. It all seems to work for me now. But, in the process of doing this though, I came up with a few question about some of the (Open)ACS DB API stuff.

First of all, what is the deal with the args parameter in the Oracle version of db_exec? E.g.:

  Oracle:  return [eval [list ns_ora $type $db -bind $bind $sql] $args]
Postgres:  return [eval [list ns_pg_bind $type $db -bind $bind $sql]]
The principle users of this db_exec args parameter seem to be the db_exec_plsql, db_dml, and db_blob_get_file procs. Looks like the $args parameter is only important for lobs, but I'm at a loss as to what exactly is going on there.

Secondly, I grabbed the OpenACS code out of CVS, and noticed that the ACS Tcl package has three separate *database-procs*.tcl files:


I guess you guys went with separate -oracle and -postgresql files in order to avoid switch statements?

Well, now that I've added support for multiple database drivers to my 10-database-procs.tcl, I've got switch statements in there for Oracle, Postgres, and nsodbc anyway. So, seems to me that if at some point I want to try and add this same feature to OpenACS, I might as well merge the three files back into one, with switch statements galore, no? What do you think?

Posted by Don Baccus on
We're trying to avoid the switch statement approach.  The generic 00-database-procs file contains db api code common to all RDBMS's.  The *-oracle and *-postgres files contain db api procs specialized for
the particular RDMBS.  Only the generic and the proper specialized file are sourced by the bootstrap installer.  In other words, if you're running under PostgreSQL then 00-database-procs.tcl and 00-database-procs-postgresql.tcl will be sourced.

What you should do is make a new 00-database-procs-db2.tcl (or whatever db you're planning to support) and write specialized versions of the procs and stuff them in there.

You then need to add the RDMBS name to the known db names, and add some code to bootstrap/installer including checks to make sure the right db and version's mapped to the pools, and off you go.

We don't have an approach that lets us define an 00-database-procs-odbc.tcl and then map several DB's atop it.  My guess is that there will still be DB differences, i.e. in procedural languages, that will require specialization even though they may use the generic odbc driver to connect to the RDBMS.