FreeTDS.org Home  |  News  |  Software  |  Documentation  |  Support  |  Contribute

FAQ

The FreeTDS User Guide, included in each release, is the appropriate place for new users to start.

This list of frequently asked questions and answers about FreeTDS is updated between releases to cover problems since the last release, and to answer perennial questions (there are some).

$Id: faq.html,v 1.19.2.1 2003/05/17 18:21:49 jklowden Exp $

  1. General Questions
  2. Implementation
  3. Programming: C++, Sybperl, SQSH, & PHP
  4. Problems Building
  5. Problems Running

General Questions

What is FreeTDS?

FreeTDS is a free re-implementation of the TDS (Tabular Data Stream) protocol that is used by Sybase and Microsoft for their database products. It currently implements TDS 4.2, 5.0, 7.0 and 8.0, and can communicate with any Sybase product as well as Microsoft SQL Server.

FreeTDS comes with a low level library (the TDS layer) along with a number of APIs (Application Programming Interfaces). The current APIs are DB-Lib, CT-Lib, and ODBC.

A JDBC driver has also been contributed under a BSDish license and is available from the download page. It does not require the FreeTDS C libraries.

Where do I get FreeTDS?

You can get the latest FreeTDS from Metalab or its mirrors. See "Quick Links" on the FreeTDS home page. You may also use CVS (on sourceforge.net); see the User Guide for details.

If your firewall prevents FTP access, you can download the JDBC jar file via http.

Your favorite operating system may have a package of some kind available. You might want to check there. Occasionally someone contributes a package here, which we keep around. Post a message to the list if you're looking for one and don't find one in the usual places.

Does FreeTDS support MSSQL?

Yes, as long as you configure the libraries with the 4.2, 7.0 or 8.0 protocol version. See the User Guide for details.

Is there any documentation on the TDS protocol?

Yes, there is some preliminary documentation available.

Sybase has also made the TDS 5.0 protocol documentation available for free (price) download, however the license is too restrictive to be used by developers of this project and the core developers have decided to not obtain the documentation until all license issues are resolved.

Is there other documentation I should know about?

How can I get help (or support)?

After reading this FAQ and the User Guide, you might want to look at the mailing list archives. If you don't see your question answered there (or, if you'd just like to learn more about what's going on with FreeTDS), please subscribe to the mailing list. Questions new and old are cheerfully answered there. Traffic is not high, normally around 10 messages a day.

Netiquette note: It's considered poor form to mail "help me" questions directly to the developers of any free software project, this one included. Please direct your question to the list, where someone with the available time and expertise can help you.

Who is responsible for FreeTDS?

Brian Bruns Started this crazy thing
Gregg Jensen Message handlers and extra datatype support and some sybperl stuff?
Arno Pedusaar Donated his TDS4.2 code to the cause
Mihai Ibanescu GNUified the packet
Craig Spannring JDBC driver and CVS repository.
Mark Schaal Cleaned up message handling, bug fixes, ctlib unittests
Kevin Lyons Various TDS bug fixes
Sam Tetherow Various TDS bug fixes
Geoff Winkless Lost connection stuff
Ken Seymour ODBC Driver Fixes
Scott Gray TDS 7.0 numeric support and bug fixes
Bob Kline NTEXT support
Koscheev Andrey negative money patch
Dennis Nicklaus vxWorks port and fixes for dbdata() and SYBVARBINARY
Brandon M. Reynolds fix for arbitrarily large queries under dblib.
Steve Langasek off by one fixes and autoconf byte size thing.
Mark J. Lilback implementation of dbstrlen and dbstrcpy
Thanks go to the folks at A2i, Inc. http://www.a2i.com for funding the development of dblib host file bulk copy and writetext support, and to Dave Poyourow there for helping with the debugging.

(These addresses have been mangled to defy "spamaton" programs that mindlessly collect email addresses from the web. To send email to anyone listed above, delete the "nospam." part of the address.)

Why LGPL license instead of license foo?

LGPL was chosen because if you want a commercial client, you can buy them from Sybase, Microsoft or others. I do believe BSDish licenses are better in some cases, but not for something like this.


Implementation

How do I access the CVS Repository?

The CVS repository is maintained in two separate projects on SourceForge

Read-only access requires no password. For the right to commit changes, contact the Project Admins listed on the SourceForge project summary pages.

To obtain the current CVS copy of FreeTDS

cvs \
-d:pserver:anonymous@cvs.freetds.sourceforge.net:/cvsroot/freetds \
login

cvs -z3 \
-d:pserver:anonymous@cvs.freetds.sourceforge.net:/cvsroot/freetds \
checkout freetds

Building from CVS is described in the file INSTALL.CVS, and involves running the autogen.sh script to generate and run configure then make and make install.

Do I need to configure anything?

When you untar the package you will need to run:
$ ./configure

