Forum OpenACS Q&A: One Script Access to Multiple PG Databases

We have multiple OpenACS 3.x sites on one machine, each with it's own postgresql database. Is it possible to do a SELECT.... in a TCL script to get data from a different database? Here is the question more in outline form:

www.site1.com  normally connects to site1db
www.site2.com   normally connects to site2db

A script:
www.site1.com/getdata.tcl  

needs to do a SELECT... on data in site2db 
for verification against data that
it already got in site1db.

The end result may require INSERT or UPDATE
into site1db or site2db.

Solutions and Suggestions Please and Thank you!

-Bob

Collapse
Posted by Jeff Davis on
you can add a pool that connects to the other db and
then do queries against it (although you can't use the
nice openacs db interface and will have to go back to the old AOLServer api "ns_db gethandle poolname" etc).
Not great but if it's limited in scope it would probably
not be too bad.
Collapse
Posted by MaineBob OConnor on

Ok, Jeff suggested "...will have to go back to the old AOLServer api "ns_db gethandle poolname" etc)...."

Please help me with the "etc..." So far I have:

#Default database
set db [ns_db gethandle]
set selection [ns_db 0or1row $db "select user_id, first_names,
last_name from users where user_id=3"] 
set_variables_after_query
ns_db releasehandle $db

#SECOND Database
set db2  [ns_db gethandle -timeout 23 localhost:bobdata2]
set selection [ns_db 0or1row $db2 "select user_id user_id2,
first_names first_names2, last_name last_name2 from 
users where user_id=4"] 
set_variables_after_query
ns_db2 releasehandle $db

append page_content "<html>...

So, this gives me "Error: no access to pool: localhost:bobdata2

***What other things do I need to set up in THIS script to get access to the second database?

Because this is a limited an occasionally used tcl script in www, I'd like to do it here rather than in the AOLServer Startup Script.

It looks like the following are not yet set for the second db:
datasource,dbtype,driver,poolname,user,password

If these are all that are needed, please help me with the syntax in my script.

TIA

-Bob

Collapse
Posted by Denis Roy on
I have once set up something like this for an ACS 3.x site that required access to a second Oracle database. I have no clue whether it will help you with Postgres:

Set up another database pool in your AOLserver config file:

ns_section ns/db/pool/bobdata2
ns_param  maxidle            1000000000
ns_param  maxopen            1000000000
ns_param  connections        5
ns_param  verbose            $debug
ns_param  extendedtableinfo  true
ns_param  logsqlerrors      $debug
if { $database == "oracle" } {
    ns_param  driver            ora8
    ns_param  datasource        {}
    ns_param  user              $db_name
    ns_param  password          $db_password
} else {
    ns_param  driver            postgres
    ns_param  datasource        localhost::bobdata2
    ns_param  user              nsadmin
    ns_param  password          ""
}

The trick here is that I needed to exclude this pool from automatic access by the database API. The corresponding setting was in parameters/yourserver.ini so I have no clue where you have to set this in OpenACS 4.x and if you have to set this at all.

[ns/server/yourserver/acs/database]
AvailablePool=pool1
AvailablePool=pool2
AvailablePool=pool3

In the end, you should be able to access the second database with

set db2 [ns_db gethandle bobdata2]

Collapse
Posted by Denis Roy on
Btw, you don't have to use the ns_db API if you query the primary database. Just use the OpenACS API as usual. ns_db calls are only necessary if you want to access a database pool that cannot be handled by the OpenACS API which is the case for other than the primary database (and this is also the reason why one has to exclude the second database's pool from API access).

Again, all this is based on ACS 3.x and Oracle. I hope others would be able to correct me if that's not the case anymore for OpenACS 4.x.