Forum OpenACS Development: Response to Database API & Query Replacement

Posted by Sebastian Skracic on

Here's how I planned to implement bind variables for InterBase driver:

InterBase does not support bind variables alla Oracle. Instead it allows SQL statements with positional parameters, like

SELECT col1, col2 FROM table1 WHERE col3 = ? AND col4 > ?

Positional parameters are denoted with question marks and they have to be described using appropriate InterBase API calls after the statement is prepared, but prior its actual execution. Once prepared, statement can be reused (re-executed) using another set of positional parameter values.

My idea is to write thin Tcl layer (ns_ib) which will transform ACS-friendly constructs like:

SELECT col1, col2 FROM table1 WHERE col3 = :tclvar1 AND col4 > :tclvar2
into IB-friendly form shown above, grabbing $tclvar1 and $tclvar2 and passing them to driver as textual representation of positional parameter values. Fortunately, when filling positional parameters, InterBase can convert textual representation into any IB native datatype, although this conversion should not be too hard to write ourselves *if* we know which datatype we should convert into.

I suspect that PostGres provides similar functionality.