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&#39;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&#39;re either misreading the docs, or conflating &#34;handle&#34; with &#34;pool.&#34;

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&#39;s db api is identical to the original ACS except for adding postgresql versions of some procs.  So Andrew&#39;s code, which looks nicely done, should come across pretty easily.