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

Request notifications

Posted by Jerome M on
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"
ns_db releasehandle

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

Posted by Cheng-Yi Hsu on
I use this to query no-default pool,
db_with_handle yourpool {
    db_foreach yoursql "...." {
    db_string yoursql ...
    db_1row ...
    other new ACS4.x db api

, and in your servicename.tcl ns/server/yourservicename/db section,
ns_param pool yourpool,main,subquery,log

make sure yourpool in first order, otherwise some strang error will happen !
Posted by Jonathan Ellis on
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...

Posted by David Walker on
multiple datababes, pools, are you sure this isn't a fantasy of some sort?
Posted by Jonathan Ellis on
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"

    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.)

Posted by Cheng-Yi Hsu on
I use db_with_handle to connect to non-default pool in ACS 3.4* project, it really works for us, but I never try this on OACS!
You can see this function on http://yourhome/api-doc/

db_with_handle db code_block
Defined in packages/acs-core/10-database-procs.tcl
Places a usable database handle in $db and executes $code_block.


Posted by Jonathan Ellis on
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.

Posted by Andrew Piskorski on
Jerome, if it's just that you can't figure out how to use the ns_db API, you need to read and practice more. Perhaps look at the old OpenACS 3.x code for lots of ns_db examples.

However, it's not that hard to to a pretty cleanly modify the OpenACS db_* ABI to let you use it on multiple database, and database you have an AOLserver database driver loaded and configured for. I did it for ACS 4.2 - see this thread.

I will volunteer to do a further cleaned up and better tested version of that for OpenACS when I have time, probably for 4.7, or perhaps a later maintenance release of 4.6.x (as the changes can be made entirely backwards compatible).

But if you really need something like that now, take a look at my old 4.2 code, and let me know if you have questions.

Posted by Jerome M on
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..


Posted by Jonathan Ellis on
Uh, Andrew and I each just gave you a way...
Posted by Jonathan Ellis on
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.