Forum OpenACS Q&A: MS SQLServer setup

Collapse
1: MS SQLServer setup
Posted by Marty Israelsen on 09/24/24 12:41 AM
Hi Gustaf, We currently have a naviserver instance that connects to both PostgreSQL and Oracle. We are now wanting to configure naviserver to connect up to MS SQLServer. Is there a good resource that shows how to configure this? Does it require a compile step too?
Collapse
2: Re: MS SQLServer setup (response to 1)
Posted by Andrew Piskorski on 09/24/24 06:03 PM
Do you plan to use the nsdbtds database driver? nsodbc might also work. (But I'm not sure, I've never used MS SQL Server.)
Collapse
3: Re: MS SQLServer setup (response to 2)
Posted by Marty Israelsen on 09/24/24 07:03 PM
Thanks Andrew for pointing me in the right direction! I am not sure which one would work best either? I will first try the nsdbtds to see how that works. Again many thanks for pointing me to these repositories:)
Collapse
4: Re: MS SQLServer setup (response to 1)
Posted by Antonio Pisano on 09/30/24 12:53 PM

Another possibility, depending on your use case, may be TDBC.

All the best

Collapse
5: Re: MS SQLServer setup (response to 1)
Posted by Gustaf Neumann on 10/13/24 11:27 AM
It really depends, for what purpose the integration is used. For infrequent calls to the database, TDBC might be sufficient.

But note, that for plain Tcl solutions the connection management of OpenACS/NaviServer is not in place. So probably, every SQL query requires in such cases a fresh connection to the database, which causes latencies. Another potential problem (crash) might be when many file descriptors are in use (>1024).

Therefore, using the NaviServer drivers is architecturally better. In case, there are problems with nsdbtds or nsodbc, please let me know.

Collapse
6: Re: MS SQLServer setup (response to 5)
Posted by Marty Israelsen on 11/21/24 12:43 AM
Hi Gustaf,

I am now back working on this nsdbtds connection with naviserver.  I got tsql to work but when I updated my config.tcl it give the error that it cannot load the nsdbtds.so because of an undefined symbol.

From error.log:
[20/Nov/2024:13:22:00][1.7fea3a39f800][-main:martyi_intra-] Notice: modload: loading module ns/db/driver/nsdbtds from file /usr/local/ns/bin/nsdbtds.so
[20/Nov/2024:13:22:00][1.7fea3a39f800][-main:martyi_intra-] Error: modload: /usr/local/ns/bin/nsdbtds.so: couldn't load file "/usr/local/ns/bin/nsdbtds.so": /usr/local/ns/bin/nsdbtds.so: undefined symbol: tds_free_connection

I compiled the nsdbtds by doing the following:

de <naviserver container>
cd /usr/local/src
git clone https://github.com/naviserver-project/nsdbtds.git
cd nsdbtds
make
#  Got error that it cannot find the config.h file..
# Modified Makefile to be:
FREETDS_HOME = /usr/local/src/freetds-1.4.22

# Ran make again
make
# Got errors
# Removed the following line from nsdbtds.c
    /* #include "freetds/string.h" */
# Ran make again
make
# It Compiled!! and generated the nsdbtds.so !!
make install      <-- this puts it into /usr/local/ns/bin

# Run tsql with -H and -S and it worked!  I could run SELECT commands
tsql -H <host>  -U <username> -P <passwd>
tsql -S egServer73 -U <username> -P <passwd>

Any ideas why I would get an undefined symbol tds_free_connection?  It does not seem like it could be from the '#include freetds/string.h' that I had to remove.

As always, thanks for your assistance,
Marty

Collapse
7: Re: MS SQLServer setup (response to 1)
Posted by Marty Israelsen on 11/21/24 01:14 AM
I also tried the MARS = yes option to see if it helped.  But I get the same symbol error.

[egServer73]
        #host = ntmachine.domain.com
        host = <hostname here>
        port = 1433
        tds version = 7.3
        MARS = yes