Forum OpenACS Q&A: 2nd database

Collapse
1: 2nd database
Posted by Vince T on 11/12/04 01:46 AM
How can I access and manipulate data from a 2nd database on a 2nd machine?

I have an application running on another machine (non-acs data). Fortunately that database is running postgres.

I would like to integrate the data from that application into my current oacs site. I was going to re-write that machine's jsp pages into my site and wanted to just go directly to the database.

Can somebody point me in the right direction?

Thanks in advance.

Collapse
2: Re: 2nd database (response to 1)
Posted by Don Baccus on 11/12/04 02:09 AM
Study the documentation on the database pools mechanism.  If you're using our standard config.tcl customized for your setup, it should be naming the pools available for OpenACS explicitly.

The idea is that you specify a new pool for the non-OpenACS database, including the correct port and hostname for the machine it is running on.

You then leave that pool out of the list of pools OpenACS should use.  If you're running a relatively recent version of OpenACS (5.0 or better I think) the db_* API allows you to name an explicit pool to grab the handle for running the db_* query you're specifying.  Just give the name of the pool you've set up for the non-OpenACS database, and do your queries (no need for queryfile entries for these of course).

Hope this helps get you started ...

Collapse
3: Re: 2nd database (response to 1)
Posted by Andrew Piskorski on 11/12/04 09:21 AM
As of spring 2003, the OpenACS db_* API includes full support for multiple databases, via the -dbn switch.

Last I checked, the default OpenACS AOLserver config file (etc/config.tcl) doesn't include instructions on how to configure things properly for that, but the comments in packages/acs-tcl/tcl/00-database-procs.tcl explain how.

Collapse
4: Re: 2nd database (response to 1)
Posted by Steve Manning on 11/12/04 10:22 AM
We use the dbn switch that Andrew mentioned on a live site and it works very well.

These are our config settings: 


ns_section "ns/server/${server}/acs/database"
ns_param database_names  [list main rohde]
ns_param pools_main      [list pool1 pool2 pool3]
ns_param pools_rohde     [list rohde_pool]

  
ns_section ns/db/pools
ns_param   pool1              "Pool 1"
ns_param   pool2              "Pool 2"
ns_param   pool3              "Pool 3"
ns_param   rohde_pool         "Rohde Databox"

# --- Pool for Rohde Databox ---
ns_section ns/db/pool/rohde_pool
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         10.8.1.1:5432:rohde
ns_param   user               xxxxxx
ns_param   password           xxxxxx

Then when we need to access the rohde db we use: 

db_foreach -dbn rohde ...
i.e the name from the database_names setting which points to the connection pools available for that database.

Hope this helps.

- Steve

Collapse
5: Re: 2nd database (response to 1)
Posted by Joel Aufrecht on 12/27/04 09:42 PM