Forum OpenACS Q&A: querying multiple datababe server..

i wondering through the documentation and i still cant find howto query multiple DB servers..

im still trap in the old ns_db Thingy... is there a way to do queries using the new OACS stucture? or do i have to do the old fashion way the

ns_db gethandle "server_pool"
and
ns_db releasehandle

assuming i already added the correct lines for pools in the config file.

db_with_handle doesn't allow you to specify the pool you use.  the first argument it takes is the _handle_ name to provide to the codeblock, not the pool name.

you do have to use ns_db if you want to specify a specific pool.

I wonder, though, if it would be possible to add a db_with_pool <poolname> <codeblock using db_ calls> function...

here is a proc that will let you use the 4.x DB api with a non-default pool. it only lets you use the one pool, not a set of pools like you normally get, and you can't nest db_force_pool calls. (but you could with just a little more work on how it renames the old db_nth_pool_name.)

you could obviously integrate this with the db_ api in a less hackish manner, but this works as proof of concept. :)

proc db_force_pool {poolname code_block} {
    global db_state
    array set db_state_old [array get db_state]
    set db_state(handles) [list]
    set db_state(n_handles_used) 0

    rename db_nth_pool_name db_nth_pool_name_before_dbfp
    proc db_nth_pool_name {n} "
        return $poolname
    "

    if [catch {
        uplevel $code_block
    } err] {
        ns_log error "$err while executing $code_block in db_force_pool"
    }

    db_release_unused_handles
    rename db_nth_pool_name ""
    rename db_nth_pool_name_before_dbfp db_nth_pool_name
    array set db_state [array get db_state_old]
}

(remember that to keep ACS from grabbing all your pools, you have to put the subset you want it to use in ns/server/$server_name/acs/database.)

you're either misreading the docs, or conflating "handle" with "pool."

db_with_handle {db codeblock} executes $codeblock in a context w/ a handle from one of the ACS-mapped pools assigned to $db.  You have no (explicit) control over which pool this comes from.  The name you give $db has no relationship to the pools involved.

hi, i was used to using the ns_db API, and since i got the OACS it seems to me using ns_db API isnt appropriate to use anymore because of the db_* API.. (or i might be wrong)

i tried downloading your files and replaced the ones ported with OACS4.5 hmm..OACS4.5 modules/services AFAIK doesnt know how to use your code so that will be a problem.. hehehe

is there a way to use the db_* API to query a diff DB server (diff pool) because all queries default to the default pool listed in the config file..

TIA

BTW, IIRC OACS's db api is identical to the original ACS except for adding postgresql versions of some procs.  So Andrew's code, which looks nicely done, should come across pretty easily.