database_api

NAME

database_api — The nstcl database api

SYNOPSIS

package require nstcl-database ?1.0?
package require nstcl ?1.0?

DESCRIPTION

The nstcl database api is a re-implementation of the ACS & OpenACS database APIs that make it possible to work with the database at a higher level than the AOLserver ns_db commands.

Bind variable emulation is available for all databases. That is, instead of writing select * from foo where bar = $id you can write select * from foo where bar = :id. The emulation takes care of escaping double apostrophes and such. For Solid RDBMS, which does not allow numeric types to be enclosed in single quotes (except on inserts), an alternate syntax of :#id may be used which does not wrap the number in single quotes.

Each of the database api commands takes a statement name. In the original ACS 4 these statement names served merely as documentation. The OpenACS project took advantage of the statement_name's to create a query dispatcher that could find and select the proper version of a query, depending on the database being used.

nstcl does not contain a query dispatcher at this time. It does have, however, one benefit to the current OpenACS setup: it can work with multiple databases at the same time.

If you only create one database pool, that pool will always be used. You do not need to explicitly set a default. If you configure multiple pools, you can use ::nstcl::set_default_pool poolname to set a default, or use the -default switch on one of the calls to nstcl::configure_pool.

To specify an alternate pool, other than the default, preface the statement_name with the name of the pool, followed by a colon.

EXAMPLE:

    package require nstcl

    nstcl::load_driver oracle
    nstcl::load_driver postgres
    nstcl::load_driver solid
    nstcl::configure_pool oracle oracle 1 "" username password
    nstcl::configure_pool postgres postgres 1 "localhost:5432:dbname" 
    nstcl::configure_pool solid solid 1 "tcp localhost 1313" username password
    nstcl::set_default_pool solid

    # uses default pool
    puts [db_string current_time_from_solid { select now() }]

    # explicitly identifies which alternate pool to use
    db_foreach oracle:old_customers {
        select first_names, last_name, email
          from customers
    } {
        db_dml postgres:new_customers {
            insert into customers values (:first_names, :last_name, :email)
        }
    }

SEE ALSO

db_0or1row, db_1row, db_transaction, db_abort_transaction, db_continue_transaction, db_dml, db_foreach, db_list, db_list_of_lists, db_multirow, db_string

KEYWORDS

database, nstcl