![]() |
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 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.
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.
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.
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.
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.)
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.
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.
When you untar the package you will need to run:
$ ./configure
You also have the following options you can add to the configure statement:
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.
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.
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.
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
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.
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.
Not at this point, there is still much work to do on the client protocol. But, libtdssrv will do the trick for some applications.
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(); } }
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
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
Use DBD::Sybase 0.94 or above.
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.
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.
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.
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.
You want to make sure:
Steps:
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.
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.
Yeah, make sure you're not relying on integrated security. Microsoft supports two security models in three permutations:
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. ;)
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
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,
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.