You also have the following options you can add to the configure statement:

--with-tdsver=VERSION
specify the fallback TDS version
(4.2, 4.6, 5.0, 7.0 or 8.0) [default is 5.0].
--enable-msdblib
enable Microsoft APIs and behavior for DBLib (useful only for porting Win32 dblib apps)
--enable-dbmfix
rename dbopen() to tdsdbopen() as a work around for dbm name conflict
--with-gnu-ld
assume the C compiler uses GNU ld [default=no]
--enable-shared
build shared libraries [default=yes]
--enable-static
build static libraries [default=yes]
--with-libiconv-prefix=DIR
search for libiconv in DIR/include and DIR/lib. required for FreeBSD. iconv is a library for converting between charsets.
--with-iodbc=DIR
build odbc driver against iODBC in DIR
--with-unixodbc=DIR
build odbc driver against unixODBC in DIR
--with-ssl
enable ssl. This option enable NT domain authentication for ms sql server (use with TDS7/8). Version after 0.60 do not require this setting, domain login are always enabled and use a LGPL implementation.
--enable-threadsafe
compile for thread safety [default=no]. Some environment have problem with theadsafe socket function. Use with care (work on recent Linux, *BSD and should work on Tru64 environment).

IMPORTANT You must either build with TDS version 4.2 to access Microsoft SQL Server or Sybase prior to 10.0 or change these values to 4.2 at runtime! TDS 7.0 may also be used to talk to Microsoft SQL Server 7 and above.

What is this SYBASE environment variable?

Many programs look for the SYBASE environment variable in order to find the library's home. You will want to set this to the main FreeTDS directory. For example, if FreeTDS is installed in /usr/local/freetds (meaning the libraries were installed in /usr/local/freetds/lib), then your SYBASE variable would be set to /usr/local/freetds.

What other environment variables are there?

How do I install the RPM?

rpm -ivh freetds-0.52-1.i386.rpm (as root) will install the libraries.

rpm -ivh freetds-devel-0.52-1.i386.rpm (as root) will install the headers and other stuff needed to build other stuff.


Programming: C++, Sybperl, SQSH, & PHP

How do I compile Sybperl with FreeTDS?

How do I compile SQSH with FreeTDS?

How do I compile PHP 3 with FreeTDS?

Please refer to the User Guide.

One small PHP hint, mailed to the FAQ master in May 2001:

In the mailing list archives I noticed a few people discussing a problem I just had.

An attempt to make a connection to a MS SQL server from PHP would fail, leaving a message in the Apache error log:

        "connect: Network is unreachable
         DB-Library: Login incorrect"

The problem turned out to be a very simple one to fix. In the php.ini file under the sybase section, there is a directive that sets the path to the sybase interfaces file "sybase.interface_file = "

After uncommenting this and setting it to a reasonable value (ie. /usr/local/freetds/interfaces), things started working.

Which Perl library should I use?

There are four options for using TDS and Perl to connect to a Sybase or MSSQL database, DBD::Sybase, DBD::ODBC, DBD::FreeTDS, and Sybperl.

From Mark Schaal:

DBD::Sybase is the recommended option, and yes it does work with MSSQL. You will need to install the perl DBI module and the FreeTDS package, particularly the CTLib portion. Set your SYBASE environment variable to /usr/local/freetds and install DBD::Sybase. Don't worry too much if some of the tests fail. Do worry if the module doesn't compile. Make sure you have the most recent version of FreeTDS installed. You can check the mailing list archives or ask the mailing list for help.

DBD::FreeTDS does not depend on the FreeTDS libraries. It is minimally functional but it is considered alpha software and is not being actively developed.

From Michael Peppler (mpeppler@peppler.org):
Sybperl is a thin wrapper around the Sybase C APIs. It's a lot more mature than DBI/DBD::Sybase (I've been working on it for 9 years :-) and it's maybe more natural to use for someone who already knows the Sybase APIs (or MS's DBlibrary). It's a little more powerful/flexible than DBI, though obviously less portable. It's still actively maintained and developed (by yours truly)

From Brian:
DBD::ODBC is the newest option available. Its primary advantage is not having to load another DBI driver if you already have DBI::ODBC load for other systems. On the downside, it may be a little less robust than DBD::Sybase.

Are there any known issues?

ODBC can be confusing to set up, but it works, subject to limitations in the implementation. See the samples directory for example .ini files.

BCP of text and image types is currently broken for Microsoft servers.

BCP does not support TDS 4.2. You must use a more modern protocol version.

FreeTDS 0.60 was released without properly upgrading the library version numbers. This can cause your applications to be confused. You can use ldd(1) to check what library they're linking to, and you can use 0.60.1 to correct the version number problem. See README.0.60.1 for details.

src/dblib/unittests/t0022 fails for Microsoft servers. 16 October 2002

