Forum OpenACS Q&A: multi-db and -dbn

Request notifications

Collapse
Posted by Marcia Prescott on
I'm using the 5.0 beta version running with postgres.  I have an oracle database that I'd like to access using the multi-db support.  I've followed the directions as closely as I understood in the 00-database-procs.tcl file.  However, I don't understand what the database name is.
I tried to set up oracle as a fourth pool.
set mydbn [nsv_get {db_default_database} .]
This returns default

set mydbn [db_name -dbn postgres]
this doesn't work, I get an error about a key

set mydbn [nsv_get {db_pool_to_dbn} pool3]
This returns default

When I replace pool3 with pool4, I get the error about a non-existant key.

I would guess that -dbn ora would access the ora database.
What am I doing wrong?

Here's part of my config file.

ns_section "ns/db/drivers"
ns_param  postgres      ${bindir}/nspostgres.so  ;# Load PostgreSQL driver
ns_param  ora8          ${bindir}/ora8.so

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/pool1
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        ${db_host}:${db_port}:${db_name}
ns_param  user              $db_user
ns_param  password          ""

ns_section ns/db/pool/pool2
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        ${db_host}:${db_port}:${db_name}
ns_param  user              $db_user
ns_param  password          ""

ns_section ns/db/pool/pool3
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        ${db_host}:${db_port}:${db_name}
ns_param  user              $db_user
ns_param  password          ""

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${db_host}:${db_port}:${db_name}
ns_param  driver            ora8
ns_param  datasource        {}
ns_param  user              $odb_name
ns_param  password          $odb_password

ns_section ns/server/${server}/db
ns_param  pools              "*"
ns_param  defaultpool        pool1

# AOLserver can have different pools connecting to different databases
ns_section ns/server/${server}/database
ns_param  databases [list pg ora]
ns_param  pools_pg  [list pool1 pool2 pool3]
ns_param  pools_ora [list pool4]
ns_param  driverkey_pg {postgresql}
ns_param  driverkey_ora {oracle}

I would appreciate any suggestions and help on understanding this problem.
  Thanks,
  Marcia Prescott

Collapse
2: Re: multi-db and -dbn (response to 1)
Posted by Trenton Cameron on
I have a similar problem:
I am trying to connect to two databases following the guide given in the 00-database-procs.tcl file. The following is a snippit from my config.tcl

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/pool1
...
<The first three databases are postgres and the 4th an oracle there config is standard and so has been ommited>

ns_section ns/server/${server}/db
ns_param  pools              "*"
ns_param  defaultpool        pool1

ns_section "ns/server/${server}/acs/database"
#ns_param  availablepool  pool1
#ns_param  availablepool  pool2
#ns_param  availablepool  pool3
ns_param  databases [list pg ora]
ns_param  pools_pg  [list pool1 pool2 pool3]
ns_param  pools_ora [list pool4]

<End config file snippit>

If I leave the availablepool lines uncommented the server starts but using a -dbn ora returns that ora was an invalid key. If I leave the lines commented I get the error that
        * Database pool "pool4" is of type "oracle". The first database pool available to OpenACS was of type "postgresql". All database pools must be configured to use the same RDMBS engine, user and database.

I am a little confused as to where my problem lies.  Thanks for any insight that you can give me.

Collapse
3: Re: multi-db and -dbn (response to 1)
Posted by Andrew Piskorski on
I recomend naming your pools to indicate which db they below to, e.g. "pg_pool_1". It's not strictly necessary but helps keep things clear.

If you want to use the multi-db support you should not be using the "availablepool" syntax, it is there only for backwards compatibility. So you are both correct to levae those availablepool lines out.

I don't know what the problem is, your config files look ok to me. I haven't touched or looked at any of that code since April 2003 though, so it's possible somebody broke it since then. Unfortunately I don't currently have Oracle and PostgreSQL set up for testing, so I can't test it myself right now.

What specific release of OpenACS are you using, the OpenACS 5.0.0b4 tarball? The multi-db stuff should work in all OpenACS 5.x versions, but like I said I haven't actually tested it for many months...

Collapse
4: Re: multi-db and -dbn (response to 1)
Posted by Trenton Cameron on
The code that I am running was checked out on the 8th of December from the 5.0 branch in CVS.  I have the same problems when trying to connect to 2 different postgres databases.  Althought the site loads, when I run

set temp [nsv_get {db_pool_to_dbn} pool4]

I get default back even thought pool4 should be in another database.  All of the pools come up as being from the default database.

Collapse
5: Re: multi-db and -dbn (response to 1)
Posted by Andrew Piskorski on
Looks like an unrelated problem, but you folks should be aware of another -dbn related bug that hasn't been fixed yet.