Forum OpenACS Q&A: partially complete backport of v4 DB API

I really liked the v4 DB API and I don't want to wait for OpenACS 4 to start using it. :) So I tried to backport as much as possible to OpenACS 3.2.x.

Incomplete stuff:

• no bind variable support. This is the biggie; bind variables are one of the main reasons to use the v4 DB API. I'm not a hardcore pg hacker like Don so I didn't even try to port ns_ora. But half a loaf is better than none, right?
• no db_resultrows (ns_ora again)
• no db_source_sql_file or db_source_sqlj_file (I didn't backport the new ad_proc, and these use its -callback feature)

In case anyone else is interested, I've put it up here (at 30k, it's too big to post). Just drop it in your tcl directory and restart aolserver.

The latest driver on sourceforge includes  pseudo-bind variable
support.  Even more importantly (since you hardly really need to use
explicit bind list), the cool ns_ora feature to automatically look up
Tcl values for you works.

A query like this:

ns_pg_bind select $db "select foo from bar where foo_key = :tcl_value"

will lookup tcl_value in your Tcl environment, then do the equivalent of:

ns_db select $db "select foo from bar where foo_key = '$tcl_value'"

The value's quoted so you can't do SQL smuggling ('35' and 35 used for
values in PG result in *exactly* the same query plan and '35 union
select <smuggled sql>' will give a string-to-int conversion error).

Even better, ns_pg_bind does the equivalent of DoubleApos so you don't
need to do that yourself anymore (Oracle bind vars are real values so
you don't need to DoubleApos them so ns_pg_bind does it for you to
keep the db api the same for both).

All this is in the current driver and has been somewhat tested by Dan
and I (Dan did most of the work).

I expect to commit the first version of the PG 4x db api tomorrow or
wednesday AM...

Excellent! Now, I can just about develop code the same way on ACS 3.4 & OpenACS 3.2.

I found one (minor) unported function in the 10-database-procs.tcl file:

db_nextval should be:

proc_doc db_nextval { sequence } { Returns the next value for a sequence. } {
    return [db_string "nextval" "select nextval('$sequence') from dual"]
}

Thanks again!

You don't need the "from dual", either ... the "from" clause is optional in PG.  We only supply dual so we can use unmodified queries more frequently, but since db_nextval is already different in Oracle and PG might as well leave it off entirely.  This will speed up the nextval query just a bit ...