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

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

General Questions

What is FreeTDS?

FreeTDS is a free implementation of the TDS (Tabular Data Stream) protocol that is used by Sybase and Microsoft for their database products. It can communicate with any Sybase or Microsoft SQL Server. It includes three client libraries: DB-Library, CT-Library, and ODBC. All libraries use the same protocol and can communicate with servers from both vendors.

Where do I get FreeTDS?

You can get the latest FreeTDS from ftp.freetds.org. See "Quick Links" on the FreeTDS home page. See the User Guide for details.

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 Microsoft servers?

Yes. Microsoft servers don't usually accept TDS 5.0 connections. Use one of versions 7.0, 7.1, 7.2 or 7.3 of the protocol. See the User Guide for details.

Does version X of FreeTDS connect to version Y of my Microsoft or Sybase server?

For the last several years, every version of FreeTDS has been able to communicate with every kind of TDS server. New servers sometimes introduce new protocol features, but the changes are small, and they're always compatible with old clients. Generally, you should use the latest version of the TDS protocol for your server. See the User Guide for details.

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?

Current contributors

Brian Bruns
Started this crazy thing
James K. Lowden
Project maintainer since 2003
Frediano Ziglio
Wrote great swaths of the ODBC driver and keeps the TDS library up to date with protocol changes. Also contributed most of the current encryption logic. Project maintainer since 2015.

For a more complete history, see AUTHORS in the distribution. Thanks also go to the folks at A2i, Inc. for funding the original development of DB-Library host file bulk copy and writetext support, and to Dave Poyourow there for helping with the debugging.

(To send email to anyone listed above, delete the "nospam." part of the address.)

Why LGPL license?

Brian Bruns started the project, and that's the choice he made. 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,” he said.


Documentation

What sort of documentation is available?

What about a Programmer's Reference Manual?

The best information is available from the vendors. FreeTDS means to conform to the documented (and, in some cases, undocumented) behavior of the vendors' offerings.

Is there any documentation on the TDS protocol?

There is preliminary documentation available. The most up to date version is in the User Guide.

Sybase publishes its TDS 5.0 Functional Specification.

Microsoft published its specification, too. It can currently be found on MSDN or search the web for “MS-TDS tabular data stream site:microsoft.com”.


Implementation

What's this SYBASE environment variable for?

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.


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

How do I compile Sybperl with FreeTDS?

How do I compile SQSH with FreeTDS?

How do I compile PHP with FreeTDS?

Please refer to the User Guide.

How should I choose among DB-Library, CT-Library, and ODBC?

FreeTDS offers three client libraries and one internal one (libtds). We generally encourage people to use one of the client libraries, and discourage writing to libtds, because the latter is evolving, more subject to change, less well documented, and harder to use. In choosing which client API to write to, you might want to peruse our brief discussion.

Where is libtds.so?

libtds.so doesn't exist and isn't needed. Any reference to it in your linker commands can be safely removed. Any test for its existence should be modified to test instead for the appropriate client library.

As of version 0.82, the TDS utility library is not built as a shared object. The static library libtds.a, is not installed by make install and is not needed by an applications that uses a client library (CT-Library, DB-Library, or ODBC). Because libtds is statically linked to the client libraries, they each now contain all the TDS functionality they need.

In the past, libtds was distributed as a shared object. We found that did more harm than good. Proper versioning and support was a burden on the developers, and it was just one more thing for a client application to link in. Only programs that used more than one client library benefited. Such programs are rare, and the benefit small.

Of course, it's still free software, and you're free to build a shared object of it if you want to. It's just not done "out of the box" by the distributed makefiles.

Which Perl library should I use?

There are four options for using TDS and Perl to connect to a Sybase or Microsoft database, DBD::Sybase, DBD::ODBC, DBD::FreeTDS, and Sybperl. As of 2010, DBD::Sybase and DBD::ODBC are your best choices. If you need paramaterized queries on a Microsoft server, use DBD::ODBC

Why does each connection support only one query at a time?

If you are accustomed to programming with other database servers, you may be surprised when you first encounter this aspect of the TDS protocol. When a TDS server—be it by Microsoft or Sybase—responds to a query, it may send a result set to the client. The server sends the rows as they're selected/formed, in real time (if you will). The client libraries make the rows available to the client application as they arrive.

The client library is tightly coupled to the server; they are synchronized, share state information. The server requires the client either to read all the results from a query, or to indicate that no further rows are desired i.e., to issue a cancellation. Until one of those two things happens, the server will not accept new queries on that connection. It will complain about "pending results".

How do mortal programmers cope with this strict one-query-at-a-time limitation? For one thing, they become better programmers.

It's important to realize that the selection of rows and their accumulation into a container of some sort are two different functions. A TDS server issues its results a row at a time, which the client library dutifully makes available to the application on arrival. It's up to the application—or a higher-level library—to form a "rowset" of some kind if desired.

But what about MARS® you ask? Doesn't that solve the problem? Well, no. MARS does make a TDS server more similar to other vendors' servers, and Microsoft benefits from one more just-like-the-others feature for its ODBC driver.

PHP note

If you use PHP, you will still run into this problem even if you create a new connection. The reason is that PHP is so nice that it will re-use the first connection if you connect again with the same parameters. You my wish to refer to this post by Daniel Fazekas in the mailing list archives.

Is FreeTDS thread safe?

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.

Problems Running

Output Parameters

I'm not getting my output parameters returned, but I seem to be doing everything right!

That's not a question!

Microsoft SQL Server 7 with SP3, and later versions, quietly changed (which is to say, broke) how they respond to queries that execute stored procedures with output parameters. Earlier servers let you send a query like EXECUTE A @P OUTPUT and fetch the output parameter as a special result row (technique varying by library). Newer servers simply don't send back that data. To elicit output parameters from them, you have to use the RPC functions such as the DB-Library dbrpcparam.

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 -H 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. See the User Guide for details.
  5. Edit the PWD file and try make check. It will call unittests for libtds, ctlib, dblib and odbc in that order

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

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. The vendors' DB-Library and CT-Library, in contrast, use the MMM DD YYYY hh:mm format.

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.


Updates and comments FreeTDS mailing list
$Id: 8f88c697b4b093237cd016d12841c907925337b3 $

Valid XHTML 1.0 Transitional