Forum OpenACS Q&A: connection to secondary database

Posted by Andrew Cadman on
Hi, I'd like to connect our legacy newsletter postgresql database to our open acs application, but I cant really see how to do this anywhere - I cant even see where the database connection strings are located in the tcl procedures.....can anyone here help me out here please - much appreciated.

Andrew Cadman

Posted by Claudio Pasolini on
Simply add one or more pools to your config.tcl file as in the following example.
ns_section ns/db/pools 
ns_param   pool1	   "Pool 1"
ns_param   pool2	   "Pool 2"
ns_param   pool3	   "Pool 3"
ns_param   pool4	   "Pool 4"


ns_section ns/db/pool/pool4
ns_param   maxidle            1000000000
ns_param   maxopen            1000000000
ns_param   connections        5
ns_param   verbose            $debug
ns_param   extendedtableinfo  true
ns_param   logsqlerrors       $debug
ns_param   driver             postgres 
ns_param   datasource        remotehostaddress::remotedbname
ns_param   user               $user_account
ns_param   password           ""
Note also that your remote db must be configured to accept IP connections (set tcpip_socket = true in your postgresql.conf).
Posted by C. R. Oldham on
What I'm not sure of is if the multiple-db-pool support ever made it into the db api.  Right now if you use any of the db_* commands the toolkit will pick up one of the available pools and historically there was no way to say which one that was.  So for db connections that were not to the main schema, you had to use the bare AOLServer api for accessing the database.

Anyone know the answer to that?

Posted by Jade Rubick on
I believe it was added in, C.R. Check api-doc to be sure, though.
Posted by Rocael Hernández Rizzardini on
AFAIK, not out of the box support for multiple pools, although you can use ns_db * API to make it work with an specific pools.
Posted by Andrew Piskorski on
C.R. and Rocael, yes, out-of-othe-box multi-db support certainly did get added to the db_* API, nearly a year ago now. (That's what all those -dbn switches you should see in the OpenACS 5.0 api-doc are for). However, at least two users have reported that something or someone has egregiously broken the multi-db support since it was added, and I still haven't seemed to find a chance to look into what might be wrong. :(
Posted by Rocael Hernández Rizzardini on
Thx Andrew, I check for db_1row and in the docs do not mention the -dbn flag... but the others db_* seems to show it ;o)
Posted by Joel Aufrecht on
Posted by Andrew Piskorski on
Docs! Way cool. Good work, Joel.