Is FreeTDS thread safe?

TDS is designed to be thread safe in the following manner. Different threads may all use separate connections without interfering with each other. Threads may not share a DBPROCESS or CS_CONNECTION without controlling access via a mutex however.

Any plans to write a C++ class library directly over TDS?

I have a JDBC-like class library written over dblib. I may port that (probably not soon).

Another option may be using libodbc++, but I have not tested this.

Are there plans to implement the OpenServer protocol/library?

Not at this point, there is still much work to do on the client protocol. But, libtdssrv will do the trick for some applications.

How can test my FreeTDS JDBC Driver?

Rajkumar Seth offers the following:

  static void ftdsjdbc () {
    try {
    	// use your hostname and port number here
      String url = "jdbc:freetds:sqlserver://nt1:1433/master"; 
      String login = "sa"; // use your login here
      String password = "secret"; // use your password here

      if (false) { //Sybase 
        url = "jdbc:freetds:sybase://unix1:4100/master";
        login = "sa"; // use your login here
        password = "secret"; // use your password here
      }

      Class.forName("com.internetcds.jdbc.tds.Driver"); 
      
      //open a connection to the database
      Connection connection 
      	  = DriverManager.getConnection(url, login, password);
      //to get the driver version
      DatabaseMetaData conMD = connection.getMetaData();
      System.out.println("Driver Name:\t" + conMD.getDriverName());
      System.out.println("Driver Version:\t" + conMD.getDriverVersion());
      //create a statement
      Statement st = connection.createStatement();
      
      //execute a query
      ResultSet rs 
      	  = st.executeQuery("SELECT * FROM master.dbo.sysprocesses");

      // read the data and put it to the console
      while (rs.next()) {
        for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
          System.out.print(rs.getObject(j) + "\t");
        }

        System.out.println();
      }

      //close the objects
      st.close();
      connection.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }


Problems Building

Apache/PHP core dumps on start up

You'll probably need to compile FreeTDS with "--enable-dbmfix", then edit PHP's "functions/sybase.c" file, and find any references to "dbopen" and replace with "tdsdbopen", then compile and run etc...

The reason for this is that Apache is linked against DBM which includes a function named dbopen() too.

Update: fixed in 0.53pre2, dbopen is now always a define to tdsdbopen

I get a message like "undefined symbol g_tds_err_handler" on Solaris.

There is a bug in the FreeTDS autoconf scripts on both Sparc and x86 platforms when linking to PHP and DBD::Sybase.

The workaround is to add "-ltds" to the link command.

For PHP, edit the EXTRA_LIBS line in config_vars.mk

