Forum OpenACS Development: Re: Connecting with SQLServer

Posted by Hamilton Chua on
Posted by Steve Manning on
We do a similar thing getting data from a different PG database. I was interested in your Openacs config because we have it slightly differently.

Firstly we specify a database section after the drivers:

    ns_section ns/server/${server}/acs/database
    ns_param database_names  [list oacs shop]
    ns_param pools_oacs      [list pool1 pool2 pool3]
    ns_param pools_shop      [list pool4]

Then we use the specified database_names as the parameter for the -dbn switch:

    db_0or1row -dbn shop some_sql {select something from somewhere}

This method also means you can easily add more pools to your second database.

- Steve

Posted by Andrew Piskorski on
Hamilton, the example config file in your link is using the obsolete "AvailablePool" syntax, which pre-dates and does not support the -dbn feature. It also never gives the "ns_param database_names" config line at all, which (as far as I recall) means that the -dbn feature can't possibly work with that config file. Are those the real config settings you actually used, or did you leave some out?

Also, your code example then does something really weird:

set handle [ns_db gethandle "pool4" ]
db_1row -dbn "pool4" "test" "select user_id from ecwork.dbo.users"
If that code works at all, it's totally by accident. When using the -dbn switch you should never be calling "ns_db gethandle" at all. Actually, when using any of the OpenACS db_* API you should not be calling "ns_db gethandle". Also, for the -dbn feature to work you need to pass a database_name to the -dbn switch, not a database pool.

Steve Manning's example above is the right way to do it.