Forum OpenACS Development: Connecting with SQLServer

Posted by Tracy Adams on
We have a need to connect to SQLServer to access some data in another system (the data is needed in real time).

Wanted to check in to see if others had done this/had ideas/was interested in collaborating.

Posted by Malte Sussdorff on
This is great, hopefully you manage to write a database driver for AOLserver (instead of the ODBC fallback).
Posted by Dirk Gomez on
Tracy, are you talking about AOLserver - SQLServer or Oracle -SQLServer. For the latter case there is a SQLServer Oracle gateway. You may also want to look at using ODBC to connect the two databases.
Posted by David Walker on
The AOLServer list had a discussion recently on nsfreetds. It is an aolserver db driver said to be able to connect to ms sql server.
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.

Posted by Ryan Gallimore on
I have successfully installed Freetds 0.62.1 and the nsfreetds 0.4 driver on OpenACS 5.1.4, and I can even run queries on my MSDE 7 database from a tcl page, but around 15 minutes after I do so aolserver stops responding; the processes don't crash, the site just does not respond to requests. There are no errors in the log. I am running Red Hat Enterprise Linux 2.32 on Intel.

I don't think it's the driver load itself, since after a restart, the site is fine... until I run a query:

db_foreach -dbn yardi get_query {
  select semail from tenant
} {
  ns_write "$semail,"

The query works.

Here are the relevant lines from config.tcl:

ns_section "ns/db/drivers"
  ns_param freetds ${bindir}/

ns_section ns/server/${server}/acs/database
    ns_param database_names [list main yardi]
    ns_param pools_main  [list pool1 pool2 pool3]
    ns_param pools_yardi [list yardi]

ns_section ns/db/pool/yardi
  ns_param driver      freetds
  ns_param connections  1
  ns_param user        username
  ns_param password    password
  ns_param datasource  yardi ;#datasource name in freetds.conf file
  ns_param verbose      off

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

When I first ran the query I got an "Unknown driver '$driver_type'" error, so I edited 00-database-procs.tcl below to add the nsfreetds driver. I realize there's some parameter in config.tcl that should take care of this for me, but adding the following lines seemed to work:

        # These are the default driverkey values, if they are not set
        # in the config file:

        if { [string equal $driver {Oracle8}] } {
            set driverkey {oracle}
        } elseif { [string equal $driver {PostgreSQL}] } {
            set driverkey {postgresql}
        } elseif { [string equal $driver {ODBC}] } {
            set driverkey {nsodbc}
      } elseif { [string equal $driver {nsfreetds}] } {  ; # added this
          set driverkey {freetds}
        } else {
            set driverkey {}
            ns_log Error "$proc_name: Unknown driver '$driver_type'."

One other thing - when compiling nsfreetds-0.4, I got an undefined function error for tds_set_charset. I commented this call out completely in nsfreetds.c (since I could find it nowhere else) and it compiled fine.

Any idea what's causing aolserver to stop responding to requests fifteen minutes after I successfully run a query on my SQL database?
Did one of my modifications screw things up?
Is there some timeout parameter I am missing in my config file?

Thank you in advance for your help!

Continuing on with my description of the last error, just over an hour after I ran the query to my sql database, aolserver stopped responding after throwing these notices:
[-sched-] Notice: dbinit: closing old handle in pool 'yardi'
[-sched-] Notice: nsfreetds: DbClose(yardi) called.
[-sched-] Notice: nsfreetds: DbCancel(yardi) called.
Do I need to release the handle to my sql database after I run the query? Thanks.