Forum OpenACS Q&A: MS SQLServer setup

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?

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.)

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:)

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

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.

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 tdsfreeconnection? 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

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

8: Re: MS SQLServer setup (response to 7)

Posted by Marty Israelsen on 11/22/24 01:24 AM

I also ran 'nm' on nsdbtds.so and found that there are two symbols for tds_free_connection: One of them is reporting Undefined. It looks like somehow the linker is not resolving it.

root@02c71ac2c2ba:/usr/local/src/nsdbtds# nm nsdbtds.so | grep tds_free_
0000000000009ba0 t tds_free_all_results
000000000000bec0 t tds_free_bcp_column_data
0000000000009330 t tds_free_bcp_column_data.part.0
000000000000bf70 t tds_free_bcpinfo
0000000000009220 t tds_free_column
000000000000a2d0 t tds_free_connection
                 U tds_free_connection
0000000000009db0 t tds_free_context
0000000000009620 t tds_free_input_params
000000000000ad20 t tds_free_locale
00000000000092f0 t tds_free_locale.part.0
000000000000a7e0 t tds_free_login
000000000000ad40 t tds_free_msg
000000000000c830 t tds_free_namelist
000000000000aad0 t tds_free_packets
00000000000097a0 t tds_free_param_result
0000000000009b10 t tds_free_param_results
0000000000009b80 t tds_free_results
0000000000009350 t tds_free_results.part.0
0000000000009b30 t tds_free_row
000000000000ad00 t tds_free_socket
000000000000a490 t tds_free_socket.part.0

I removed the following line from nsdbtds.c and this gets rid of the linking problem. And things appear to work now in naviserver;) But I fear that this is probably a memory leak that I just created.

   /* tds_free_connection(connect); */

Thoughs?

9: Re: MS SQLServer setup (response to 1)

Posted by Gustaf Neumann on 11/22/24 12:43 PM

Hi Marty,

I removed the following line ... But I fear that this is probably a memory leak that I just created.

I would share this fear. Probably the leak is not large, since the nsdb driver keeps typically (dependent on the configuration) the connection open for a long time, so this is probably not a big memory leak.

However, when looking at the source code, there is something strange: In case, the connect operation fails, the "connect" data is freed via tds_free_login(). The manual [1] shows, that tds_read_config_info() returns actually a "TDSLOGIN *", so tds_free_login() might be indeed the right operation.

Can you check whether this works?

All the best -g

[1] https://www.freetds.org/reference/a00574.html#ga9ff2436ea56ddcdecf984acf46ce4b90

10: Re: MS SQLServer setup (response to 1)

Posted by Gustaf Neumann on 11/22/24 02:42 PM

Another option might be to perform tds_read_config_info() only once and keep this information around.

I've updated nsdbtds to make the compilation clean.

11: Re: MS SQLServer setup (response to 9)

Posted by Marty Israelsen on 11/26/24 12:30 AM

Thanks Gustaf !!

This seems to work just fine:)

I appreciate your help and expertise! Marty

12: Re: MS SQLServer setup (response to 1)

Posted by Gustaf Neumann on 11/26/24 08:27 AM

Great that this works!

Many thanks to you as well!
Experience reports are also important for other users.

-g