Nsfreetds FAQ

From AOLserver Wiki
Jump to navigation Jump to search

Here is the list of Frequently Asked Questions (FAQ) for the Panoptic FreeTDS driver for AOLserver, otherwise known as nsfreetds.

1. What do I need in order to get started?

Download the nsfreetds driver from SourceForge: nsfreetds-0.4-src.tar.gz

Verify that FreeTDS is built correctly by running "make check" which runs the acceptance tests that are part of FreeTDS. (Make sure to edit the file "PWD" in the root of the FreeTDS source tree appropriately.) This will also help verify that your "interfaces" file is correct.

I built FreeTDS with the following args to the configure script:

 ./configure --prefix=/usr/local --with-tdsver=7.0 --enable-msdblib --enable-dbmfix

Make sure you set the SYBASE environment variable to point to the root of the FreeTDS installation (in my case, SYBASE=/usr/local), even if you're connecting to Microsoft SQL Server only.

Configuring AOLserver is more difficult, as it requires getting the nsd.tcl right. This is the real "quick and dirty" guide to configuring a datasource under AOLserver:

  • In section ns/db/drivers, add a line "ns_param freetds nsfreetds.so", like this:
  ns_section "ns/db/drivers"
  ns_param freetds nsfreetds.so

(Change ".so" to ".dll" or whatever the appropriate file extension is for your platform for dynamically linked files.)

  • In section ns/db/pools, add a line "ns_param freetds freetds_pool", ilke this:
  ns_section "ns/db/pools"
  ns_param freetds freetds_pool
  • Create a section ns/db/pool/freetds, like this:
  ns_section "ns/db/pool/freetds"
  ns_param driver       freetds
  ns_param connections  1
  ns_param user         <your-database-user-here>
  ns_param password     <your-database-password-here>
  ns_param datasource   <your-datasource-name-here>
  ns_param verbose      off
  • Create a section ns/server/${servername}/db, like this:
  ns_section "ns/server/${servername}/db"
  ns_param Pools *
  ns_param DefaultPool freetds

Some caveats -- datasource is case-sensitive, at least when connecting to a Sybase server. Datasource is the name of the database as per the "interfaces" file. You may want to raise the connections parameter to be more than 1, but for testing setting it to 1 may be better.

2. What databases work with the driver?

The following configurations have been tested with nsfreetds-0.1pre and FreeTDS 0.51:

  • x86/Linux-2.2.18 AOLserver-3.3.1 => x86/WinNT-4.0 SQL Server 7 (tds7.0)
  • x86/Linux-2.2.18 AOLserver-3.3.1 => x86/WinNT-4.0 SQL Server 7 Service Pack 2 (tds7.0)
  • x86/Linux-2.2.18 AOLserver-3.3.1 => x86/Win2000 SQL Server 7 (tds7.0)
  • x86/Linux-2.2.18 AOLserver-3.3.1 => SPARC/Solaris-2.6 Sybase ASE 11.9.2 (tds4.2)
  • SPARC/Solaris-2.6 AOLserver-3.3.1 => x86/Win2000 SQL Server 2000 (tds4.2)

The following configurations have been tested with nsfreetds-0.2 and FreeTDS 0.53:

  • x86/Linux-2.4.17 AOLserver 3.4.2 => ???/??? Sybase ASE 11 (tds5.0)
  • x86/Redhat-7.2 AOLserver 3.4.2 => x86/??? SQL Server 2000 (???)

The following configurations have been tested with nsfreetds-0.2 and FreeTDS 0.53-CVS-snapshot:

  • x86/Redhat-7.2 AOLserver-??? => x86/WinNT-4.0 SQL Server 2000 (???)

The following configurations have been tested with nsfreetds-0.4 and FreeTDS 0.62.1:

  • x86/NetBSD 2.0_BETA AOLserver-4.0.7 => Sybase ASA 8 (tds 5)

In order to connect to SQL Server 7, use the TDS7.0 protocol (set this in the "interfaces" file). To connect to Sybase 11.x or SQL Server 2000, use the TDS4.2 protocol (again, set in the "interfaces" file).

3. Known Problems

  • There is limited support for datatypes. LOB support (via TEXT columns) is in place, but not very well tested.
  • Support for Stored Procedures is incomplete using the ns_db sp_* API.
  • There's a bizarre "Notice: " segfault bug under 4.0b1 (from CVS).
  • There may be some real bad thread-safety issues.
  • Concurrent connections (ns_param connection > 1) has not been tested well.
  • Multiple result-sets not handled properly (at least Sybase can return multiple result-sets, one after another, with a no-more-rows in between result-sets). Will probably solve this by adding a way to detect additional result-sets to the nsfreetds API.

3.1. Question: convert(datetime) fails with the error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." Why?

The default language for the database user specifies a different date format than is being passed to the convert(). For instance, if you get the error when doing this:

  convert(datetime, '12/31/1999')

then, chances are you're assuming mm/dd/yyyy while the default language's date format is dd/mm/yyyy. You can find out what your default language is by executing this query:

  SELECT name FROM master.dbo.syslanguages WHERE langid = @@default_langid

You can change the default language (for example, to us_english) using this SQL statement:

  sp_defaultlanguage <username> us_english

Replace <username> with the name of the user whose default language you wish to change. The sp_helplanguage stored procedure may help you choose which language you want to use, or the following query will list all the different language names:

  SELECT name FROM master.dbo.syslanguages