|
Home | News | Software | Documentation | Support | Contribute |
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).
FreeTDS is a free implementation of the TDS (Tabular Data Stream) protocol that is used by Sybase and Microsoft for their database products. It implements TDS 4.2, 5.0, 7.0 and 8.0, and can communicate with any Sybase or Microsoft SQL Server.
FreeTDS comes with a low level library (the TDS layer) along with a number of APIs (Application Programming Interfaces). The APIs are DB-Lib, CT-Lib, and ODBC.
You can get the latest FreeTDS from Ibiblio or its mirrors. See "Quick Links" on the FreeTDS home page. You may also use the CVS repository (on sourceforge.net); 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.
Yes. Microsoft servers don't usually accept TDS 5.0 connections. Use one of versions 4.2, 7.0 or 8.0 of the protocol. See the User Guide for details.
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.
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.
| 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.)
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.
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.
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”.
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.
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.
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.
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.
As of version 0.82, the TDS utility library is not built as a shared object. There is a static library libtds.a, although it's not installed by make install. libtds is statically linked to the "official" client libraries, ct-lib, db-lib, and ODBC.
Distributing libtds as a shared object 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. The only benefit was to programs that use more than one client library.
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.
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:
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.
Errors can sometimes be confusing. When an application uses the library incorrectly, or when there are problems in a data file being uploaded with BCP, the message returned by FreeTDS can sometimes be misleading. In the latter case, it's often necessary to examine the log file to understand what went wrong.
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 does not construct a complete result set first, unless it needs to (say, to execute an ORDER BY clause). Instead, it sends the rows as they're selected/formed, in real time (if you will). Likewise, the client libraries do not read all the rows from the server before making them available to the client application.
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.
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.
Not at this point, there is still much work to do on the client protocol. But, libtdssrv will do the trick for some applications.
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 protocols such as the db-lib dbrpcparam.
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 variable to a
value appropriate for your server: normally 5.0 for
Sybase and 7.0 for Microsoft. If that works, double
check your work. If your freetds.conf file and
configure options were right, but you
needed the environment variable anyway, please post a message to
the list and help us track it down.
You want to make sure:
Steps:
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.
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.
| Vendor | Version | TDS Version |
| Sybase | 4.92+ | 5.0 |
| Microsoft | 6.0, 6.5 | 4.2 |
| Microsoft | 7.0/2000 | 7.0 |
Edit the PWD file and try make check. It will call unittests for libtds, ctlib, dblib and odbc in that order
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.
Microsoft supports two security models in three permutations:
"Windows NT Authentication", often called "integrated security", relies on Microsoft's domain logins, which establish a user's network security attributes at network login time. When connecting to the database server, SQL Server accepts an encrypted password in the login packet, and uses Windows NT facilities authenticate it, usually via the Primary Domain Controller (PDC). The server then permits or denies login access based on the response.
With traditional "Standard Mode" authentication, usernames and passwords are stored within SQL Server. They are passed in the login packet as plaintext, and connection requests are authenticated without consulting the operating system.
FreeTDS supports both security models. Domain logins are recognized by the presence of a backslash (\) character in the username. See the User Guide for details.
Encrypted connections to Microsoft SQL Server 2008 using FreeTDS 0.82 do not work. Avoid them. Patches welcome!
The text data type is different from char and varchar types. The maximum data length of a text column is governed by the textsize connection option. 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 the textsize option to a reasonable value when establishing a connection. For example:
1> set textsize 10000 2> goSee also the “text size” option in freetds.conf.
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.
db-lib and
ct-lib, 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.
This is usually a webserver configuration issue, typically permissions reading freetds.conf or similar. Remember that the account running the e.g. Apache server is normally not the one you use to log in, or to test your script with on the command line.