For DBD::Sybase edit the Makefile after running perl Makefile.PL. (Please send mail to the list if this doesn't help.)

If you have a Solaris machine and the requisite autoconf skills to fix this, a patch would be appreciated.

Status: fixed in version 0.60

I couldn't get DBD::Sybase 0.93 to compile.

Use DBD::Sybase 0.94 or above.

Getting it to work on MAC OS X.

Wed 16 October 2002

Mark J. Lilback rolled up his sleeves and solved this. But, it's not baby simple, because you have to use bleeding edge tools. The latest stable release of libtool, which FreeTDS uses to generate its configure script, doesn't do what you need. But, as a dynalib builder, you're used to that, eh?

To get started, first, obtain a current CVS version. Then follow Mark's instructions:

Here's the final process that allows compiling out of cvs. I've tested it on a clean install (after mucking around with many solutions on my system) and it should work for anyone on 10.2.

The steps seem kinda stupid in some places, but that's because most users don't have a /usr/local/bin and it isn't in the path by default. I also avoided giving shell commands because the default shell is tcsh and I refuse to deal with anything other than bash.

  1. If you don't have /usr/local/bin, create it.
  2. Run the command "ln -s /usr/bin/glibtoolize /usr/local/bin/libtoolize"
  3. Add /usr/local/bin to your path if not there.
  4. Download and install autoconf-2.54, making sure it is in your path. I installed it in /usr/local/autoconf-2.54 and added /usr/local/autoconf-2.54/bin to my path just for getting freetds to work, but I'm paranoid about updating Apple-supplied software.
  5. Apply the patch to /usr/share/libtool/ltmain.sh. This modifies the installed libtool with a libtool patch that is in CVS but has not made it into a public release.
  6. Follow the normal instructions for compiling freetds from cvs.

After this, they compile and the unit tests work up to dblib0013, which hung for 10 minutes or so until I canceled it.

There were a couple of other issues I want to clean up at some point (like the fact that iODBC is installed on the system by default, so there is no need to specify a directory), but it works with the above steps.


Problems Running

Why is my TDS protocol wrong since upgrading to 0.53?

You upgraded your FreeTDS library and now seem to be connecting at a different protocol version, but your interfaces file hasn't changed. What gives?

Between the 0.51 and 0.53 releases a lot of changes went into the configuration options. Formerly, at least three different schemes existed for specifying the protocol version:

Location Example format
build-time (./configure) 4.2
interfaces file tds4.2
TDSVER environment variable 42

The preferred form is now 4.2 and the others are deprecated and have been or will be removed in subsequent releases.

What does this unknown marker message mean?

Most of the time, it means you're not using the right protocol version. That can happen even if your ./configure was done correctly. Try setting the TDSVER to 70. If that works, double check your work. If your interfaces file and ./configure were right, but you needed the environment variable anyway, please post a message to the list and help us track it down.

What if I get a connection refused message?

You want to make sure:

Steps:

  1. Try telnet <hostmachine> <port> and see if it's listening. You should get some form of response from the server (actual text varies by vendor & version).
     
  2. Try tsql -U <hostmachine> -p <port> -U <username> -P <password>

    That will connect to the server, bypassing the freetds.conf file. If it doesn't work, the problem lies upstream.

  3. Try tsql -S <servername> -p <port> -U <username> -P <password>

    That will connect to the server using freetds.conf. This allows you to isolate freetds.conf mistakes. man tsql for more.

  4. Check your ./configure and environment variables. The --with-tdsver determines what flavor of the TDS protocol your runs by default; the $TDSVER variable overrides that default. The following combinations are reasonable:
  5. Vendor Version TDS Version
    Sybase 4.92+  5.0
    Microsoft 6.0, 6.5 4.2
    Microsoft 7.0/2000 7.0
     
  6. Edit the PWD file and try make check. It will call unittests for libtds, ctlib, dblib and odbc in that order

  7. Compile sqsh and try that before the more complicated stuff (PHP/Perl). If you can connect with sqsh, you don't have a FreeTDS problem.

The Microsoft SQL Server is listening, my configuration and environment are set up per question 6.1, but logins still fail.

Yeah, make sure you're not relying on integrated security. Microsoft supports two security models in three permutations:

  1. Windows NT Authentication Mode (Windows NT Authentication)
  2. Standard Mode (SQL Server Authentication)
  3. Mixed Mode (Windows NT Authentication and SQL Server Authentication)

You need either Standard or Mixed mode.

"Windows NT Authentication", often called "integrated security", relies on Microsoft's trusted connections and is not supported by FreeTDS. In it, user's network security attributes are established at network login time. When connecting to the database server, SQL Server uses Windows NT facilities to determine the validated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.

FreeTDS supports the traditional database security model, which Microsoft terms "SQL Server Authentication" but is frequently known as "standard security". Username+Password pairs have to be passed to the server explicitly.

Mixed Mode allows users to connect using either authentication method. Users who connect through a Windows NT account can make use of trusted connections in either Windows NT Authentication Mode or Mixed Mode. After successful connection to SQL Server, the security mechanism is the same for both modes.

Update: support for "Windows NT Authentication" has been added in 0.60. This code is quite new, hasn't seen much use and might work. ;)

My text data are being truncated or are causing my client to break.

The text data type is different from char and varchar types. The maximum data length of a text column is governed by the textsize variable on the server. Microsoft claims in their documentation to use a default textsize of 4000 characters, but in fact their implementation is inconsistent. Sometimes text columns are returned with a size of 4 GB!

The best solution is to make sure you set textsize to a reasonable value when establishing a connection. For example:


set textsize = 10000

My dates aren't formatted right!

Some dates turn out better than others.

If you think your dates should look like 2001-12-13 17:58:55.000, but you're seeing something like Dec 13 2001 05:58PM instead (or vice versa), you've bumped into driver behavior. There's no standard governing the default character string representation of a datetime datatype. Different drivers make different choices, and your driver has chosen a representation for you.

Microsoft's ODBC driver (which is used by among other things the Query Analyzer tool) converts datetime to an ISO format. That format has the advantages of being all numeric: sortable, unambiguous, and locale-independent. db-lib and ct-lib, in contrast, as well as the FreeTDS ODBC driver, use the MMM DD YYYY hh:mm format (fixed in CVS, October 2002).

If you want to be sure your queries always return dates in a particular format, don't leave the formatting up to the driver! Use the Convert function. For example:



1> select convert( varchar(30), getdate(), 120 ) as Now
2> go
 Now
 ------------------------------
 2002-07-02 12:36:31

As of version 0.60, the default datetime->string conversion is controlled by the locale.conf file. See the User Guide for details.

There are two other date issues.

  1. FreeTDS dates are restricted to the range supported by the standard C library, approximately from 1970 to 2038. This restriction was lifted with version 0.60.
  2. Big endian clients experience difficulty with Microsoft servers. See Known Issues in the User Guide.


Updates and comments FreeTDS FAQ Master