Forum OpenACS Development: Using Oracle *and* Postgres

Posted by Andrei Popov on 01/07/02 11:22 AM

[07/Jan/2002:11:24:48][31139.1024][-main-] Error:

The following database pools generated errors: Database pool "subquery" is of type "postgresql". The first database pool available to OpenACS was of type "oracle". All database pools must be configured to use the same RDMBS engine, user and database. Database pool "main" is of type "postgresql". The first database pool available to OpenACS was of type "oracle". All database pools must be configured to use the same RDMBS engine, user and database. Database pool "log" is of type "postgresql". The first database pool available to OpenACS was of type "oracle". All database pools must be configured to use the same RDMBS engine, user and database.

These messages are, well, descriptive enough. The question is: is it at all possible to use several pools linked to different RDBMS -- both Oracle *and* Postgres, like in my case?

I know for fact that I can connect to different databases within the same RDBMS driver (DataSource localhost::db1 in one pool and DataSource localhost::db2 in another). But also to use different `provider'?

2: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/07/02 03:58 PM

I looked at this option, but it does not seem to fit well into the current framework. There seems to be quite a few places where the next available pool (regardless of database provider) is used to perform a query...

3: Response to Using Oracle *and* Postgres (response to 1)

Posted by Don Baccus on 01/07/02 05:43 PM

If you add something like this:
<blockquote><pre>

ns_section "ns/server/${servername}/acs/database"
ns_param availablepool pool1
ns_param availablepool pool2
ns_param availablepool pool3
</pre></blockquote>
Your OpenACS 4 instance will only use those three pools. Any other pools you define can be accessed via "ns_db gethandle" and can access another RDBMS without interference from the toolkit.

I've not used this to move data from Oracle to PG, for instance, but have used it with Musea's script to move bboard items from 3.2 to 4.x. I open one pool for my existing 3.2 installation and three for OpenACS 4 and then run the scripts to migrate data.

Should work fine if the other db's Oracle, too ...

4: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/07/02 06:05 PM

Thanks, Don, this works, at least I can start up and do 'normal' things (i.e. those that don't use 'extra' pool). Do get the following error tough:

Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind select nsdb0 {select
             gl_code, gl_desc
           from
             gltm_glmaster}"
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec select $db $statement_name $sql 3"
    (procedure "template::query::multirow" line 5)
    invoked from within
...

From which I deduce that by default it tries to take data from Postgres pool.

The data source for the page is as follows, a very simple one -- just for testing:

# @datasource coa multirow
# Test data source
# @column cnt is the count of records from dual

set dbpool "boflex"

set db [ns_db gethandle $dbpool]

set query "select count(*) cnt from dual"

template::query get_coa coa multirow $query -db $db

5: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/07/02 06:20 PM

Fine, I see my mistake -- I won't be able to do all the nice things through proper OpenACS toolset, but I do get access to raw AOLServer calls, etc. It now is just a question how to marry it with templating (if at all, may as well just right good-ol' Tcl pages...)
Re-written as follows:
<pre>
set dbpool "boflex"

set db [ns_db gethandle $dbpool]

set query "select count(*) cntr from dual"

set row [ns_db 0or1row $db $query]

set cnt [ns_set get $row cntr]

ns_return 200 text/html "<h1>$cnt</h1>"
</pre>
...works correctly
Thanks again,

6: Response to Using Oracle *and* Postgres (response to 1)

Posted by Don Baccus on 01/07/02 07:14 PM

Sorry, I didn't make that clear. You can't use the OpenACS database api because it is using the "available pools" only ... sort of the opposite of what you wanted! So you need to use the "raw" ns_db interface.

7: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/09/02 03:02 PM

I must be going insane... One day it was all working, now it does not -- It just gets stuck and based on a few ``ammendments'' made to oracle driver, it gets stuck here:

[09/Jan/2002:14:03:24][7657.9223][-conn2-] Notice: dbdrv: opening database 'ora8:boflex.world'
[09/Jan/2002:14:03:24][7657.9223][-conn2-] Notice: ora8.c:1014:ora_open_db: entry (dbh 0x8169180)
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1141:ora_open_db: (dbh 0x8169180); return NS_OK;
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: >>> database handle is nsdb1 for boflex
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1387:ora_exec: generate simple message
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1388:ora_exec: entry (dbh 0x8169180, sql select count(*) c from dual)
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:2334:flush_handle: entry (dbh 0x8169180, row (nil))
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1296:handle_builtins: entry (dbh 0x8169180, sql select count(*) c from dual)
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1515:ora_exec: query type `1'
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: dbinit: sql(boflex.world): 'select count(*) c from dual'
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1734:ora_bindrow: entry (dbh 0x8169180)
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1777:ora_bindrow: n_columns: 1
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1829:ora_bindrow: name 1 `c'
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:1845:ora_bindrow: column `c' type `2'
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: >>> the set is t4
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:2047:ora_get_row: entry (dbh 0x8169180, row 0x81693e8)
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:2106:ora_get_row: >> fetch succeeded, copying to ns_set
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:2112:ora_get_row: >> we are at 0
[09/Jan/2002:14:03:25][7657.9223][-conn2-] Notice: ora8.c:2291:ora_get_row: >> Ns_SetPutValue was called as 135697384, 0, 1

This happens in both -- remote an local Oracle server configuration. Any idea what is going wrong?

8: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/09/02 03:04 PM

Forgot to say that after the last line of the log extract things just stop -- browser is waiting for data from the server, nothing is sent back. However, normal ACS processes are still running (i.e. the server itself does not lock up).

9: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrew Piskorski on 01/09/02 06:09 PM

Andrei, regarding my "Multiple Databases with the ACS DB API" hack for ACS 4.2, you are mistaken. By design, it works identically to the stock ACS 4.2 DB API, except that if you define the database name 'ora-foo' and then pass the switch '-dbn {ora-foo}' to any of the DB API procs, the DB API will use the 'ora-foo' database (which may consist of one or more AOLserver database handle pools) rather than the default database. It never uses the "next available pool regardless of database provider".

10: Response to Using Oracle *and* Postgres (response to 1)

Posted by Andrei Popov on 01/09/02 11:03 PM

Andrew,

You probably are right, but (and you have put a warning upfront on that) it does not fall into place as a full replacement of what is in current OpenACS -- there are some procedures missing (db_type, db_version) and some are returning not what OpenACS is expecting (can't recall exact errors now).

Unfortunately I did not have much time to try and fit it properly in (and it may well have solved my problems, to be fair) -- I just needed to get an access to an Oracle database from within a running OpenACS-Postgres instance, more as a proof of concept than anything else.