FreeTDS"> DB-Library'> CT-Library'> ODBC'> freetds.conf"> ]> $Date: 2011/05/21 18:31:37 $ $Revision: 1.139.2.1 $ &freetds; User Guide A Guide to Installing, Configuring, and Running &freetds; Brian Bruns James K. Lowden 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 Brian Bruns and James K. Lowden Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts. A copy of the license is included in the section entitled GNU Free Documentation License. About this User Guide This User Guide describes &freetds; &version;. It is the product of (lots of) happy collaborative effort. Although Brian's name and mine are at the top of it, behind it are many others, who contributed thoughtful suggestions, bamboozled questions, stellar prose, and terse instructions. I don't mention this for the usual reasons (the enumeration of which I leave to you) but rather to emphasize that the purpose of our effort is to help you and those who come after you to have the easiest and most enjoyable time with &freetds;. It is surprisingly hard, after a while, to remember how it can be for someone newly approaching a project to use it. What seems as obvious as a fog horn to an old hand may be much more like the fog itself to the newcomer. That can make installing and setting up new software a puzzling or frustrating experience. You may have heard, It's easy if you know how. Indeed it is, and that's our purpose here: to make it easy, by letting you know how. This guide is here for you, and we hope that you will be here for it, that others might benefit from your experience or inexperience. The most recent version The version you're reading is: $Revision: 1.139.2.1 $ $Date: 2011/05/21 18:31:37 $ CVS control number $Id: userguide.sgml,v 1.139.2.1 2011/05/21 18:31:37 jklowden Exp $. can be found on the &freetds; web site, where you will also find the most up to date FAQ, as well as links to the anonymous and browseable CVS tree. If you find something wrong, unclear, badly put, misleading, or incorrigible, I hope you will let us know. Post your musings or rants to the mailing list (see Helping). Patches to doc/userguide.sgml are especially welcome, of course. By taking the time let us know what you think, perhaps the path to enlightenment will be made a little smoother for the fellow behind you. A few technical notes. This guide is written in SGML DocBook format, specifications for which are found in the DocBook book. It was converted to HTML with OpenJade. The SGML text is distributed with the rest of the source code, and may be edited with your favorite or least favorite text editor. Enough. Let's begin. --jkl What is &freetds;? &freetds; is re-implementation of C libraries originally marketed by Sybase and Microsoft SQL Server. It allows many open source applications such as Perl and PHP (or your own C or C++ program) to connect to Sybase or Microsoft SQL Server. &freetds; provides drop-in replacements for Sybase's &dblib; and &ctlib; Microsoft's &dblib; (which differs in small details from Sybase's) the &odbc; drivers from both vendors interactive SQL and BCP utilities The TDS part of the name comes from name of the protocol used to communicate with such servers: the Tabular Data Stream. &freetds; is distributed in source code form, and is expected to compile on just about any operating system. That means every form of Unix® and Unix-like™ system (including notable variants such as Interix® and QNX®), as well as Win32®, VMS®, and OS X®. If it doesn't compile on your system — and you're not using MS-DOS® — it's probably considered a bug. Background: The <acronym>TDS</> Protocol and related <acronym>API</>s TDS is a protocol, a set of rules describing how to transmit data between two computers. Like any protocol, it defines the types of messages that can be sent, and the order in which they may be sent. Protocols describe the bits on the wire, how data flow. In reading this manual, it may be helpful to keep in mind that a protocol is not an API, although the two are related. The server recognizes and speaks a protocol; anything that can send it the correct combination of bytes in the right order can communicate with it. But programmers aren't generally in the business of sending bytes; that's the job of a library. Over the years, there have been a few libraries — each with its own API — that do the work of moving SQL through a TDS pipe. &odbc;, &dblib;, and &ctlib; have very different APIs, but they're all one to the server, because on the wire they speak TDS. The TDS protocol was designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984. The problem Sybase faced then still exists: There was no commonly accepted application-level protocol to transfer data between a database server and its client. To encourage the use of their product, Sybase came up with &dblib;. &dblib; provided an API to the client program, and communicated with the server. What it sent to the server took the form of a stream of bytes meant for tables of data, a Tabular Data Stream. In 1990 Sybase entered into a technology sharing agreement with Microsoft which resulted in Microsoft marketing its own SQL Server. Microsoft kept the &dblib; API and added &odbc;. (Microsoft has since added other APIs, too. It no longer supports its own &dblib; implementation.) At about the same time, Sybase introduced a more powerful successor to &dblib;, called &ctlib;, and called the pair OpenClient. &ctlib;, &dblib;, and &odbc; are APIs that — however different their programming style may be — all communicate with the server in the same way. The language they use is TDS. The TDS protocol comes in several flavors, most of which were not openly documented. If anything, it was considered to be something like a trade secret, or at least proprietary technology. The exception is TDS 5.0, used exclusively by Sybase, for which documentation is available from Sybase. History of <acronym>TDS</> Versions At first, there was One Version of TDS common to both vendors but, in keeping with the broad history of private ventures, they soon diverged. Each vendor has subsequently brought out different versions, and neither supports the other's flavor. That is to say, each vendor's client libraries use the latest version of TDS offered by that vendor. You can't reliably use Microsoft's libraries to connect to Sybase, or Sybase's libraries to connect to Microsoft. In some cases you'll get a connection, but pretty soon you'll bump into some incompatibility. Versions of the <acronym>TDS</> protocol TDS 4.2 Sybase and Microsoft The version in use at the time of the Sybase/Microsoft split. TDS 5.0 Sybase Introduced for Sybase. Because TDS 5.0 includes negotiated capabilities through which protocol features can be expanded, we are unlikely to see a new TDS version from Sybase. TDS 7.0 Microsoft Introduced for SQL Server 7.0. Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters). It also includes support for Unicode. TDS 7.1 Microsoft was 8.0 Earlier &freetds; documentation referred to versions 7, 8 and 9. Microsoft subsequently published a protocol specification document denoting 7.1 and 7.2, and one finds scattered references using that scheme elsewhere, too. For that reason, &freetds; switched to Microsoft's nomenclature. Introduced for SQL Server 2000. Includes support for big integer (64-bit int) and variant datatypes. TDS 7.2 Microsoft was 9.0 Introduced for SQL Server 2005. Includes support for varchar(max), varbinary(max), xml datatypes and MARS. History of &freetds; &freetds; was and is developed by observation and experimentation, which is to say, by trial and error. In early 1997, the only option for connecting to a Sybase server from Linux or other free systems was an aging Sybase-released version of OpenClient. Unfortunately it had a few problems. The original release was a.out-based, although Greg Thain did a great service in converting the library to ELF. Secondly, it included only the newer &ctlib; API. The older &dblib; API was missing. Brian Bruns, a Sybase DBA and originator of the &freetds; project, had some &dblib; programs he wanted to run under Linux, and thus began the &freetds; project. The original work focused on &dblib; and version 5.0 of the protocol, but quickly expanded to include a &ctlib; compatible layer and TDS version 4.2. Later support for &odbc; and TDS 7.0 and 7.1 was added. Craig Spannring wrote a Java JDBC driver which became FreeTDS/JDBC. As the project matured, it gained new participants. Frediano Ziglio greatly expanded the &odbc; driver, and continues to improve both it and the underlying TDS library. Bill Thompson wrote most of the present BCP system and added cursors to our &ctlib;. Your humble author joined the project to add documentation, and in 2002 became its maintainer. Such are the rewards for doing a good deed. There have been many other contributions. Please see the AUTHORS in the distribution for a (we hope) complete list. Current Projects, Language Bindings, and Alternatives Current Projects &freetds; consists of two projects. The &freetds; C libraries and &freetds;/ JDBC. The &freetds; C libraries support three separate APIs: &dblib;, &ctlib;, and &odbc;. Underlying these three is libtds, which handles the low-level details of the TDS protocol, such as sending, receiving, and datatype conversion. This document and the FreeTDS website are dedicated to these libraries. If Java is your game, we refer you to the jTDS project on SourceForge. It is a fork of the FreeTDS/JDBC project, by Craig Spannring, and is a free, native 100% Java implementation of a Type 4 JDBC driver. Status The libraries are portable, mature, and stable. They're expected to compile readily and normally do not crash or corrupt data. Extensive logging aids in diagnosing problems. While they do not include every feature provided by the vendors' libraries, they do faithfully implement a useful — and widely used — subset of their APIs. The &dblib; and &ctlib; APIs have been usable for several years. They have been successfully substituted for Sybase's own libraries in a variety of venues, including Perl and PHP. The ODBC driver should be fully ODBC 3.0 compliant. Basic API coverage information for all libraries may be found in this manual. It is maintained in doc/api_status.txt, included in the source distribution. For Microsoft servers, &freetds; now offers the best &dblib; for any OS on the planet (including Windows!) thanks not only to the hard work of its contributors, but also to Microsoft'sMicrosoft ceased enhancing &dblib; in 2001, advising customers to avoid using &dblib;. For Microsoft's unmaintained product, that's good advice. But if the &dblib; specification meets your needs, &freetds; permits you to keep using it with little loss (and some gain) of functionality. strategy. It is the only Win64 implementation of &dblib;, and the only Win32 implementation to support modern versions of the protocol. (SQL Server 2008 still accepts the TDS 4.2 connections that Microsoft's old library uses, but rejects BCP uploads with a spurious permission-denied message.) In addition to the core &dblib; API, &freetds; includes a full implementation of &dblib;'s bcp functions, as well as freebcp, a replacement for Sybase's bcp utility. How big is it? &freetds; has over 100,000 lines of C code, maintained by a handful of developers. Patches arrive irregularly, varying in size from one-liners to thousand-line monsters. Almost all are applied or used in some way. The mailing list has some 700 or so subscribers at this writing. Safe to say, &freetds;'s success so far lies somewhere between the Beetle and the Edsel. Who uses it? Oh, pretty much everyone. &freetds; users number in the tens of thousands. It's used by large corporations, by the U.S. federal government (e.g. Database Access Library at the National Center for Biotechnology Information) and, judging by the mailing list, by many webservers running Apache and PHP. Sybase recommends &freetds; for their EAServer product. Microsoft recommends &freetds; to their customers who want access to Microsoft SQL Server from non-Win32 clients. So do we. Languages besides C and Java You may be wondering how these libraries fit with Perl, PHP, TCL, Python, or other popular scripting languages. Most of these languages have bindings to Sybase that use either the &dblib; or &ctlib; API, for which &freetds; is intended as a drop-in replacement. For instance, Michael Peppler's DBD::Sybase works very well using &freetds; to access Sybase or Microsoft SQL Servers. PHP has options for sybase (&dblib;) and sybase-ct (&ctlib;) APIs. Alternatives Should &freetds; not suit your needs, some alternatives Sybase OpenClient In the time since &freetds; was started, Sybase (as well as most major DBMS vendors) has released its database for the Intel GNU/Linux platform. The good: it is a solid product and supports TDS 4.2 and TDS 5.0. The bad: it doesn't support TDS 7.0 or Linux/*BSD on non-Intel platforms. The ugly: Microsoft broke date handling for big endian Sybase clients. Depending on platform, it may cost something. &odbc; bridge products They use the &odbc; driver on the NT box where your SQL Server runs so you'll never have trouble with new protocols and the like. On the downside, they can be costly and may be inefficient. We know of EasySoft ODBC-ODBC Bridge from EasySoft, Universal Data Access Driver from OpenLink Software, SequeLink from Merant, and &odbc; Router from August Software Corporation. Inline &odbc; driver Based on libtds, this is a native &odbc; driver for i386 *nix. It is free in price, but comes only as a binary at the present time. DBD::Proxy We have no direct experience with this Perl-only option. It has the same caveats as an &odbc; bridge except it's free. Build &freetds; If you build it they will come. The <acronym>GNU</> World &freetds; uses GNU Autoconf, Automake, and libtool Versions used for this release autoconf (GNU Autoconf) 2.65 automake (GNU automake) 1.11.1 ltmain.sh (GNU libtool) 2.2.6b to increase portability. For many people, the preceding sentence says it all (good or bad). If you're familiar with the GNU system, you can probably just download the tarball and get away with scanning the README impatiently and then following your instincts. Because everyone is a beginner once and no one is an expert at everything, we'll try to explain things in plain English where possible, and to define our terms as we go along. If the following nevertheless reads like gibberish, you might very well want to use something prepackaged (see Alternatives). If it reads like a vaguely intelligible alien script that might yield to intensive research, we've included links to some of the usual suspects at the end of this chapter. If it reads like a bad explanation of something you could explain better, please send us your version! What to build: Packages, Tarballs, and the <productname>CVS</productname> repository The latest &freetds; package is always available from iBiblio and its mirrors. Code changes by the developers are immediately available in the CVS repository. If you've run into a problem, you may want to check out from CVS to see if it's fixed there. No password is needed to obtain the current CVS copy of FreeTDS; you need only have a CVS client installed on your machine. Then: $ cvs -d:pserver:anonymous@freetds.cvs.sourceforge.net:/cvsroot/freetds login $ cvs -z3 -d:pserver:anonymous@freetds.cvs.sourceforge.net:/cvsroot/freetds checkout -P freetds Password: $ For those behind firewalls or otherwise unable to access CVS, nightly snapshots of CVS are rolled up into tarballs for your convenience. They can be downloaded from ibiblio.org. Tarballs are generated around 3am EST (GMT-5). In general, the CVS HEAD revision (the basis of the current nightly snapshot) works better and has more functionality than the release version. Bugs sometimes persist in the release version but are usually fixed in short order (once identified) in CVS HEAD. As with any project of this sort, if you want to use the CVS HEAD revision, it's a good idea to join the mailing list. How to build: Configure and make If you've built other GNU projects, building &freetds; is a fairly straightforward process. We have a terse and verbose description. &freetds; is known to build with GNU and BSD make. If you encounter a large number of build errors, and your operating system's make is not GNU make (as is the case on most non-GNU/Linux systems), you may wish to install GNU make from ftp.gnu.org. For Experts $ ./configure --prefix=/usr/local $ make $ su root Password: $ make install Building from CVS is described in the file INSTALL.CVS. For Everyone Else (&freetds; for Dummies?) The GNU development system can generate code for a wide variety of hardware architectures and operating systems, virtually all of which can run FreeTDS in consequence. The work of building and installing the FreeTDS libraries begins with the command configure, which generates the Makefile that governs how the code is compiled, linked, and installed. Once you've configured the project, make will manage the rest of the build. ODBC Preparation If you intend to build the FreeTDS ODBC driver — and want to use a Driver Manager (DM), as most people do — install the Driver Manager before configuring FreeTDS. configure will detect the the DM and use its header (.h) files for ODBC constants and such. If your DM is installed in an unusual directory, you may have to provide the directory name as a parameter to configure. FreeTDS doesn't require a DM. You can build the ODBC driver without one, as long as you have the requisite header files: sql.h, sqlext.h and sqltypes.h. These can be taken from either the iODBC or UnixODBC distributions. Put them wherever you like (e.g., /usr/local/include). Because FreeTDS won't detect your (missing) DM, it won't automatically build the ODBC driver, so you'll have to tell configure what to do and where to look. Cf. The simplest form of running configure is: $ ./configure and sometimes that's enough. configure accepts command-line arguments, too, and you may need to provide some, depending on your environment. There are a few optional arguments to configure that may be important to you. For a complete list, see configure --help. <command>configure</> options Directories and TDS version install architecture-independent files in PREFIX. When you run make install, libraries will be placed in PREFIX/lib, executables in PREFIX/bin, and so on. The default is /usr/local if this argument is not passed to configure. read-only single-machine data in DIR The default is PREFIX/etc (PREFIX being the value of , above) if this argument is not passed to configure. Specifies the location of the iconv library to use. configure will search for libiconv in the usual places; use if it's unsuccessful (assuming you want to use iconv, of course). Overridden by , below. Specifies the default TDS version. (There are a couple of ways to set the TDS version at run-time. This parameter takes effect if no run-time settings are provided.) Acceptable values of VER are 4.2, 4.6, 5.0, 7.0, 7.1 and 7.2. The default is 5.0 if this argument is not passed to configure. ODBC Driver Managers Specify directory of iODBC or unixODBC support, and use it as the Driver Manager. As of version 0.62, the ODBC Driver Manager is detected by configure, so use this parameter only if yours is installed in a nonstandard path. (Requires iODBC or unixODBC to have already been installed.) If you're building the ODBC driver and not using a Driver Manager, use this option to indicate the location of the .h files. configure will not cause the ODBC driver to be built unless this option is used or a DM is detected/specified. Things you can turn off Do not attempt to detect ODBC, and do not build the ODBC driver. In case you don't care about ODBC. Do not attempt to build applications like tsql. Do not attempt to build server stuff. Do not attempt to build pool stuff. By default, configure will search your system for an iconv library for use with Microsoft servers (because TDS 7.0 employs Unicode). This switch prevents that search. If no iconv library is used, &freetds; relies on its built-in iconv emulation, which is capable of converting ISO-8859-1 to UCS-2, sufficient for many applications. Force &freetds; not to use threadsafe versions of functions such as gethostbyname_r() where available. Rely instead on the older and non-threadsafe ones such as gethostbyname(). configure tests some of these functions. If the tests are successful, &freetds; will use threadsafe functions throughout. Threadsafe operation has been tested on Linux, FreeBSD, and HP-UX. It should work on Solaris, Tru64, and (reportedly) IRIX. Not expected to work on non-unixy systems. It is a good idea to enable threadsafe operation if you configure Apache with multi-threading support. Debug-mode compiles are enabled by default, and will remain so at least until version 1.0. You can speed things up ever so slightly by disabling it. Things you can turn on Enable Microsoft behavior in the &dblib; API where it diverges from Sybase's. Use this option if you are replacing Microsoft's libraries with &freetds; This option specifies default behavior. Programs can change the default at compile time by defining MSDBLIB or SYBDBLIB (for Microsoft or Sybase behavior, respectively). Enable close compatibility with Sybase's ABI, at the expense of other features. Currently, this enables the generation of a dbopen() entry point in &dblib;, which may clash with the DBM function with the same name. Absolutely not required for use with other free software. Enable Kerberos support. With Kerberos you can connect to server using your stored Kerberos ticket. Obviously requires Kerberos be configured on the machine. Enable SSPI support. SSPI is a Micrsoft library that allows you to use your current logged-in account for authentication. With this option enabled, FreeTDS supports "trusted logins" for Win32/64, just as Microsoft's own implementations do. Intended for debugging purposes, enables certain internal consistency checks against problems like memory corruption and buffer exhaustion. Enable some code still in development. Should be used only by a developer or a brave user :) SSL support Enable SSL using GnuTLS. Use version 1.2.3 or newer. Enable SSL using OpenSSL. Unlike &freetds;, OpenSSL does not use the LGPL. Please read the OpenSSL license before distributing binaries compiled with this option. <command>Make</> Now you're ready to build. Follow these easy steps. Download the tarball and unpack it. Alternatively, get the latest build from CVS CVS users will need the GNU autotools: Autoconf, Automake, and libtool. . Change to the freetds directory. run ./configure with any options you need. make; make install; make clean You normally need to be root to make install, unless you used the With any luck, you've built and installed the &freetds; libraries. Two bits of advice, if you like to keep things tidy and keep track of what you did. Create a file to hold your configure options called, say, .build_options. Create a build directory for the binaries, and invoke ../configure $(cat ../.build_options). This approach lets you remove the binaries at any time and rebuild from scratch using the same options. OS-specific Issues If you've recently built and installed &freetds; and noticed steps peculiar to your OS, we'll happily include your comments here. One thing that can be said, if it's not too obvious: check with your vendor or favorite download site. &freetds; is routinely rolled up into OS install packages. We know of packages for Debian, Red Hat, FreeBSD, and NetBSD. The installation through the package management systems in these environments may well reduce your work to simply make install. Win32 and Win64 Building for Windows using Microsoft's compiler is supported via the NMakefile included in the distribution. Set up the command-line build environment per Microsoft's instructions, and run e.g. $ nmake -fNmakefile -nologo apps PLATFORM=win32 CONFIGURATION=debug The assumed availability of standard .h files is captured in win32/config.h. If you encounter difficulties related to missing include files, examine the indicated file for an include guard, and compare it to win32/config.h. For example, you get an error like this: Missing include file in Windows … asprintf.c include\tds_sysdep_private.h(239) : fatal error C1083: Cannot open include file: 'inttypes.h': No such file or directory src/replacements/asprintf.c includes include/tds_sysdep_private.h, where you find 238 #ifdef HAVE_INTTYPES_H 239 #include <inttypes.h> 240 #endif and, sure enough, asprintf.c earlier includes win32/config.h, which says 91 /* Define to 1 if you have the <inttypes.h> header file. */ 92 #define HAVE_INTTYPES_H 1 Which shouldn't be defined unless inttypes.h exists on your system. Let's review: config.h has preprocessor definitions that (try to) describe the build environment to the compiler. In this example, it got one wrong, causing the compiler to look for a file that isn't present, creating the error. The solution is not to change the &freetds; source code, but merely to comment out line 92 in config.h. Perhaps you're shaking your head at such an old school approach. Over the years, Microsoft's proprietary project-configuration files have proved difficult to support. Every version is different, and there are a great many versions in use in the wild at any one time. As the project changes, it becomes impossible to maintain these kinds of files. For Windows applications that use &freetds; the hard-won wisdom is just use the NMakefile, please, thanks! If you like a visual environment and visual debugging, no problem: Microsoft's tools support Makefile projects. The author has direct knowledge of developers for whom that arrangement works quite well. Other ways to build under Windows® Visual Studio. Project files are included in the win32 directory. See above note for why these might be out of date. Dev-C++ MingW gcc under cygwin. The Borland Builder 6.0 compiler is also reported to work, but requires some tweaking of the #include statements. We would apply any patches that make this work cleanly. From the Department of Double Emulation: &freetds; builds as a .dll under WINE and as a .a under Interix. See the mailing list archives (second half of 2003) for details. VMS® &freetds; will probably build and run on most versions of OpenVMS Alpha 7.0 and later with DEC/Compaq C 6.0 or later. Other prerequisites: gunzip vmstar MMS or MMK Build Instructions Decompress and unpack the source archive using gunzip and vmstar. If you are untarring on an ODS-5 disk, you should use the /ODS2 or -o option to create universally VMS-friendly filenames; otherwise the build will fail to locate some files. Set default to the top-level source directory and run the configuration script: $ @[.vms]configure This creates a descrip.mms in the top-level source directory which you may execute by simply running MMS (if you have the Module Management System that is part of DECset) or MMK (a freeware MMS alternative available from www.madgoat.com). Further information can be found in the in the source distribution. OS X® As of this writing ($Date: 2011/05/21 18:31:37 $), the regular distribution compiles on OS X. Alternative build procedure On 11 March 2004, Dmitri Fedortchenko offered the following approach, using a local libtool. It is included here as a source of clues, in case you encounter trouble. Installing with libtool 1.5.2 Install the latest libtool from GNU into /usr/local, so as not to interfere with the Apple-original. Make sure /usr/local/bin is in your PATH and /usr/local/lib is in your LIBRARY_PATH. Go to the &freetds; source directory and generate the Makefiles $ ./configure --disable-libiconv --disable-odbc Overwrite FreeTDS's libtool with a symbolic link to your (better) one If you run configure again, you'll need to perform this step again, because libtool will have been regenerated in its fossilized state. $ ln -sf /usr/local/bin/libtool To check that you've done everything correctly up to this point, $ ./libtool --version libtool should report version 1.5.2 (or whatever version you downloaded, and not 1.4). And finally, of course $ make && make install ]]> AIX® AIX® can induce linker indigestion. libtool doesn't always understand that a .a file can be a shared library. One solution is to build only static libraries with the Another problem seems to be that the linker isn't asked to pull in all the requisite libraries. Cf. this helpful mailing list message. GNU/Linux distributions that use RPMs You may find it convenient to make an RPM from the source distribution, in which case you'll be glad to know it is easily done: $ rpmbuild -ta freetds-0.63RC9.tar.gz Install &freetds; If you install it they will stay? Confusing terminology Configuring and installing don't have absolute, context-free definitions. In some circles, we install a product and then configure it. In the GNU world, we configure the package (generate the Makefiles), then we make install the package. In the case of a library package such as &freetds; To install the package is to copy the files the application developer will use to their canonical locations: header files to include, libraries to the lib, documentation and man pages share. Install targets were specified during the build process as arguments to configure, covered in the last chapter. For lack of a better term, this chapter describes installing the product. Put more specifically, once we're done with the package manager, we still have to tell &freetds; about your database servers, and we still have to tell your client programs about &freetds;. The local environment After &freetds; has been built and installed, it still doesn't know where your servers are or what particular version of Sybase or Microsoft software each one is using. The purpose of this section is to explain how to describe your servernames to &freetds;. &freetds; looks up your server's attributes in &freetdsconf;. Some of the attributes can be overridden by environment variables. One of the more important (and arcane) settings is the TDS protocol version, described next. Choosing a <acronym>TDS</> protocol version The TDS protocol version is probably something you'd rather not know even existed, much less something you'd have to choose. But there's not that much to it, really. Unless you run into an incompatibility, you're best off running with the highest protocol version supported by your server. That's what the vendors' own products do, which is why when you read the Sybase or Microsoft documentation you find no mention of TDS versions. Versions of the <acronym>TDS</> Protocol, by Product Product TDS Version Comment Sybase before System 10, Microsoft SQL Server 6.x 4.2 Still works with all products, subject to its limitations. Sybase System 10 and above 5.0 Still the most current protocol used by Sybase. Sybase System SQL Anywhere 5.0 only Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0. Microsoft SQL Server 7.0 7.0 Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters), and support for Unicode. Microsoft SQL Server 2000 7.1 Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used. Microsoft SQL Server 2005 7.2 Includes support for varchar(max), varbinary(max), xml datatypes and MARSMultiple Active Result Sets. &freetds; does not support MARS.. Microsoft SQL Server 2008 7.2 (unchanged)
For best results, use the highest version of the protocol supported by your server. If you encounter problems, try a lower version. If that works, though, please report it to the mailing list! Want to help? Try out the auto-protocol feature. &freetds; has experimental support for iteratively trying protocol connections until it finds one the server accepts. This is suitable when query responses are non-trivial (because the tiny delay in connecting is thus insignificant). Try setting your TDS version to 0 and report your results. TDS 4.2 has limitations ASCII only, of course. RPC is not supported. BCP is not supported. varchar fields are limited to 255 characters. If your table defines longer fields, they'll be truncated. dynamic queries (also called prepared statements) are not supported. The protocol version may also affect how database servers interpret commands. For example, Microsoft SQL Server 2000 is known to behave differently with versions 4.2 and 7.0. Version 7.0 is recommended for compatibility with SQL Server tools. <replaceable>servername</> Lookup &freetds; converts the servername to an IP address by following the steps below, stopping when it succeeds. Name lookup sequence <footnote><para>This description applies to &dblib; and &ctlib;. ODBC lookup is different.</para></footnote> Find servername in &freetdsconf;. If a section with that name exists, use the hostname, port, and TDS version specified therein. Attempt to convert servername to an IP address with inet_addr(3). Request name-lookup from the operating system via gethostbyname(3) or similar. If the TDS version and port are not read from &freetdsconf;, they are derived from the compiled-in defaults and overridden by applicable environment variables. As you can see, if most of your servers use the same TDS version and answer to the default port, then you don't need to list them all in &freetdsconf;. You can simply compile in the right defaults — or set the TDSPORT and TDSVER environment variables — and rely on DNS for name resolution. The &freetdsconf; file What it does Just as DNS defines hostnames for network addresses, &freetdsconf; uses a servername to define the properties of your server. In general, the servername is arbitrary and local; it's used only by your client programs to tell &freetds; which server to connect to. You can choose any name you like. Sybase SQL Anywhere (a/k/a Sybase ASA), however, is fussy. Unless you use the ASA Database property, you must use the database's name as your servername. Otherwise, the server will refuse your connection. In particular, &freetds; needs to know: Primary Server Properties Hostname or IP address of the server Port number or Instance name (not both) TDS protocol version &freetds; also supports an older configuration file format, known as the interfaces file. Use &freetdsconf; unless interfaces is needed for your situation. It is easier to read, and it is where all the new options are being added. &freetds; looks for &freetdsconf; first, falling back on interfaces only if &freetdsconf; is not found. Should you need it, more information about interfaces can be found in the Appendix. Where it goes The default location of &freetdsconf; is determined by the --sysconfdir option of configure. If you don't specify anything, configure's default sysconfdir is /usr/local/etc. tsql -C reports the sysconfdir to let you confirm it. In addition, &freetds; will look for a file .freetds.conf in the user's home directory (${HOME}/.freetds.conf). The actual name and location of &freetdsconf; may be specified by the environment variable FREETDS (or FREETDSCONF, same effect). See Environment Variables, below. &freetds; reads the user's ${HOME}/.freetds.conf before resorting to the system-wide sysconfdir/&freetdsconf;. The file used is the first one that is readable and contains a section for the server. What it looks like The following information is also provided in the &freetdsconf; manual page, cf. man freetds.conf. The &freetdsconf; file format is similar to that of Samba's modified win.ini. It is composed of two types of sections: one [global] section, and a [servername] section for each servername. Settings in the [global] section affect all servernames, but can be overridden in a [servername] section. For example A &freetdsconf; file example [global] tds version = 4.2 [myserver] host = ntbox.mydomain.com port = 1433 [myserver2] host = unixbox.mydomain.com port = 4000 tds version = 5.0 [myserver3] host = instancebox.mydomain.com instance = foo tds version = 7.1 In this example, the default TDS version for all servernames is set to 4.2. It is then overridden for myserver2 (a Sybase server) which uses 5.0, and myserver3 (a MSSQL 2000 server) which uses 7.1. Usually, it is sufficient to state just the server's hostname and TDS protocol version. Everything else can be inferred, unless your setup (or your server's) strays from the defaults. Some people seem to feel safer using the IP address for the server, rather than its name. We don't recommend you do that. Use the name, and benefit from the inherent advantages. That's why DNS was invented in the first place, you know. It bears mentioning here that prior versions of &freetds; were quite fussy about domain logins, forcing users to make explicit per-server entries in &freetdsconf;. That is no longer the case. If the username has the form DOMAIN\username, &freetds; will automatically use a domain login. &freetdsconf; settings Name Possible Values Default Meaning tds version 4.2, 5.0, 7.0, 7.1, 7.2, auto --with-tdsver value (5.0 if unspecified) Overridden by TDSVER. The TDS protocol version to use when connecting. auto tells &freetds; to use an autodetection (trial-and-error) algorithm to choose the protocol version. host host name or IP address none The host that the servername is running on. port any valid port Product Version Default Port Sybase SQL Server prior to System 10 1433 Sybase SQL Server 10 and up 5000 Sybase SQL Anywhere 7 2638 Microsoft SQL Server all 1433 The port number that the servername is listening to. Please note: The "defaults" to the left are the server's default settings. &freetds; chooses its default port based on the TDS protocol version: 5000 for TDS 5.0, and 1433 for everything else. Mutually exclusive with instance, below. Overridden by TDSPORT. instance instance name none Name of Microsoft SQL Server instance to connect to. The port will be detected automatically. Mutually exclusive with port, above. Requires UDP connection to port 1434 on the server. ASA database valid database name servername [section] name Specifies the name of the default database when connecting to an ASA server. A TDS 5.0 login packet has a field called lservname. For most TDS servers, lservname is a user-defined string with no inherent meaning. ASA servers, however, requires that lservname contain a valid database name, and sets that as the default database for the connection. FreeTDS normally fills lservname with the [section] text.. This entry instead sets the database name independently of the [section] name. initial block size multiple of 512 512 Specifies the maximum size of a protocol block. Don't mess with unless you know what you are doing. dump file any valid file name none Overridden by TDSDUMP. Specifies the location of a tds dump file and turns on logging dump file append yes/no no Appends dump file instead of overwriting it. Useful for debugging when many processes are active. timeout 0- none Sets period to wait for response of query before timing out. connect timeout 0- none Sets period to wait for response from connect before timing out. emulate little endian yes/no no Forces big endian machines (Sparc, PPC, PARISC) to act as little endian to communicate with MS Servers. Set automatically for TDS 7.0 or above on big endian hosts client charset any valid iconv character set ISO-8859-1Valid for ISO 8859-1 character set. See Localization and TDS 7.0 for more information. Makes &freetds; use iconv to convert to and from the specified character set from UCS-2 in TDS 7.0 or above. &freetds; uses iconv to convert all character data, so there's no need to match the server's charset to insert any characters the server supports. text size 0 to 4,294,967,295 4,294,967,295 default value of TEXTSIZE, in bytes. For text and image datatypes, sets the maximum width of any returned column. Cf. set TEXTSIZE in the T-SQL documentation for your server. debug flags Any number even in hex or octal notation 0x4fff Sets granularity of logging. A bitmask. See table below for specification. encryption off/request/required off Specify if encryption is desidered. Supported for Microsoft servers. off disables encryption (only if needed); request means use if available; required means create and allow encrypted connections only. enable gssapi delegation on/off off Enable delegation flag using Kerberos.
Overrides</> <para>Many settings in &freetdsconf; can be overridden by <link linkend="envvar">environment variables</link>.</para> <para>The servername can also be decorated adding the port or instance name using <link linkend="PortOverride">port override syntax</link>.</para> </sect3> <sect3> <title>Controlling log details</> <abstract><para>The logging capability has helped solve innumerable cases, some trivial and some very low-level bugs. Sometimes a developer needs very detailed information about one function, whereas someone else may interested only in whether or not a particular function is called, or even want to see only the SQL that was transmitted to the server.</para> </abstract> <para>The log's granularity can be controlled with the <literal>debug flags</> entry. The default value (<literal>4FFF</> hex) gives a level of detail that is useful for resolving problems via the mailing list.</para> <table id="tab.freetds.conf.debugflags"> <title>Valid bitmask values for <literal>debug flags</> entry in &freetdsconf; Value Meaning 0x80 function trace and info 0x40 information level 2 0x20 information level 1 0x10 network 0x08 warning 0x04 error 0x02 severe error 0x1000 show pid 0x2000 show time 0x4000 show source level info (source file and line) 0x8000 thread id (not implemented) For more about the wonderful world of &freetds; logs, see Logging. Deprecated options</> <para>The following options have long been deprecated.</para> <itemizedlist id="lst.freetds.conf.deprecated" spacing="compact"> <title>Deprecated &freetdsconf; settings try server login try domain login nt domain cross domain login debug level The <filename>locales.conf</filename> file What it does For an English-speaking American, not much. &freetds; originated in the United States, and uses U.S. conventions if no locales.conf is present. The locales.conf provided with the installation also reflects these conventions. locales.conf will probably be dropped from FreeTDS one day. Its only real purpose now is to control the format of date strings. The Right Way™ to deduce the appropriate default date format is from the application's locale settings, while allowing an override in &freetdsconf;. That's the direction we're headed. If your purpose is to affect the client charset description, use &freetdsconf; instead. Information on locales and locale strings is easily (even too easily!) found on the Internet, or see man locale for your system. &freetds; will examine its environment for a LOCALE string. If it finds one, it will look it up in locales.conf to find your preferred settings. If it fails to find one, it will use its defaults. Where it goes Like &freetdsconf;, the location of locales.conf is determined by the value of What it looks like The format of locales.conf is similar to that of &freetdsconf;. There is a [default] section, and a section for each locale. locales.conf controls three settings date format This entry will be passed (almost) literally to strftime(3) to convert dates to strings. For the most part, see you system documentation for strftime(3) (man 3 strftime). You will see there though that strftime(3) has no provision for milliseconds. The locales.conf format string uses %z for milliseconds. If your system's strftime(3) does employ %z for its own use, it will not be given that chance by &freetds;. &freetds; will consume the %z for its milliseconds needs, and will not pass it on to strftime(3). language The language that will be used for error/status messages from the server. A SQL Server client can specify a language for such messages at login time. &freetds; issues a few messages of its own. Messages from the server are called messages; those from the client library (i.e., from &freetds;) are called error messages. &freetds;-issued messages are not affected by locales.conf. charset Indicates to the server what character set should be used for communicating with the client. Environment variables What they're for You can use environment variables to Override some of the settings in &freetds;'s configuration file. Advertise the location of the &freetds; libraries to programs that want them. Control how logging is done. This section covers the first two items. For information about environment variables that control logging, see Logging In a typical system, no environment variables need be used. They're sometimes handy for testing, for instance setting TDSVER to check if a connection problem is due to using the wrong protocol version. And they have other uses, described below. But they're just knobs, so don't feel you have to turn every one, unless you're the sort that likes turning knobs. Environment Variables FREETDS may be used to specify the name and location of the &freetdsconf; file. In prior versions of &freetds; this variable was known as FREETDSCONF. TDSVER governs the version of the TDS protocol used to connect to your server. For a given server, &freetds; inspects four sources in the following order to determine which TDS protocol version to use, using the first one it finds. The value specified in TDSVER A &freetdsconf; file entry (see below) The interfaces file entry (see below) The TDSPORT specifies a TCP port number at which the servername is listening. It overrides the default port (1433 for TDS 4.2/7.0/7.1/7.2, 4000 for TDS 5.0) as well as any port specified in the &freetdsconf; file. SYBASE points to the &freetds; run-time directory. Use of this variable originated with Sybase (the company), and many programs still rely on SYBASE to discover the location of the SYBASE libraries. The primary use of SYBASE is to advertise the location of the &freetds; libraries. A secondary use is to point to the location of the interfaces file (if used, see the Appendix), which some programs examine directly. TDSQUERY DSQUERY provides a server name to connect to if none is specified by the application. DSQUERY is the historical Sybase name for this variable. TDSHOST overrides the host specified in the &freetdsconf;. Setting environment variables Of course, each shell is a little different. In the Bourne shell and variants such as ksh and bash, to set SYBASE and TDSVER do: $ export SYBASE=/usr/local/freetds # (or your favorite directory) $ export TDSVER=4.2 In csh: $ setenv SYBASE /usr/local/freetds $ setenv TDSVER 4.2 Checking your work When you're done, you should see something very like this: $ ls $SYBASE etc include interfaces lib Port override syntax The port to which to connect can be overridden using a &freetds; extended syntax. A port may be appended to the servername in the form servername:port. &freetds; will attempt to connect to specified port. Please note port must be a number; a service name is not supported. If you specify servername\instance as servername during login, &freetds; will attempt to connect to specified instance. Only Microsoft SQL Server instances are supported. (This server feature was introduced with SQL Server 2000.) Note that other &freetdsconf; properties still apply. For the technically curious: each SQL Server instance appears on the network as a server listening at a port. The old way — and it still works — is to designate each instance in &freetdsconf; as a separate server. The new named instance notation, if we can call it that, instead uses the server to discover the port. The library sends a UDP packet containing the instance name to the server at a well known port, port 1434. The server responds with a port number. &freetds; then uses that number to connect in the usual way. Confirm the installation We want to make sure that when your application requests a connection to your server, it actually works. In detail, we want to know: &freetds; can find and read &freetdsconf; servername exists in &freetdsconf; a host property exists for servername host can be resolved to a network address the server is listening to the port or named instance the user can log in to the server Each of the above can be confirmed independently with tsql. Once you're sure you can connect and log in, you can run the unit tests to see if the software works as promised. <application>tsql</application> The tsql utility is provided as part of FreeTDS expressly for troubleshooting. tsql is superficially similar to an isql, but uses libtds directly, bypassing the client libraries (e.g., &dblib;). It can also report where it looks for &freetdsconf; and other compile-time settings (with tsql -C). Show compile-time settings with <command>tsql</> $ tsql -C Password: locale is "C" locale charset is "646" Compile-time settings (established with the "configure" script): Version: freetds &version; freetds.conf directory: /usr/local/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: no TDS version: 7.0 iODBC: no unixodbc: no For details on the use of tsql, consult its man page. <replaceable>servername</> Lookup If all goes well, the first time you fire up tsql it connects and you can issue your first query. More often, though, the result is less joyous. Listed below for your troubleshooting pleasure are a variety of servername lookup failures and their corresponding messages. When servername cannot be converted to an address, up to two messages may result. Successful conversion (by any means) never produces an error message. Failure to find <replaceable>servername</> in &freetdsconf; $ tsql -S nobox -U sa Password: locale is "C" locale charset is "646" Password: Error 20012 (severity 2): Server name not found in configuration files. Error 20013 (severity 2): Unknown host machine name. There was a problem connecting to the server $ host nobox Host not found. In the above case message 20012 indicates nobox was not found in &freetdsconf;. The library then treated nobox as a network hostname but found it also not to be valid per DNS, leading to message 20013. If servername is found in the configuration files, but refers to an invalid hostname, only message 20013 is returned. Failure to resolve hostname for <replaceable>servername</> $ tsql -S nonesuch -U sa Password: locale is "C" locale charset is "646" Error 20013 (severity 2): Unknown host machine name. There was a problem connecting to the server Unfortunately, the host machine name (the right side of the host line in &freetdsconf;) isn't mentioned in the error message. Fortunately, this kind of setup problem is rarely encountered by users. Connecting to the Server If name lookup succeeds, &freetds; next attempts to connect to the server. To connect means to form at TCP connection by calling connect(2). A valid connection must exist before any information can be exchanged with the server. Specifically, we need a connection before we can log in. A few things can go wrong at this point. The address returned by DNS may not be that of the machine hosting the server, or indeed of any machine! The machine may be down. The server may not be running. The server may be running but not listening to the port &freetds; is attempting to connect to. In rare cases, both ends are correctly configured, but a firewall stands in the way. If no server accepts the connection, no connection can be established. It's difficult to know why, and the message is consequently vague. Failing to connect with tsql $ tsql -S emforester -U sa #only connect? Password: Msg 20009, Level 9, State -1, Server OpenClient, Line -1 Unable to connect: Adaptive Server is unavailable or does not exist There was a problem connecting to the server If you get message 20009, remember you haven't connected to the machine. It's a configuration or network issue, not a protocol failure. Verify the server is up, has the name and IP address &freetds; is using, and is listening to the configured port. Named instances provide another way for connections to fail. You can verify the instance name and the port the server is using with tsql -L. Getting instance information with tsql $ tsql -LH servername locale is "C" locale charset is "646" ServerName TITAN InstanceName MSSQLSERVER IsClustered No Version 8.00.194 tcp 1433 np \\TITAN\pipe\sql\query servername could be configured to use instance MSSQLSERVER or port 1433. After a valid connection is formed, &freetds; sends a login packet. The TDS protocol provides no way to interrogate the server for its TDS version. If you specify the wrong one, you'll get an error. Using the wrong protocol for the server $ tsql -S servername Password: Msg 20017, Level 9, State -1, Server OpenClient, Line -1 Unexpected EOF from the server Msg 20002, Level 9, State -1, Server OpenClient, Line -1 Adaptive Server connection failed There was a problem connecting to the server Unexpected EOF from the server seems to be a fairly common message when the wrong TDS version is used. Note that there's no complaint about the login. If the right TDS version is used, the server will accept the login packet and examine its contents to authenticate the user. If there's a problem, the server will say so. This is the first time we're receiving a message from the server. If you'd like to help the project and want to so something fairly easy but still useful, modify tsql to distinguish clearly between errors returned by the library, and those returned by the server. Errors should be marked error and don't return state or a line number, but can contain an error code (and message) from the operating system. Login failure $ tsql -S servername -U notme Password: Msg 18456, Level 14, State 1, Server [servername], Line 0 Login failed for user 'notme'. Msg 20002, Level 9, State -1, Server OpenClient, Line -1 Adaptive Server connection failed There was a problem connecting to the server Bypassing &freetdsconf;: tsql -H hostname -p port -U username -Ppassword -C Keep in mind that the TDS protocol version normally comes from &freetdsconf;. When using tsql this way, the library uses the compiled-in default (set by the configure script). If that's not what you want, override it using the TDSVER environment variable. Connect with <command>tsql</> using a hostname and port number $ TDSVER=7.0 tsql -H hillary -p 4100 -U sa Password: 1> For details on tsql, see the its man page. <application>Unit Tests</application> The source code directory of each &freetds; library includes a unittests directory. $ ls -d -1 src/*/unittests src/ctlib/unittests src/dblib/unittests src/odbc/unittests src/tds/unittests The unit tests rely on the PWD file in root of the FreeTDS source tree. PWD holds a username, password, servername, and database to be used for the unit tests. We try to make sure to leave nothing behind: any data and objects created are either temporary or removed at the end of the test. The tests should all work, subject to disclaimers in the directory's README. To invoke the tests, edit the PWD file and issue the command make check. In order to execute all tests successfully, you must indicate a working, available servername in PWD. Some tests require permission to create stored procedures on server. To complete successfully, the ODBC tests require some additional setup. In your PWD file, add a SRV entry specifying the DSN entry for your odbc.ini. The ODBC tests all build their own odbc.ini and try to redirect the Driver Manager to it, however this functionality is very DM dependent and may well fail unless you have either iODBC or unixODBC. The PWD provided by &freetds; includes usernames and passwords that probably don't exist on your server. Preparing ODBC Background and Terminology To connect to a database server, a library such as &freetds; needs some information about the connection. By server, which IP address and port is do you mean? Which user is requesting the connection, and what authentication does he offer? Every database library needs a way to capture and convey that information. ODBC was conceived as a general interface definition, not tied to any particular database or access library. For that reason, ODBC also needs to know which driver to use with a given server. The original ODBC solution to this conundrum employed the odbc.ini file. odbc.ini stored information about a server, known generically as a Data Source Name (DSN). ODBC applications connected to the server by calling the function SQLConnect(DSN, UID, PWD), where DSN is the Data Source Name entry in odbc.ini, UID is the username, and PWD the password. Any and all information about the DSN was kept in odbc.ini. And all was right with the world. The ODBC 3.0 specification introduced a new function: SQLDriverConnect. The connection attributes are provided as a single argument, a string of concatenated name-value pairs. SQLDriverConnect subsumed the functionality of SQLConnect, in that the name-value pair string allowed the caller to pass — in addition the the original DSN, UID, and PWD — any other parameters the driver could accept. Moreover, the application can specify which driver to use. In effect, it became possible to specify the entire set of DSN properties as parameters to SQLDriverConnect, obviating the need for odbc.ini. This led to the use of the so-called DSN-less configuration, a setup with no odbc.ini. But FreeTDS did not start out as an ODBC driver (remember &dblib; and &ctlib;), and has always had its own way to store server properties: &freetdsconf;. When Brian added the &freetds; ODBC driver, he began by supporting the old SQLConnect, using odbc.ini to describe the DSN. That choice complied with the expectations of the Driver Managers, and minimized the amount of duplicated information in the configuration files. But it can be a little confusing, too, because odbc.ini in effect points to &freetdsconf;. We call this configuration ODBC-combined, because it supports all three FreeTDS libraries. As progress on the the &freetds; ODBC library progressed, the driver was made able to read the connection attributes directly from odbc.ini, rather than leaning on &freetdsconf;. For installations that don't need &dblib; and &ctlib;, this ODBC-only setup is simpler. More recently, SQLDriverConnect was added to &freetds;. As described above, this function allows the application to specify connection attributes with reference to either, or neither, configuration file. It's your choice. In making that choice, keep the following terms clear in your mind: Important &freetds; ODBC terms SERVERNAME specifies the [servername] entry in &freetdsconf;. SERVER specifies the real server i.e., the TCP/IP name of the machine hosting the database server. DSN Driver In your connection string, you can decide to use a DSN entry in odbc.ini using the DSN attribute, or to specify the driver you want with the Driver attribute. In sum, &freetds; supports three ODBC three choices: ODBC configuration choices DSN-less No connection information is specified in odbc.ini. Advantageous if you're using more of &freetds; than just the ODBC driver. ODBC-only All connection information is specified in odbc.ini, without the need for &freetdsconf;. This is the traditional ODBC setup. ODBC-combined Connection information maintained in &freetdsconf;. odbc.ini contains DSN entries that refer to servernames in &freetdsconf;. Library or Driver? What's a library and what's a driver? Technically, they're the same thing: bodies of subroutines whose names are exported to a linker (static or runtime). By convention, a library is used directly by an application, whose programmer will require documentation and header files. A driver, by contrast, is defined by a binary API and is used in some kind of framework, hence printer driver and video driver. An ODBC driver is a hybrid. For the most part, an application relies on a driver manager to define manifest constants, and links to the DM's library. But because the ODBC specification leaves behavior up to the driver, the application is forced to include the driver's header files, too, to exploit driver-specific functions. Connection attributes The following table defines all possible ODBC connection attributes for the FreeTDS ODBC driver. Which ones you'll need depends on how you set yourself up. They may appear in your connection string, or in odbc.ini. Connection attributes used only in connection strings Name Possible Values Default Meaning DSN A valid DSN entry none The DSN to which FreeTDS should connect. FreeTDS will search odbc.ini for entry. It lets you specify a connection as for SQLConnect, but using SQLDriverConnect. Do not use Servername and DSN together. UID Any valid username none The username to be used when connecting. To use domain authentication, specify the domain using the format domain\username. PWD Any empty The password to be used when connecting. WSID Any Computer name The name of the local computer, sent to server. Can be specified only for a DSN-less connection.
Connection attributes that may appear in <filename>odbc.ini</> Name Possible Values Default Meaning Servername A valid &freetdsconf; server section none A &freetdsconf; servername, not a hostname as known to DNS. If you want to use ODBC-only configuration, use Server instead. Server A server name or (ip) address none Hostname of a server. Used in an ODBC-only configuration. To specify a Microsoft SQL Server instance, use the form server\instance. Port Any TCP port Depends on the TDS version specified with configure The TCP port where the servername is listening. TDS_Version Any valid protocol version Depends on the TDS version specified with configure TDS protocol version to use (e.g., 5.0, 7.0). ClientCharset A name recognized by the iconv library linked to FreeTDS. Corresponds to client charset in &freetdsconf;. ISO 8859-1 Character set (encoding) used by the client. APP Free form text, up to 30 characters. none Application name. Identifies the connecting application to the server. LANGUAGE Any us_english (Human) language the server should use for error messages. Address Any none IP address of the servername. Useful if you want to specify a server by address, rather than by name. The format is ip,port or simply ip in standard dotted-decimal notation. Database Any none Specify which database you want to access. If the database does not exist or the user lacks permission to access it, the connection will fail. TextSize Any Server-dependent Maximum size returned from server for blobs. PacketSize Any Server-dependent Size of packets to server. Some users saw some performance gain by increasing this value. Normally you shouldn't set it. Trusted_Connection Yes/No No Use your current account instead of UID/PWD attributes. This option require SSPI or Kerberos and supersedes any UID/PWD attributes passed from the application.
DSN-less configuration In a DSN-less configuration, the odbc.ini file is not consulted for server connection properties. To connect to a servername, your application may refer to a servername entry in &freetdsconf;, or explicitly specify the servername's hostname (bypassing &freetdsconf;). Sample files for a DSN-less configuration The odbcinst.ini is quite brief: ; ; odbcinst.ini ; [FreeTDS] Driver = /usr/local/freetds/lib/libtdsodbc.so The &freetdsconf; might look something like: ; ; freetds.conf ; [JDBC] host = jdbc.sybase.com port = 4444 tds version = 5.0 Connecting with a DSN-less configuration /* * application call */ const char servername[] = "JDBC"; refers to the [JDBC] entry in &freetdsconf;. sprintf(tmp, "DRIVER=FreeTDSrefers to the [FreeTDS] entry in odbcinst.ini.;SERVERNAME=%s;UID=%s;PWD=%s;DATABASE=%s;", servername, username, password, dbname); res = SQLDriverConnect(Connection, NULL, (SQLCHAR *) tmp, SQL_NTS, (SQLCHAR *) tmp, sizeof(tmp), &len, SQL_DRIVER_NOPROMPT); if (!SQL_SUCCEEDED(res)) { printf("Unable to open data source (ret=%d)\n", res); exit(1); } You can even establish a connection without reference to either odbc.ini or freetd.conf. Connecting with a DSN-less configuration that does not use &freetdsconf; /* * application call */ const char servername[] = "jdbc.sybase.com"; refers to the real server name. sprintf(tmp, "DRIVER=FreeTDSrefers to the [FreeTDS] entry in odbcinst.ini.;SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;TDS_Version=5.0;Port=4444;", servername, username, password, dbname); res = SQLDriverConnect(Connection, NULL, (SQLCHAR *) tmp, SQL_NTS, (SQLCHAR *) tmp, sizeof(tmp), &len, SQL_DRIVER_NOPROMPT); if (!SQL_SUCCEEDED(res)) { printf("Unable to open data source (ret=%d)\n", res); exit(1); } ODBC-only configuration An ODBC-only configuration relies solely on odbc.ini for server properties. Other &freetds; libraries don't know about odbc.ini. Sample ODBC-only <filename>odbc.ini</filename> file [ODBC Data Sources]Several DSNs might be listed here. In this example, we have only one, JDBC. It matches the [JDBC] entry later in the file. JDBC = Sybase JDBC Server [JDBC] Driver = /usr/local/freetds/lib/libtdsodbc.so Description = Sybase JDBC Server Trace = No Server = jdbc.sybase.com Database = pubs2 Port = 4444 TDS_Version = 5.0 [Default] Driver = /usr/local/freetds/lib/libtdsodbc.so ODBC-combined configuration Like the DSN-less configuration, ODBC-combined keeps server properties in &freetdsconf;. The difference is that your applications can refer to the server by its DSN. To make that possible, the DSN entry in odbc.ini refers to the servername entry in &freetdsconf;. Sample ODBC-combined <filename>odbc.ini</filename> file [ODBC Data Sources]Several DSNs might be listed here. In this example, we have only one, JDBCdsn. It matches the [JDBCdsn] entry later in the file. JDBCdsn = Sybase JDBC Server [JDBCdsn] Driver = /usr/local/freetds/lib/libtdsodbc.so Description = Sybase JDBC Server Trace = No Servername = JDBCRefers to the [JDBC] entry in &freetdsconf;. Database = pubs2 [Default] Driver = /usr/local/freetds/lib/libtdsodbc.so Sample ODBC-combined &freetdsconf; file ; ; freetds.conf ; [JDBC] host = jdbc.sybase.com port = 4444 tds version = 5.0 With this arrangement, an application can connect to the server in two ways, via its DSN (JDBCdsn), or its servername (JDBC). Troubleshooting ODBC connections Supposing everything compiles and installs without trouble, how do you know if your ODBC setup works? Or, if you know it doesn't, what then? First, try to connect with tsql. If you're intending to use &freetdsconf;, exercise it with tsql -S servername. If not, use TDSVER=7.0 tsql -H hostname -p port If tsql works and isql doesn't, you've isolated the problem to the ODBC setup. &freetds; might have some interoperability problems, but mere connection to the database isn't one of them! If tsql doesn't work, turn on logging with TDSDUMP. The log will tell you what TCP/IP name (and address) FreeTDS is attempting to connect to, and what version of the TDS protocol it's using. With iODBC iODBC comes with a sample command line query program called odbctest, located in the iodbc/samples directory. Using this program you can get a listing of DSNs, connect, and issue queries. For debugging purposes, you may wish to link a program such as odbctest directly to &freetds; instead of to the driver manager. Why? Once the program is started in the debugger, the driver entry points become viable breakpoints. Because the DM loads the driver dynamically with dlopen(3), no driver addresses even exist until the runtime linker loads it. To do so, compile and install the &odbc; driver with iODBC as normal When linking directly to &freetds; you still need the Driver Manager's header files., then compile and link the program: Compile <filename>odbctest</> without a driver manager. $ make odbctest.o $ gcc -g -o odbctest odbctest.o /usr/local/freetds/lib/libtdsodbc.a Now you can run gdb or another debugger and set breakpoints on functions in the library without the driver manager getting in the way. With unixODBC Try isql -v dsn username password, and have a look at the log. See if the right address and TDS version are being used. Adjust to taste. Use <command>osql</> The osql utility is a Bourne shell script that checks your ODBC configuration. If it approves, it invokes the unixODBC isql utility. Cf. man osql for details on its use. Use <command>osql</> to test the ODBC setup. $ make odbctest.o $ osql -S machine -U mr_ed -P hayseed looking for odbc.ini and odbcinst.ini in /usr/local/etc reading "/usr/home/mr_ed/.odbc.ini" [machine] found in "/usr/home/mr_ed/.odbc.ini" found this section: [machine] Database = testdb Servername = machine Trace = Yes TraceFile = /tmp/unixodbc.trace looking for driver for DSN [machine] no driver mentioned for [machine] in .odbc.ini looking for driver for DSN [default] driver "FreeTDS" found for [default] in .odbc.ini found driver named "FreeTDS" FreeTDS is not a readable file looking for entry named [FreeTDS] in /usr/local/etc/odbcinst.ini driver "/usr/local/lib/libtdsodbc.so" found for [FreeTDS] in odbcinst.ini /usr/local/lib/libtdsodbc.so is a readable file Using ODBC-Combined strategy FreeTDS servername is "machine" (from /usr/home/mr_ed/.odbc.ini) looking for [machine] in /usr/home/mr_ed/.freetds.conf "/usr/home/mr_ed/.freetds.conf" is a readable file found this section: [machine] host = machine.example.com port = 2500 tds version = 7.1 machine.example.com has address 10.82.32.177 DSN: machine Driver: /usr/local/lib/libtdsodbc.so Server's hostname: machine.example.com Address: 10.82.32.177 Attempting connection as mr_ed ... + exec isql machine mr_ed hayseed -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> The reader is here advised that the isql that comes with many versions of unixODBC will truncate text and surprise in other ways without warning. If it behaves strangely, try &freetds;'s bsqlodbc before you decide you've found a &freetds; bug. Advanced Configurations This chapter details some advanced configurations that need expanded explanation. Big Endian Clients with Buggy <productname>SQL Server</productname>s Several version of Microsoft SQL server have a bug that affects big endian clients. This includes 7.0 GA and 7.0 SP1. Furthermore, TDS Protocol version 7.0 is natively little endian. SQL Server 2000 is also reported not to work from big endian clients without little endian emulation turned on. The terms big endian and little endian come originally from Gulliver's Travels. In computer science they refer to the the integer byte-order for a processor. Big endian processors, such as Sparc and PowerPC store the most significant byte in the first memory location of a multi-byte integer. Little endian processors, such as Intel and Alpha do it the other way around. So the 16-bit number 258 would be 0x0102 on big endian and 0x0201 on little endian machines. In this example we want to force connections to a server named mssql to emulate a little endian client. We are using protocol version 4.2 here, version 7.0 or above will automatically emulate little endian mode regardless of the &freetdsconf; setting. You shouldn't use this option, set another protocol version instead (7.0, 7.1 or 7.2). Emulate Little Endian &freetdsconf; setting [mssql] host = ntbox.mydomain.com port = 1433 tds version = 4.2 emulate little endian = yes Localization and <acronym>TDS</> 7.0 TDS 7.0 uses 2-byte Unicode (technically, UCS-2) to transfer character data between servers and clients. Included in character data are query text (i.e., SQL), metadata (table names and such), and bona fide data of datatypes nchar, nvarchar, and ntext. (Background information on Unicode and how it affects &freetds; can be found in the appendix.) Because most Unix tools and environments do not support UCS-2, &freetds; provides for conversion by the client to other character sets. The mechanism used is determined by the configure script, which looks for a iconv(3) function, an implementation of the iconv standard. If no iconv library is found, or if it is explicitly disabled, &freetds; will use its built-in iconv substitute, and will be capable of converting among only ISO 8859-1, UTF-8, and UCS-2. To learn what character set the client wants, &freetds; prefers the applicable &freetdsconf; client charset property. If that is not set, it parses the LANG environment variable. In either case, the found string is passed to iconv(3) (or its built-in replacment). The built-in replacement expects GNU iconv names: ISO-8859-1, US-ASCII, or UTF-8.. If neither is found, UCS-2 data are converted to ISO 8859-1. To list all supported iconv character sets try iconv(1). GNU's does: $ iconv --list For other systems, consult your documentation (most likely man iconv will give you some hints). In this example a server named mssql will return data encoded in the GREEK character set. Configuring for GREEK &freetdsconf; setting [mssql] host = ntbox.mydomain.com port = 1433 tds version = 7.0 client charset = GREEK If &freetds; runs into a character it can not convert, its behavior varies according to the severity of the problem. On retrieving data from the server, &freetds; substitutes an ASCII '?' in the character's place, and emits a warning message stating that some characters could not be converted. On sending data to the server, &freetds; aborts the query and emits an error message. It is well to ensure that the data contained in the database is representable in the client's character set. If you have a mix of character data that can not be contained in a single-byte character set, you may wish to use UTF-8. UTF-8 is a variable length unicode encoding that is compatible with ASCII in the range 0 to 127. With UTF-8, you are guaranteed to never have an unconvertible character. &freetds; is not fully compatible with multi-byte character sets such as UCS-2. You must use an ASCII-extension charset (e.g., UTF-8, ISO-8859-*)not EBCDIC or other weird charsets. Great care should be taken testing applications using these encodings. Specifically, many applications do not expect the number of characters returned to exceed the column size (in bytes). In the following example, a server named mssql will return data encoded in the UTF-8 character set. Configuring for <acronym>UTF-8</> &freetdsconf; setting [mssql] host = ntbox.mydomain.com port = 1433 tds version = 7.0 client charset = UTF-8 It is also worth clarifying that TDS 7.0 and above do not accept any specified character set during login, as 4.2 does. A TDS 7.0 login packet uses UCS-2. Microsoft Server Note String literals in SQL must be prefixed with 'N' unless the enclosed string can be represented in the server's single-byte character set, irrespective of the column's datatype. For example, in the SQL statement INSERT INTO tablename (greeting) VALUES ('Hallå') the string is subject to somewhat surprising treatment by the server. When the server parses the SQL, it extracts the data values for insertion (or update, or comparison, etc.) Unprefixed strings are converted to the single-byte character set of the server/database.The precise rules are unknown to the author. Inserted data are then of course stored in the column. In the case of UCS-2 columns — nchar, nvarchar, and ntext — the value stored is that which results from a second conversion: from the single-byte form to the UCS-2 form. The only safe way to enclose strings in SQL text is with an 'N' prefix: INSERT INTO tablename (greeting) VALUES (N'Hallå') Commentary What's surprising about this? Versions 7.0 and later of the TDS protocol use UCS-2 to send SQL text. No matter how your local client is configured — with UCS-2 or ISO 8859-1 or anything else — it's converted to UCS-2 before it's sent to the server. And obviously arrives at the server as UCS-2. If the column into which it's being inserted is also UCS-2, there's no need of any conversion, much less two, and certainly no need to lose infomation. Why this happens is anyone's guess. Here's one: it makes the datatype of the column unimportant. Regardless of whether you use char/varchar/text or nchar/nvarchar/ntext or a mixture of the two, the arriving SQL (if naïvely written) will store exactly the same characters. Domain Logins Domain logins can be used only with TDS protocol versions 7.0 or above. As mentioned in the installation chapter, Microsoft SQL Server includes the ability to use domain The term domain in this context is a Microsoft term. It refers to what's sometimes called an NT domain. It's unrelated to the DNS domain. DNS domains are used for name resolution. NT domains are used for authentication. Authentication is done by the domain controller, often the Primary Domain Controller (PDC). The SQL Server machine may belong to an NT domain. &freetds; provides an encrypted password — a domain password, known to the domain controller — that the server will ask the domain controller to verify. logins instead of standard server logins. Passwords are encrypted on the wire using a challenge-response protocol. &freetds; plays nice with such logins. &freetds; supports single sign-on (connecting without prompting for a username & password) or not, depending on how it was configured. For Windows hosts (both 32- and 64-bit), if SSPI is enabled, &freetds; will log in using so-called trusted authentication. For non-Windows hosts, enabling Kerberos provides similar functionality. When neither option is enabled, &freetds; can still log in using the domain account, but the user must supply the username & password. To use domain logins without SSPI or Kerberos, use the 'DOMAIN\username' syntax for the username and use the domain password. Logging in with a domain login $ tsql -S camelot -U 'NOTTINGHAM\lancelot' -P roundtable locale is "C" locale charset is "646" Msg 5703, Level 0, State 1, Server CPRO200, Line 0 Changed language setting to middle_english. 1> When &freetds; sees the \ character, it automatically chooses a domain login. Implementation details Support for domain logins in &freetds; is limited to the TCP/IP network protocol stack. &freetds; does not currently implement support for Named Pipe-based SQL connections — that is, connections transported over the DCE/RPC interface, which uses TCP port 139, 445, or 135 on Win32 machines depending on the type of encapsulation used for DCE/RPC itself. Supporting this would require a fairly extensive DCE/RPC library for Unix. Samba has one that is licensed under the GPL and therefore not usable by LGPL-licensed projects such as &freetds; . For a technical description of the protocol used for domain logins, see http://davenport.sourceforge.net/ntlm.html Kerberos Support Perhaps surprisingly, It works because much of Active Directory is based on Kerberos. From each according to his ability; to each according to his needs. Kerberos can be used to authenticate to Microsoft SQL Servers. This affords single-signon (or, at most, double-signon) capability in non-Windows environment. To take advantage of Kerberos you have to set up your machine with keytab No, the author does not really know what he's talking about. from your Active Directory. You could use Samba or configure Kerberos directly (/etc/krb5.conf). configure includes options to define the location of your Kerberos installation (cf. ). By default UNIX does not initialize a Kerberos ticket with your login account. You must use kinit to initialize a ticket. You could also configure Kerberos in PAM to initialize a Kerberos ticket at login time. Threading in unixODBC unixODBC uses a strong thread-locking policy that causes big locks with the default configuration for &freetds;. Performance of multi-threaded applications can be affected because every operation is serialized. To avoid this problem, choose a threading model in odbcinst.ini. Sample <filename>odbcinst.ini</> for threading model [FreeTDS] Driver = /usr/local/freetds/lib/libtdsodbc.so Threading = 1 Sample <filename>odbc.ini</> for threading model [Server1] Driver = FreeTDS Server = myServer1 Port = 1433 TDS_Version = 7.2 You can use also a connection string e.g. DRIVER=FreeTDS;SERVER=myServer1;PORT=1433;TDS_Version=7.2;. Appending Dump Files When running &freetds; with applications such as Apache/PHP it is often difficult to get a usable log file. Since each of the many httpd children opens the file at the beginning of its connection and closes it on connection close, they tend to stomp all over each other. In append mode, the log file is opened for append each time it is written to and then immediately closed. If you are experiencing problems when running under Apache (or similar application) use append mode to generate useful logs. Turning on Dump File Append mode in &freetdsconf; [mssql] host = ntbox.mydomain.com port = 1433 tds version = 7.0 dump file = /tmp/freetds.log dump file append = yes In this example, the /tmp/freetds.log file will contain log entries for all processes using the Microsoft SQL Server server, identified by pid. Because there will be one log file being opened and closed more or less continuously, there is going to be a negative impact on performance. Also, be advised that the log file will grow quite large. As an alternative to &freetds; logging, you might also consider using tcpdump or wireshark to log network packets. While not as useful as a TDS log, it can also help to identify problems. TDS Connection Pooling The Connection Pooling server swims in the src/pool directory. The &freetds; connection pool is a server process; it emulates a SQL Server. Any program that can attach to a real SQL Server may instead elect to attach to the pool server. The pool in turn connects to the SQL Server and database you specify, and attempts to share these connections. See the src/pool/README for a more detailed description of its inner workings. To configure the pool server, first make sure &freetds; has a working entry for the real SQL Server by connecting to it with SQSH or another program. The &freetds; connection pool currently only supports TDS version 4.2. This restriction applies to both the client-to-pool and pool-to-server connections! After FreeTDS has been installed, you will find an executable named tdspool in the /usr/local/bin directory (or whatever directory was specified with the configure Edit pool.conf in the &freetds;'s etc directory. The pool.conf file is formatted like &freetdsconf;, with a section name in brackets and options for each section in key/value pairs. Just as in &freetdsconf; there are two types of sections, a [global] section whose options affect all pools, and a section with the name of the pool for pool-specific options. The following options are supported and may appear in either section. pool.conf settings Name Possible Values Default Meaning user Any valid user none The username used to connect to the servername. password Any none The password of the user at the servername. server Any TDS 4.2 entry in the freetds.conf file none The alias from the freetds.conf file representing the servername that will be connected to. database Any valid database User's default database The database on the servername to use. port Any TCP port none Port on which tdspool will listen. min pool conn 1 or more none Minimum number of open connections to maintain to the servername. max pool conn 1 or more none Maximum number of open connections to open against the servername. max member age 0 (no limit) or a number of seconds 0 Maximum age of idle members before connection is closed.
Now, let's put this into practice. pool.conf [global] min pool conn = 5 max pool conn = 10 max member age = 120 [mypool] user = webuser password = secret database = ebiz server = fooserv max pool conn = 7 port = 5000 The [global] section defines that we will open 5 connections against the server initially, and will increase up to 10 as demand requires. These connections will be closed after being idle for 2 minutes (120 seconds), but only until there are 5 remaining open. The [mypool] section defines a pool named mypool that will listen on port 5000. It will login to a SQL Server named fooserv using the user webuser and the ever so clever password of secret. Once logged in, the connections will use the database ebiz instead of webuser's default database. Also, since this SQL Server has a limited number of CALs (Client Access Licenses), we are restricting the maximum number of connections to 7, which overrides the global setting of 10. Run tdspool with the name of the pool you are serving. $ tdspool mypool Before your clients connect to the pool, you must edit your &freetdsconf; to include the host and port of the pooling server, and point your clients at it. stunnel HOWTO Contributed by Bradley Bell. To set up FreeTDS over stunnel between a Linux webserver and a W2k SQL server: Get unencrypted freetds working Install openssl and stunnel on the Linux box: stunnel.org Download the stunnel binary and openssl dll's for Windows. Generate stunnel.pem (complete with Diffie-Hellman parameters) for placement on the W2k box. See instructions in the stunnel FAQ. Start stunnel on the W2k box: $ stunnel.exe -d 61666 -r localhost:1433 61666 is just an arbitrary port number. Start stunnel on the Linux box: $ stunnel -c -d 1433 -r win2kserver:61666 where win2kserver is the hostname or IP address of the W2k box. Set up FreeTDS to use the tunnel. If this is your unencrypted entry in &freetdsconf;: Unencrypted entry in &freetdsconf; [win2kserver] host = win2kserver port = 1433 the encrypted equivalent uses: Encrypted entry in &freetdsconf; [win2kserver] host = localhost port = 1433 Use &freetds; &freetds; includes several utilities. Some are testing tools, some demonstration projects, some intended for day-to-day use. All have man pages. &freetds; Utilities</> <variablelist><title>(listed alphabetically) bsqldb A non-interactive equivalent of the isql utility programs distributed by Sybase and Microsoft. Like them, bsqldb uses the command go on a line by itself as a separator between batches. The last batch need not be followed by go. bsqldb makes use of the &dblib; API. Intended for production use. bsqlodbc A non-interactive equivalent of the isql utility programs distributed by Sybase and Microsoft. Like them, bsqlodbc uses the command go on a line by itself as a separator between batches. The last batch need not be followed by go. It uses the &odbc; API. bsqlodbc is a demonstration project, but can also aid in isolating problems. &odbc; applications typically have many layers, and it can be difficult to know if a problem arises in a layer, or in the interface between layers. By executing a query in bsqlodbc, you can see if the functionality of the &odbc; driver works when used as the folks who wrote the driver thought it would be used. datacopy A tool for migrating data between Sybase ASE and SQL Server or vice versa. datacopy will move table data from one server to another without the need for intermediate files. datacopy is much faster and more efficient than is freebcp out/in. datacopy makes use of the &dblib; bcp API. defncopy Replaces a similar program of the same name distributed by Sybase. defncopy reads the text of a stored procedure or view, and writes a script suitable for recreating the procedure or view. For tables, it reads the output of sp_help and constructs a CREATE TABLE statement, complete with CREATE INDEX, too. fisql A complete replacement of the isql utility programs distributed by Sybase and Microsoft. Like them, fisql uses the command go on a line by itself as a separator between batches. freebcp Replicates the functionality of the bcp utility programs distributed by Sybase and Microsoft. freebcp makes use of the &dblib; bcp API. The manual pages or online help for Sybase or SQL Server can be referenced for more detailed information on bcp functionality. osql A Bourne shell script that checks and reports on your configuration. tsql A diagnostic tool that uses uses the lowest level &freetds; library, libtds, as a way to isolate potential bugs in the protocol implementation. tsql is not a replacement for a complete isql. How to get what works with it working The following programs are known to work to some extent with &freetds;. Here you will find any special instructions for getting them compiled or running. <application>SQSH</application> SQSH is a command line based query tool written by Scott Gray to replace the isql utility that ships with Sybase ASE. It makes a great diagnostic tool for &freetds; as well. If you are having trouble, install SQSH (it's easy) and try getting that to work before more complicated arrangements. SQSH is an excellent tool. Because it uses &ctlib;, it works with &freetds;, but potentially — and with significant effort — it could be ported to ODBC and thus made useful for other server environments. Just a thought…. SQSH 2.1 includes direct support for &freetds;, so these instructions may not be necessary, but are still included just in case. After running configure in SQSH's directory (make sure you set the Sybase environment variable first), look for the Sybase_LIBS definition in the Makefile. Change the line to match this example. The <application>SQSH</application> Makefile # # The following set of CT-LIB libraries were determined automatically # by 'configure'. For most systems configure looks up the required # libraries by looking at the name of the OS (although this doesn't # mean it got them right), however if the line below ends with the # word "Guess", then 'configure' didn't have an entry for your operating # system and it took a best guess to figure out which libraries you # need. In either case, there may be problems, so look this line over # and if it doesn't work, compare it to the libraries located in # $SYBASE/samples/ct-library. # # The listings below show suggested libraries for Operating Systems # that frequently fail to be recognized by 'configure': # # SCO: -lblk -lct -lcs -lcomn -ltcl -ltli -lnsl_s -lintl -m -lsocket # Dynix: -lblk -lct -lcs -lcomn -ltcl -ltli -lnsl -lintl -lm -lseq # SYBASE_LIBS = -lct -ldl -lm At this point you can also enable readline support if you didn't specify it in the configure arguments. After that just type make and you are off and running. Perl There are a few ways to use Perl to connect to a SQL Server using &freetds;. DBD::Sybase The recommended choice is DBD::Sybase from Michael Peppler. Despite the name it works for any Sybase or Microsoft SQL Server. DBD::Sybase uses the &ctlib; API and works well. DBD::ODBC You may also use DBD::ODBC with the &freetds; &odbc; driver. You may find this attractive if you're familiar with DBD::ODBC. Sybperl Finally, you can use Sybperl. Scripts written against Sybperl will not run against other databases the way DBI scripts will. However, it will be familiar ground for those who know &dblib;. Building and using the Perl modules Building <systemitem class="library">DBD::Sybase</systemitem> $ cd DBD-Sybase-0.91 $ export SYBASE=/usr/local/freetds $ perl Makefile.PL $ make $ su root Password: $ make install There will be some output about missing libraries after perl Makefile.PL. These are normal. The following example will attach to Sybase's public JDBC server and run a simple query (it can be found in samples/test.pl): Connect to a server with <systemitem class="library">DBD::Sybase</systemitem> #!/usr/local/bin/perl # use DBI; my $dbh = DBI->connect("dbi:Sybase:server=JDBC", 'guest', 'sybase', {PrintError => 0}); die "Unable for connect to server $DBI::errstr" unless $dbh; my $rc; my $sth; $sth = $dbh->prepare("select \@\@servername"); if($sth->execute) { while(@dat = $sth->fetchrow) { print "@dat\n"; } } Building <systemitem class="library">DBD::ODBC</systemitem> $ cd DBD-ODBC-0.28 $ export SYBASE=/usr/local/freetds $ export ODBCHOME=/usr/local $ export DBI_DSN=dbi:ODBC:JDBC $ export DBI_USER=guest $ export DBI_PASS=sybase $ perl Makefile.PL $ make $ su root Password: $ make install We used the public JDBC server logins for our configuration here. You'll want to replace these with ones suitable to your environment. Connect to a server with <systemitem class="library">DBD::ODBC</systemitem> #!/usr/local/bin/perl # use DBI; my $dbh = DBI->connect("dbi:ODBC:JDBC", 'guest', 'sybase', {PrintError => 0}); die "Unable for connect to server $DBI::errstr" unless $dbh; my $rc; my $sth; $sth = $dbh->prepare("select \@\@servername"); if($sth->execute) { while(@dat = $sth->fetchrow) { print "@dat\n"; } } You'll note this is the same program as for DBD::Sybase with the exception of the connect statement, welcome to the magic of DBI! PHP There are three options for building PHP with support for &freetds; corresponding to the three APIs that &freetds; supports: &dblib;, &ctlib;, and &odbc;. All these examples build the CGI version. Consult PHP's documentation for building the Apache module and including other extensions. &dblib; PHP can be configured with &dblib; access for a "Sybase" server (which also works with Microsoft servers), or with the mssql extension, intended exclusively for Microsoft servers. PHP and &dblib; for <quote>Sybase</> First build &freetds; normally. $ ./configure --prefix=/usr/local/freetds $ make $ su root Password: $ make install Then build PHP with support for Sybase $ cd php $ ./configure --with-sybase=/usr/local/freetds $ make $ su root Password: $ make install And that's it! &ctlib; Option 2 is to use the &ctlib; API. Again here, we run into minor difficulties at build time. Applications linking with Sybase's OpenClient have to link in a handful of libraries and these libraries vary slightly from platform to platform. When creating &freetds; it was decided that there would be only one library: libct. This saves a great deal of library naming conflicts that Sybase ran into (e.g. libtcl is used both by Sybase and the language TCL), however some applications like PHP assume that all the Sybase libraries will be present. So, some hand editing of the Makefile is necessary to remove these extra libs. Build &freetds; just as you would for &dblib; in with &dblib;, above. Then configure PHP with &ctlib;. $ cd php $ ./configure --with-sybase-ct=/usr/local/freetds Now edit the Zend/Makefile looking for the libZend_la_LDFLAGS line and remove -lsybtcl -lintl -lcomn and -lcs, leaving the -lct. Then proceed to make and install PHP. $ make $ su root Password: $ make install We hope an upcoming version of PHP will automatically detect the presence of &freetds; and include only the -lct library. &odbc; The third and newest option is to use the &freetds; &odbc; driver with PHP. First build the iODBC or unixODBC driver manager and &freetds; as detailed in . Then build PHP with support for ODBC. $ cd php $ ./configure --with-iodbc=/usr/local $ make $ su root Password: $ make install Now everything should run. There is a sample PHP script in the &freetds; samples directory called odbctest.php. SybSQL SybSQL is a Qt-based GUI interface to Sybase databases that uses the &dblib; API. SybSQL has a fairly basic build process that simply uses a Makefile. In order for SybSQL to find Qt and &freetds; you need to define QTDIR and SYBASE environment variables. If you have Qt installed, you may have QTDIR defined already. To verify, type echo $QTDIR at the shell prompt. This example uses my own installation path of qt-2.3.1 (from RedHat 7.2), YMMV. $ export QTDIR=/usr/lib/qt-2.3.1 $ export SYBASE=/usr/local $ make When finished you'll have an executable named sybsql that you can run. One caveat to the way in which SybSQL and &freetds; interact is that SybSQL expects to be running under OpenClient, and makes the assumption that a valid $SYBASE/interfaces file exists. Since &freetds; has deprecated use of the interfaces file in favor of the &freetdsconf; config file, you may have to create a interfaces file just to satisfy SybSQL. By defining SYBASE to the parent directory of the interfaces file, you may put it wherever you like; it does not have to be in /usr/local. When using &freetdsconf;, &freetds; does not rely on the SYBASE variable for finding its own components, so it is safe to point it elsewhere. Python Your humble author is not enlightened enough to use Python, and the information contained in this section is a little rough. Please contact the list for more assistance or (better yet) to improve these instructions. Install distutils if you haven't already. $ tar xvfz distutils-latest.tgz $ cd distutils $ python setup.py install You can obtain the Python Sybase module here. This example uses version 0.34, the most current at the time of this writing, please adjust accordingly if using a different version. $ tar xvfz sybase-0.34.tgz $ cd sybase-0.34 $ export SYBASE=/usr/local/freetds $ export CFLAGS="-DHAVE_FREETDS" $ export LD_LIBRARY_PATH=/usr/local/freetds/lib:${LD_LIBRARY_PATH} $ python setup.py install Edit the example.py and fix the bottom stuff, FreeTDS lacks the 110 symbols for version use 100 $ python example.py Qt Qt has two drivers to access SQL Server databases: QTDS and QODBC. At the time of writing (January 2010) QTDS has a performance problem because rather than maintaining a connection, it instead re-connects for every query. So use QODBC. There are however some problems with wide character support on Qt because Qt assumes sizeof(SQLWCHAR) == 2. On some DMs, though — including iODBC, the default on Ubuntu — sizeof(SQLWCHAR) == 4, which could lead to invalid character conversion. ODBC on Unix &odbc; has some issues on Unix, mainly due to lack of clean specifications. ODBC and 64-bit ODBC was originally specified as 32-bitIn fact, the earliest versions were 16-bit.. Its evolution to 64-bit took place in the absence of a good specification which led to conflicting declarations and associated problems. For instance, some parameters are defined as SQLINTEGER but are used for pointer offsets. But SQLINTEGER was (and remains) 32-bit, while pointer offsets must be 64-bit. Also row numbers and some other formerly 32-bit quantities are now 64-bit. If you use unixODBC Frediano would recommend at least version 2.2.14. Earlier versions have issues if used on 64-bit environments. sizeof(SQLWCHAR) Under Windows sizeof(wchar_t) == sizeof(SQLWCHAR) == 2 but on many Unix systems you have sizeof(wchar_t) == 4. And some DMs decided to keep sizeof(SQLWCHAR) == 2 (including unixODBC) while in other DM sizeof(SQLWCHAR) == sizeof(wchar_t) == 4 (namely iODBC). This leads to incompatible ABIs between applications and drivers. If you compile the &freetds; ODBC driver using iODBC take care to ensure all drivers are compiled with the same header files. Alternatively, compile &freetds; with both includes and rename the library to use two ABIs (for instance having a libtdsiodbc.so and a libtdsuodbc.so). At the time of writing Ubuntu compiled Qt using iODBC but most packages use unixODBC. If you plan to use Qt with the &freetds; &odbc; driver, you should have an iODBC-compatible driver. Also be aware that the QODBC Qt driver has problems with iODBC and SQLWCHAR (see Qt). Due to these problems Frediano suggests not using this configuration (Qt database) on Ubuntu at this time. Default charset Character encoding is yet another trap. ODBC makes no provision for specifying client character encoding. By default many DM converting from multi-byte to wide characters assume the client uses ISO 8859-1. Even the &freetds; driver assumes ISO 8859-1 by default. Also some DM have problems converting multi-byte encodings (like UTF-8), by assuming a byte can be converted to a single wide character (and vice versa). That creates problems if you use multi-byte encoding for &freetds; driver. Troubleshooting Jason Mewes (Mall Rats) He's like motherf**king McGuiver, no he's better than McGuiver! Known Issues Porting Issues Date Structures and Offsets Microsoft and Sybase use different &dblib; date structures and conventions. Notably months can by [0,11] or [1,12]. Pay careful attention to the results of dbdatecrack(). Floating Point Precision may surprise you if you pay attention. Microsoft's &dblib; promotes single-precision to double in dbbind() by appending zeros; C promotes it to the nearest double. &freetds; relies on the C compiler. Math libraries vary, too. If porting an application whose output uses functions such at log(3), expect differences in different implementations. Perfectly consistent results between OSes will require the use of a single math library. <type>Text</type> Fields Questions sometimes arise over large varchar types (anything larger than varchar(255)) that became available with Microsoft SQL Server 7.0. When accessing long varchars with TDS protocol version 4.2 or 5.0, these fields will be truncated to 255 characters, due to limitations inherent in the protocol definition. Your best bet in that case is to convert them to text types. In Microsoft SQL Server 7.0 and later, varchar types can hold up to 8000 bytes (8000 ASCII characters or 4000 Unicode characters). To move these large varchars through TDS 4.2, convert them with either a CONVERT as in, SELECT mycol = convert(mycol, text) FROM mytable or with the newer SQL92 CAST syntax e.g., SELECT CAST(mycol as TEXT) FROM mytable A related problem is that some people have reported problems with text field using TDS version 7.0. One known workaround is to convert long strings to varchar(8000) in your query text with CAST( variable_name as varchar(8000) ) as variable_name. Text datatype handling is fixed in &freetds; 0.60, except for bcp operations. There is also a bug (Lions and tigers and bugs! Oh, my!) in Microsoft's implementation of text fields. Disregardless [sic] of their documentation, you must explicitly set the value of TEXTSIZE, else the text fields will be represented to have a maximum size of 4 gigabytes or so. The usual manifestation is some sort of spurious out of memory error or segment fault. To avoid this, set TEXTSIZE to some reasonable value before querying any TEXT fields. For example, in isql: 1>set TEXTSIZE 10000 2>go Another way to handle control the default TEXTSIZE is to use the setting in &freetdsconf;. Endianism If either your server or your client is a big endian system, pay careful attention to all references to endianism anywhere near &freetds;. See the section on Little Endian Emulation for details. <type>Datetime</type> and <type>Money</type> Big endian clients may experience difficulty with Microsoft servers. Some versions of SQL Server 7 did not handle these types on these machines correctly, according to the protocol. According to http://support.microsoft.com/support/kb/articles/Q254/1/23.ASP on the Microsoft support site, it's fixed as of service pack 3. Unfortunately, there's no direct way for &freetds; to know whether or not a service pack has been installed, and how/whether to support the buggy version is an outstanding issue. Your best bet is to apply their patch. The Knowledge Base article states The Sybase CT-Lib client is the only known big-endian client that can connect to SQL Server. Depends on who's doing the knowing, of course. Microsoft's <quote>Integrated Security</quote> &freetds; may be unable to connect to the server. The error message will be "Login failed for user 'example'. Reason: Not associated with a trusted SQL Server connection". To solve this, turn on SQL Server authentication: Open the SQL Server Enterprise Manager, Select the server, Right mouse click and choose Properties. A properties window will appear. Choose the Security tab. The security properties will be displayed. Change the Authentication field to SQL Server and Windows, Apply the changes and try again. These instructions apply to Microsoft SQL Server 7 and SQL Server 2000. &freetds; supports integrated security mode, too. If you have SQL Server running in integrated (domain) mode along with a Windows PDC, and wish to try it, see Domain Logins in the Advanced Configurations chapter. If you have Active Directory you can also use Kerberos, see Kerberos support. Is the server there? Start with <command>ping</> First ping the host to make sure you can talk to the machine the server resides on. Finding the server's host $ ping -c1 myhost PING myhost (127.0.0.1) from 127.0.0.1 : 56(84) bytes of data. 64 bytes from myhost (127.0.0.1): icmp_seq=0 ttl=255 time=250 usec A successful ping shows that your network isn't preventing you from reaching the machine hosting the server. Test with <command>telnet</> Attempt to telnet to the port, to verify that the servername is listening. Finding the server $ telnet myhost 1433 Trying 127.0.0.1... Connected to myhost. Escape character is '^]'. If you get output as above, the servername is listening. If you get a 'Connection Refused' message, you're talking to the wrong host, wrong port, or the servername is down. To exit telnet: When connected, telnet's command mode may be entered by typing the telnet escape character (initially Ctrl-], as above). Once in command mode, telnet may be exited with the command quit. Test with <command>tsql</> tsql can be run in two ways, one which uses &freetdsconf; and one which connects directly using the host and port. First attempt a connection using host and port. Connecting to the server, bypassing &freetdsconf; $ cd src/apps $ TDSVER=7.0 ./tsql -H myhost -p 1433 -U user If you receive a message of 'Login Failed.' then your connectivity is OK, but you have a authentication issue. If you receive a message like Msg. No.: 18450 Severity: 14 State: 1 Login failed- User: loginid Reason: Not defined as a valid user of a trusted SQL Server connection SQL Server is accepting only domain logins. This applies only to Microsoft SQL Server and you'll need to have your DBA verify that server logins are allowed, or use a domain login. Finally, if you received a prompt, then try tsql using the servername. Connecting to the server using &freetdsconf; $ ./tsql -S myserver -U user If this fails, FreeTDS is either not finding your &freetdsconf; file, finding the wrong one, or there is an error in the file. Logging &freetds; has quite extensive logging capabilities. These are often invaluable in setting up new configurations, when it's hard to be sure precisely what configuration information is being used, and what communication is (not) working. Often such questions can be quickly resolved by turning on logging and examining the logs. Environment Variables that Control Logging TDSDUMP Log files can be turned on using the TDSDUMP environment variable. For instance, setting the location of a dumpfile $ export TDSDUMP=/tmp/freetds.log Will generate a log file named freetds.log in the /tmp directory. The filenames stdout and stderr are also supported. They can be handy if you want to intersperse the log output with your application's output, or if your application opens more than one connection. (The logfile is otherwise normally truncated each time the library connects to the server.) TDSDUMPCONFIG Set TDSDUMPCONFIG to a file to write information to on how the configuration information is being obtained, e.g. from environment variables, a &freetdsconf; file, or interfaces file. Sometimes it's unclear what source of information &freetds; is using to connect to a given servername. This variable can make that bright and clear. What if you were running Apache/PHP? Apache has many children. Setting the TDSDUMP (and/or TDSDUMPCONFIG) variable to a null string will cause &freetds; to open a log under every PID. $ export TDSDUMP="" The log files will be named /tmp/freetds.log.9999, where 9999 is the pid number of the process generating the log. A couple of important notes about using the logs with &freetds;. First, the logs tend to grow large, so trim or archive them often. Secondly, &freetds; will record certain network packets to the log, this includes login packets which can contain clear text or clear text equivalent passwords. So, if this is a concern (most likely is) make sure that the files are not world readable, and avoid posting them to mailing lists. Once in a while, someone writes to the mailing list, asking why &freetds; is so slow. It sometimes turns out that logging was left turned on. Don't you be the next victim! &freetds; logs are meant for development and debugging, not as a system monitoring tool. &freetdsconf; variables that Control Logging See Valid bitmask values for debug flags entry in &freetdsconf; The logfile is normally truncated each time &freetds; connects to the server. Logging in ODBC land (Tree-huggers need not worry) Many ODBC Driver Managers have their own support for logging. How logging is controlled, however, varies widely by implementation. The ODBC log is often very helpful because it provides a log of all calls made directly by the application. unixODBC unixODBC supports logging via some entries in odbcinst.ini. For example: [ODBC] Trace = Yes TraceFile = /tmp/sql.log ForceTrace = Yes Will generate a log file named sql.log in the /tmp directory. "Page contains no data" Web browsers display this error when the underlying script didn't return any information. The error could be in any of several places, of which &freetds; is one. To isolate the cause, turn on enough logs to see the query, and execute the query through SQSH. If that works, the problem lies further up the chain. If it doesn't, take a look at the known issues section. &freetds; under PHP executing within an Apache process may abort with a segmentation fault. The evidence of this is the words "Segmentation Fault" or "Bus Error" in the Apache error log, and a "Page contains no data" warning displayed by the web browser. The unexpected termination of the process causes the connection to the client to be closed before any buffered data is sent. To diagnose this sort of problem, follow this procedure; Compile PHP as a CGI binary. This should have been a side-effect of your build of PHP, look for an executable called php in the PHP build tree. If you are using a packaged binary, look for a php-cgi package. Make a reproducer. Make a PHP script that reliably reproduces the segmentation fault via the web server, but with no arguments. This is so that you can execute it using the PHP binary, thus excluding the web server as the cause of the problem. Reproduce on command line. Reproduce the segmentation fault using PHP on the command line, by activating PHP with the script as first argument. For example; % php file.php Segmentation fault % If this doesn't reproduce the segmentation fault, then there is something about the environment that differs, so look for the differences and resolve them. Check environment variables, assumptions made by the script, the UID you are executing under, and the current working directory. Reproduce using GDB. Now reproduce the segmentation fault using the debugger, GDB. Instead of aborting to the command line, GDB will stop executing the PHP program at the point of failure. Use the bt command to determine the details and context. This is called a backtrace. % gdb php gdb> run file.php gdb> bt Analyze the backtrace. Read the backtrace to determine what the cause of the problem is. Examine each line, assigning responsibility by component; some code is PHP, some is &freetds;, and some may be glibc. You will need the source code for each component, and software engineering debugging skills. If you cannot determine the cause yourself, send the backtrace to the mailing list, along with the PHP script. It helps to make the script as small as possible, but still fail. It also helps to report the version numbers of PHP, and &freetds;. Slow connection or data retrieval &freetds; is not slow. We know this because we've tested it. It's measurably slower than the vendors' products for some operations, but it's not noticeably slower and it's certainly no laggard. If your experience is different, if you're waiting 30 seconds for simple operations or minutes instead of seconds for for query results, something is up with your setup. There are two likely culprits. Logging. If everything seems a bit sluggish, check to make sure logging is turned off. TDSDUMP should not be defined, and there should be no dump file mentioned in &freetdsconf;. You can double-check by setting TDSDUMPCONFIG temporarily, which will log only the startup process. DNS. If connecting to the server takes 30 seconds or 1 minute, you could do worse than to check your resolv.conf. Use host or nslookup to confirm that &freetds; can actually resolve the name/address you provided in &freetdsconf;. Give particular attention to reverse DNS lookups, if you were forced (or thought you were forced) to identify the server by number, instead of by name, as Vint intended. You can defeat &freetds;'s automatic reverse-DNS lookup feature by inserting #define NOREVERSELOOKUPS in src/tds/config.c, rebuilding, and reinstalling. Reverse lookup code has been removed as of version 0.62 because it was poorly implemented. Packet size. The default packet size setting in &freetdsconf; (see initial block size) is usually fine. Slowness can potentially be due to multiple packet to use. Under GNU/Linux system we use an optimization to reduce network traffic; you shouldn't see much difference using this system. Getting Help Beatles Help me if you can, I'm feeling down And I do appreciate you being 'round. Help me get my feet back on the ground, Won't you please, please help me? In the battle against frustration and wasted motion, this manual is our first defense. Our documentation is intended to make it possible for a knowledgeable user to, well, use &freetds; without further assistance. We strive to include all known features and behaviors here, so you can work quickly and anonymously, and go home before 5:00. Would that it were always thus. Reconfirm the installation For initial setup and login problems, review . Distinguish between network and server issues, between finding the server and logging into it. The TDSDUMPCONFIG log will show how the servername is being looked up, what address & port is being used, what TDS version is being used. The TDSDUMP log will show quite clearly whether or not the server accepted the connection, and whether or not the login succeeded. Remember compiled-in defaults can be displayed with tsql: $ tsql -C Compile-time settings (established with the "configure" script) Version: freetds v0.83.dev.20110124 freetds.conf directory: /usr/local/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: no TDS version: 7.0 iODBC: no unixodbc: no SSPI "trusted" logins: no Keberos: no For &odbc; setup issues, the osql script is intended to confirm the configuration files are all sane. If it fails to report a problem, please post a message describing the problem to the mailing list. Thanks. Isolate the cause Successful problem isolation will yield earliest resolution. You (believe it or not) have more information about your environment than anyone else does, and have the greatest motivation to solve your problem. The resources at your disposal will be much more useful if the problem is specific. (Sorry if this is obvious. If it is, you might be surprised how often it's not.) If you can demonstrate the problem with tsql or sqsh, you can expect a quick answer to your question, possibly even a fairly quick fix. (It has happened several times in the last few years that bug reports to small problems were fixed the same day. On a few occasions, new functions were added in a few days. Making &freetds; useful and bugless is the goal of the project, after all.) &freetds; being what it is, problems frequently arise amidst complex environments. It can be hard for both you and the list participants — who are your allies and best resource — to determine what's going wrong. If you can submit a script that they can use to try to reproduce your results, you have a much better chance of happy resolution. On the plus side, the list includes people with a variety of backgrounds, who frequently answer questions that aren't really about &freetds; per se. Clear questions have sometimes even led to submitting patches to other projects. Try a different client</> <para>&freetds; comes with its own utilities that use the various libraries. It's a good idea to run your query through one of them — the one that uses the same API you're using — to see if it produces the same behavior you're seeing. That helps eliminate your application (and the rest of the calling hierarchy) as a source of the problem.</para> </sect2> <sect2 id="help.permissions"> <title>Check permissions If your query works in tsql but not with Apache, make sure the account running Apache can find and read freetds.conf. The Mailing List 3 Henry VI, I, ii, approximately In them I trust; for they are [hackers] Witty, courteous, liberal, full of spirit. The Archive The &freetds; mailing list archive is a good place to start. It is searchable. It should be considered the most up to date (and least edited) source of information. New developments between releases tend not to be announced on the website. The website is updated only intermittently, when we post a new release or &freetds; is somehow in the news, say. If you found a bug or need a feature, you may find it was announced/discussed/fixed by perusing the archive. Ask the list Many of the original authors and anyone maintaining or extending the code reads the list. The traffic tends to be bursty. It usually focuses on build problems and troubleshooting. Again, the more specific your question, the sooner you'll get a useful reply (if it comes). Please, do not email the authors directly. You may well be ignored because they're they type that gets a ton of mail. Anyone willing to address your question reads the list, and you don't want to offend anyone willing to help you by going about it the wrong way. What to include when asking for help Waddya got? It's important to convey your setup and configuration. SQL Server version &freetds; version (or snapshot date, if not a release) which client library you are using what language or Perl module, as appropriate, you're using your client OS and hardware architecture Attach a logfile If you're puzzled by some interaction with the server (often the case), it's a very good idea to set TDSDUMP and attach the log to your message. Messages are currently limited to 75 KB attachments, and the logs are quite detailed, so make your query as short as possible. If necessary, trim the log; gzip is also your friend here. It's always a good idea to post it on a website where people can fetch it if they're so inclined. Log files are especially important if you're not programming at the C level. Sometimes there are problems — an impedance mismatch, to coin a phrase — between &freetds; and the calling framework/language. But if you write to the list and say Why does my PHP foo() returns an empty string?, please keep in mind that your question might as well be in Urdu to someone familiar with the C library. Without knowing which C function was called, and with what data, it's impossible to even begin to try to answer the question. Think about it this way: If you attach a log no one reads, you wasted some bandwidth. If you don't attach one and someone asks you for it, you wasted a day. Like that. Show your work Great questions make the problem crystal clear to a tired developer after supper. Show what you did, and show what happened. Throughout this User Guide, you've seen examples of screenshots; in each case the first line was the command entered, followed by the machine's response. By showing verbatim what you did and saw, you give someone who knows what to do a chance to look over your shoulder. Across the Internet! How cool is that? Whether you're having a problem with your own application or with something at a higher level, you're well advised to try to reproduce it using one of the &freetds; utilities, preferably one that used the same client library you're using. If, say, bsqldb works and your program doesn't, that's a clue. By the same token, if bsqldb exhibits problems, too, chances are you found a bug. Or — how to say it? — a missing feature. It's always good to know about those. Helping Bertrand Russell The time you enjoy wasting is not wasted time. &freetds; is a cooperative, volunteer effort. Flame wars on the list are unknown and the signal to noise ratio is pretty high for its venue. Many people have contributed patches, and few have been turned away. Pick a weak spot and fix it. We don't have enough non-English speakers to test our character set conversion features. Anyone willing to participate in that way would be most welcome. Canonical examples of using the each library would be very helpful to newcomers. An isql Perl and PHP would all make debugging and testing easier for everyone. Send a patch Good patches are nearly always applied in short order. Patches uploaded to SourceForge trigger automatic notification to the &freetds; mailing list. Correct this User Guide Any corrections or suggestions, be they typographical, grammatical, structural, factual, or mineral are most welcome. Please send it to &freetds; FAQ Master, or post a message to the list. The User Guide is maintained in SGML DocBook format; the file in your distibution is doc/userguide.sgml. It is a flat ASCII file that you can edit with any text editor. You don't have to know SGML to correct or add to the User Guide, however. Just open it up, find the place you're interested in, and type away. Do a diff -u old_version your_version and post your patch to the SourceForge site. Any errors or lackings in your markup will be graciously emended by yours truly. Document an <acronym>API</> We have just begun an independent reference manual to &freetds;; the main API documents are the work of the server vendors. We're using Doxygen, which extracts documentation directly from comments in the source code, and we're maybe 25% done. The TDS protocol is partly documented, as are the APIs to libtds and &dblib;, but much remains. Be the Webmaster The FAQ and in particular the news don't get updated often enough. If that's your thing, drop a line to your friendly project maintainer, James K. Lowden. Light another's taper Every question you answer on the mailing list will save someone time and, if done well, will actually improve your own knowledge. The project's developers will often answer technical questions that require substantial understanding of the code or suggest a possible bug. Setup issues, though, connecting and logging in to the server, getting Apache going, are questions many experienced users can and do answer, thereby fostering the community on which the project depends. Your experience may well be more closely aligned with the question posed than that of anyone else reading the list that day. You may use that framework or language or OS, or have that particular server. No one, no matter how expert in the code, has used every configuration, version, OS, compiler, etc. Whether you simply confirm there's a problem in some particular arrangement, or say, dunno, works for me, you're adding information. Ambitious ideas If you want to get your hands really dirty, here are some big ideas to contemplate. <literal>libtds2</literal> After many years developing &freetds;, we've learned quite a bit about the protocol and how to write database libraries. Unfortunately, though, one of the things holding us back — and, obviously hampering the project — is the underlying utility library. This wouldn't be a from-scratch effort; most of the code is already written. What's needed is a more uniform API that better reflects the TDS protocol, and that does not attempt character set conversions immediately on receipt of the data. <literal>libstddb</literal> This would be a new client library modelled after stdio, a project to demonstrate what database programming should be like. Server code &freetds; includes a little stub of a server, but it could be much more useful. One idea would be to make it a front-end to SQLite, thereby creating for the first time a TDS client & server pair composed entirely of free software. Advocacy Out of ten people you know, it's a fair bet 10 never heard of &freetds; and nine don't understand the problem it solves. Lots of places have begun to use Microsoft SQL Servers in all sorts of ways, and if you adhere to the Microsoft line, there's only one way to connect to them: from a Microsoft OS. What can &freetds; do that can't be done any other way? Glad you asked. &freetds; can Connect to every version of either vendor's server, using the same binaries. Provide a &ctlib; for Microsoft SQL Server. This feature alone allows DBD::Sybase and sqsh, among others, to connect to Microsoft's product. Provide a modern &dblib; for Microsoft SQL Server: Win32/64, and TDS 7+. Provide a bcp-capable interface and command-line utility on unix-like operating systems for Microsoft SQL Server. Run on many more operating systems than either vendor's libraries do. Get fixed, instead of telling you to get stuffed. Amuse and inform. Also frustrate and infuriate, but we don't put that under Advocacy. If more people knew, fewer would be stuck. Programming TDS protocol reference Can be found on www.freetds.org API Reference Manual The reference manual is installed as part of FreeTDS. It can be regenerated at any time using Doxygen with cd doc; make doc. The reference manual is a work in progress: only &dblib; is completely documented, and quite minimally at that. Should you find it inadequate, you may be interested to learn it's not hard to add to, technically. Doxygen generates a manual from encoded comments in the source code. Its markup syntax is not hard to learn. You can read more about it at the Doxygen website. Basic API coverage information for the db-lib, ct-lib, and ODBC client libraries is maintained in doc/api_status.txt, included in the source distribution. For your convenience and enjoyment, we include that file in the following sections. In each table, we note for the function Sybase and Microsoft sometimes use slightly different names for the same function. It is the intention of the the extent to which it is implemented. The Status field may be: &dblib; API function status domain (blank) Function is not implemented. stub Function is implemented as a stub. Some such functions return SUCCEED even though they have no effect, to satisfy upper layers. Partial Function is partly implemented. We haven't dealt with every possible option, for instance. OK Function is implemented. Completely, we claim. &dblib; API Implementation Summary Microsoft's version of &dblib; is online. Sybase's is both online and can be downloaded as a PDF file. Links such as these are quite perishable. Should you find them broken, please check the FreeTDS User Guide posted on our website. If it's out of date, please let us know, so we can correct it. Thanks. &dblibapisgml; ct-lib API Implementation Summary Sybase ct-lib documentation can be found online and in PDF form. Links such as these are quite perishable. Should you find them broken, please check the FreeTDS User Guide posted on our website. If it's out of date, please let us know, so we can correct it. Thanks. &ctlibapisgml; ODBC API Implementation Summary Microsoft's ODBC documentation is online. The functions are linked to the reference page on Microsoft's website. Links such as these are quite perishable. Should you find them broken, please check the FreeTDS User Guide posted on our website. If it's out of date, please let us know, so we can correct it. Thanks. &odbcapisgml; DB-Library for the Tenderfoot Mark Twain Few things are harder to put up with than the annoyance of a good example. Below is a complete sample working &dblib; program, presented as a series of examples. Features of sample code Processes command-line options to select the server, database, username, and password Remaining arguments on the command line comprise the SQL query to execute Installs error and message handlers Illustrates correct row-processing Illustrates correct error detection and handling Other sample code may be found in the distribution, in the cleverly named samples directory. A complete program, heavily commented for your perusal, is apps/bsqldb.c. What's the big deal with errors? Correct handling of errors is extremely important in database applications because they involve two systems most others don't: the network and the database server. Both can give rise to errors that, if not detected and reported when they occur, let the application proceed blithely on until something truly mysterious happens. In the worst case, in the absence of a properly reported error, the application may seem to have updated the data, when in fact it did not. Every &dblib; application uses the network, making it subject to network failures. Database programs also almost always have very high data integrity requirements. It is necessary to know the row was absolutely, positively committed, once and only once, without error or exception. Without taking great care to trap and handle all error conditions, no statement about the program's reliability can be made with confidence. How to Get and Build the sample code Run doc/grep_sample_code to extract the C code from the User Guide SGML source. Compile Link Files Required to Build the Sample Code sybfront.h sybdb.h libsybdb.a or libsybdb.so Your library's extension may vary according to your operating system. The source code may be built with commands similar to these. The precise options and paths depend on your particular system. The commands below work with the GNU compiler and linker on an ELF system with dynamic linking, common on Linux and BSD systems. Building the Sample Code $ ../doc/grep_sample_code ../doc/userguide.sgml > sample.c $ cc -I /usr/local/include -Wl,-L/usr/local/lib -Wl,-R/usr/local/lib sample.c -lsybdb -o sample where /usr/local/include and /usr/local/lib are respectively the locations of your header files and libraries. We now proceed to the code proper. Header files We need two header files to use &dblib;. We need a few others to deal with I/O in C, as you know. Also declare the error and message handler functions, more about which later. Sample Code: &dblib; header files #include #include #include #include #include #include ]]> #include <sybfront.h> /* sybfront.h always comes first */ #include <sybdb.h> /* sybdb.h is the only other file you need */ int err_handler(DBPROCESS*, int, int, int, char*, char*); int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*, int); Prolog Nothing special here. Collect the command line parameters. We do this with the standard getopts(3) function. Cf. man 3 getopts for details. Sample Code: &dblib; prolog extern char *optarg; extern int optind; const static char syntax[] = "syntax: example -S server -D db -U user -P passwd\n"; struct { char *appname, *servername, *dbname, *username, *password; } options = {0,0,0,0,0}; int main(int argc, char *argv[]) { int i, ch; LOGINREC *login; DBPROCESS *dbproc; RETCODE erc; options.appname = basename(argv[0]); while ((ch = getopt(argc, argv, "U:P:S:D:")) != -1) { switch (ch) { case 'S': options.servername = strdup(optarg); break; case 'D': options.dbname = strdup(optarg); break; case 'U': options.username = strdup(optarg); break; case 'P': options.password = strdup(optarg); break; case '?': default: fprintf(stderr, syntax); exit(1); } } argc -= optind; argv += optind; if (! (options.servername && options.username && options.password)) { fprintf(stderr, syntax); exit(1); } Prolog Notes LOGINREC is a structure that describes the client. It's passed to the server at connect time. DBPROCESS is a structure that describes the connection. It is returned by dbopen(). RETCODE is the most common return code type for &dblib; functions. Initialize Initialize the library. Create and populate a LOGINREC record. Sample Code: &dblib; Initialize if (dbinit() == FAIL) { fprintf(stderr, "%s:%d: dbinit() failed\n", options.appname, __LINE__); exit(1); } dberrhandle(err_handler); dbmsghandle(msg_handler); if ((login = dblogin()) == NULL) { fprintf(stderr, "%s:%d: unable to allocate login structure\n", options.appname, __LINE__); exit(1); } DBSETLUSER(login, options.username); DBSETLPWD(login, options.password); Initialization Notes Always make dbinit() the first db-lib call. Install the error- and mesage-handlers right away. They're explained in more detail later. dblogin() almost never fails. But check! No point in trying to use a null pointer. The LOGIN record isn't directly accessible. It's populated via macros like these. There are other fields, but these two are essential. Look for SETLsomething in the documentation. Connect to the server dbopen() forms a connection with the server. We pass our LOGINREC pointer (which describes the client end), and the name of the server. Then, optionally, we change to our favored database. If that step is skipped, the user lands in his default database. Sample Code: &dblib; Connect to the server if ((dbproc = dbopen(login, options.servername)) == NULL) { fprintf(stderr, "%s:%d: unable to connect to %s as %s\n", options.appname, __LINE__, options.servername, options.username); exit(1); } if (options.dbname && (erc = dbuse(dbproc, options.dbname)) == FAIL) { fprintf(stderr, "%s:%d: unable to use to database %s\n", options.appname, __LINE__, options.dbname); exit(1); } Send a query &dblib; maintains a command buffer to hold the SQL to be sent to the server. Two functions — dbcmd() and dbfcmd() — build up the query from strings of text. The command buffer is reset after the query is sent to the server. We left the SQL on the command line. We fetch it now and send it to the server. Sample Code: &dblib; Send a query for (i=0; i < argc; i++) { assert(argv[i]); printf("%s ", argv[i]); if ((erc = dbfcmd(dbproc, "%s ", argv[i])) == FAIL) { fprintf(stderr, "%s:%d: dbcmd() failed\n", options.appname, __LINE__); exit(1); } } printf("\n"); if ((erc = dbsqlexec(dbproc)) == FAIL) { fprintf(stderr, "%s:%d: dbsqlexec() failed\n", options.appname, __LINE__); exit(1); } Initialization Notes Failure at this juncture is rare. The library is merely allocating memory to hold the SQL. dbsqlexec() waits for the server to execute the query. Depending on the complexity of the query, that may take a while. dbsqlexec() will fail if something is grossly wrong with the query, e.g. incorrect syntax or a reference to nonexistent table. It's only the first of a few places where an error can crop up in processing the query, though. Just because dbsqlexec() succeeded doesn't mean you're in the clear. Fetch Results A query may produce zero, one, or more results. Broadly, that entails providing buffers to &dblib; to fill, and iterating over the results a row (and column) at a time. Kinds of Results Results is a special term: it means more than rows or no rows. To process the results means to gather the data returned by the server into the application's variables. Kinds of Results Type Metadata Regular Rows Compute Rows Return Status Example SQL None None None None None Any INSERT, UPDATE, or DELETE statement Empty 1 set None 0 or more None SELECT name FROM systypes WHERE 0 = 1 Simple 1 set 0 or more None None SELECT name FROM sysobjects Complex 2 or more 0 or more 1 or more None SELECT name FROM sysobjects COMPUTE COUNT(name) Stored Procedure 0 or more 0 or more 0 or more 1 or more EXEC sp_help sysobjects
As the above table shows, results can comprise ordinary rows and compute rows (resulting from a COMPUTE clause). Stored procedures may of course contain multiple SQL statements, some of which may be SELECT statements and might include COMPUTE clauses. In addition, they generate a return status (with a RETURN statement or else automatically) and perhaps OUTPUT parameters. Data and Metadata Observe that a row is set of columns, and each column has attributes such as type and size. The column attributes of a row are collectively known as metadata. The server always returns metadata before any data (even for a a SELECT statement that produced no rows). Result-fetching functions Function Fetches Returns Comment dbresults() metadata SUCCEED, FAIL or, NO_MORE_RESULTS. SUCCEED indicates just that: the query executed successfully (whew!). There may be metadata (and perhaps data) and/or stored procedure outputs available. nextrow() data REG_ROW, compute_id, NO_MORE_ROWS, BUF_FULL, or FAIL. Places fetched data into bound columns, if any.
Binding Each time dbresults() returns SUCCEED, there is something to retrieve. &dblib; has different functions to deal with the different kinds of results. The functions are of two kinds: those that convert the data into a form desired by the application, known as binding, and those that return the data in native form. To understand binding, it may be easiest to examine two primitive functions, dbdata() and dbconvert(). dbdata() returns a pointer to the column's data. The data to which it points are in native form, 4 bytes for an INT, 8 bytes for a DATETIME and so on. dbconvert() converts between datatypes; you can hand it an integer and get back a character array (or a C double. You might think of dbconvert() as atoi(3) on steroids). dbbind() combines these two functions. The application indicates in what form it would like to use each column, and the library converts them on the fly as each row is read. To bind a column is to provide a buffer to &dblib; to fill and indicate which datatype the buffer is meant to hold. This is the sort of thing C++'s type system does so much better It may be well to pause here to observe the three ways a datatype is described in a &dblib; program. <function>db-lib</> Datatype Descriptors Sever Datatype Describes the data as an abstract type, not representing any particular kind of storage. SYBREAL, for example, doesn't imply any particular arrangement of bits; it just means a floating-point datatype corresponding to the T-SQL REAL type on the server. These all begin with SYB, e.g. SYBINT4. Program Variable Datatype Defines a C variable in a machine-independent way. Because a C defines its int type according the CPU architecture, it may have 2, 4, 8, or some other number of bytes. A DBINT on the other hand, is guaranteed to be 4 bytes and, as such, assuredly will hold any value returned by the server from a T-SQL INT column. These all begin with DB, e.g. DBREAL. Bind Type Prescribes a conversion operation. Indicates to dbbind() the Program Variable Datatype defined by the target buffer. Sybase and Microsoft call this the vartype. These all end with BIND, e.g. STRINGBIND. Typically it's more convenient to have &dblib; convert the data into the desired form. The function that does that is dbind(). So: after fetching the metadata, and before fetching the data, we usually prepare the bound columns. Fetching Data Data-fetching functions Type Regular rows Compute rows Return status OUTPUT parameters Meta dbnumcols() dbnumcompute(), dbnumalts(), dbaltop(), dbbylist() dbhasretstatus() dbnumrets() Binding dbbind(), dbnullbind() dbaltbind(), dbanullbind() dbretstatus() none Native dbdatlen(), dbdata() dbadlen(), dbalttype(), dbaltutype(), dbaltlen(), dbadata() none dbretdata(), dbretlen(), dbretname(), dbrettype()
The paradigm may now perhaps be clear: Query, fetch results, bind columns, fetch regular rows, fetch compute rows, fetch stored procedure outputs. Repeat as necessary. Putting it all together Step Function Once Per Many Times Per Query dbsqlexec() Query Program Fetch metadata dbresults() SQL statement Query Prepare variables dbbind() Column Statement Fetch regular data dbnextrow() Row Statement Fetch compute data dbnextrow() Compute column Statement Fetch output parameters dbretdata() output parameter Stored procedure Fetch return status dbretstatus() Stored procedure Program
Fetch All Rows! &dblib; doesn't insist every column — or even any column — be bound or otherwise retrieved into the application's variables. There is, however, one absolutely crucial, inflexible, unalterable requirement: the application must process all rows produced by the query. Before the DBPROCESS can be used for another query, the application must either fetch all rows, or cancel the results and receive an acknowledgement from the server. Cancelling is beyond the scope of this document, so for now fetch all rows. Now, at last, some sample code that fetches data. In the interest of simplicity, we don't bind anything except regular rows. Sample Code: &dblib; Fetch Results while ((erc = dbresults(dbproc)) != NO_MORE_RESULTS) { struct COL { char *name; char *buffer; int type, size, status; } *columns, *pcol; int ncols; int row_code; if (erc == FAIL) { fprintf(stderr, "%s:%d: dbresults failed\n", options.appname, __LINE__); exit(1); } ncols = dbnumcols(dbproc); if ((columns = calloc(ncols, sizeof(struct COL))) == NULL) { perror(NULL); exit(1); } /* * Read metadata and bind. */ for (pcol = columns; pcol - columns < ncols; pcol++) { int c = pcol - columns + 1; pcol->name = dbcolname(dbproc, c); pcol->type = dbcoltype(dbproc, c); pcol->size = dbcollen(dbproc, c); if (SYBCHAR != pcol->type) { pcol->size = dbwillconvert(pcol->type, SYBCHAR); } printf("%*s ", pcol->size, pcol->name); if ((pcol->buffer = calloc(1, pcol->size + 1)) == NULL){ perror(NULL); exit(1); } erc = dbbind(dbproc, c, NTBSTRINGBIND, pcol->size+1, (BYTE*)pcol->buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbbind(%d) failed\n", options.appname, __LINE__, c); exit(1); } status);]]> if (erc == FAIL) { fprintf(stderr, "%s:%d: dbnullbind(%d) failed\n", options.appname, __LINE__, c); exit(1); } } printf("\n"); /* * Print the data to stdout. */ while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS){ switch (row_code) { case REG_ROW: for (pcol=columns; pcol - columns < ncols; pcol++) { char *buffer = pcol->status == -1? "NULL" : pcol->buffer; printf("%*s ", pcol->size, buffer); } printf("\n"); break; case BUF_FULL: assert(row_code != BUF_FULL); break; case FAIL: fprintf(stderr, "%s:%d: dbresults failed\n", options.appname, __LINE__); exit(1); break; default: printf("Data for computeid %d ignored\n", row_code); } } /* free metadata and data buffers */ for (pcol=columns; pcol - columns < ncols; pcol++) { free(pcol->buffer); } free(columns); /* * Get row count, if available. */ if (DBCOUNT(dbproc) > -1) fprintf(stderr, "%d rows affected\n", DBCOUNT(dbproc)); /* * Check return status */ if (dbhasretstat(dbproc) == TRUE) { printf("Procedure returned %d\n", dbretstatus(dbproc)); } } dbclose(dbproc); dbexit(); exit(0); } Data-fetching Notes As soon as dbresults() reports SUCCESS, the row's metadata are available. &dblib; columns start with 1. dbcollen() returns the sizeof the native data (e.g. 4 bytes for a T-SQL INT). We'll use dbbind() to convert everything to strings. If the column is [VAR]CHAR, we want the column's defined size, otherwise we want its maximum size when represented as a string, which FreeTDS's dbwillconvert() returns (for fixed-length datatypes). For IMAGE data, we need to multiply by 2, because dbbind() will convert each byte to a hexadecimal pair. The example program will report an error with IMAGE data. NTBSTRINGBIND null-terminates the character array for us. NTB might perhaps stand for null terminating byte. A zero-length string is not a NULL! dbnullbind() arranges for the passed buffer to be set to -1 whenever that column is NULL for a particular row. Each time dbnextrow() returns REG_ROW, it has filled the bound buffers with the converted values for the row. Computed rows are left as an exercise to the reader. Messages and Errors Errors may originate on the server or in the library itself. The former are known as messages (because they are: they arrive as messages from the server); the latter are termed errors. Their handling is a little intimidating. It requires writing and installing a callback function (whose parameters are predefined by &dblib;), and thinking about how to handle different types of errors. Kinds of Errors Messages Messages arise because the server has something to say. Just one more way in which databases differ from files.. They usually describe some problem encountered executing the SQL. Perhaps the SQL refers to a nonexistent object or attempted to violate a constraint. But they can also be benign, indicating for instance merely that the default database has changed. Errors Errors arise either because the application has misused &dblib; in some way — say, passed a NULL DBPROCESS pointer or tried to issue a query while results were pending — or because some trouble cropped up in communicating with the server (couldn't find it, say, or didn't hear back from it). Why these two require distinct handling is lost in the mists of time. But it does help to keep them distinct in your mind, especially while reading the documentation. To have &dblib; use your handler, pass its name to the appropriate dberrhandle() or dbmsghandle() function immediately after calling dbinit(). Sample Code: &dblib; Error and Message handlers int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line) { enum {changed_database = 5701, changed_language = 5703 }; if (msgno == changed_database || msgno == changed_language) return 0; if (msgno > 0) { fprintf(stderr, "Msg %ld, Level %d, State %d\n", (long) msgno, severity, msgstate); if (strlen(srvname) > 0) fprintf(stderr, "Server '%s', ", srvname); if (strlen(procname) > 0) fprintf(stderr, "Procedure '%s', ", procname); if (line > 0) fprintf(stderr, "Line %d", line); fprintf(stderr, "\n\t"); } fprintf(stderr, "%s\n", msgtext); if (severity > 10) { fprintf(stderr, "%s: error: severity %d > 10, exiting\n", options.appname, severity); exit(severity); } return 0; } int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr) { if (dberr) { fprintf(stderr, "%s: Msg %d, Level %d\n", options.appname, dberr, severity); fprintf(stderr, "%s\n\n", dberrstr); } else { fprintf(stderr, "%s: DB-LIBRARY error:\n\t", options.appname); fprintf(stderr, "%s\n", dberrstr); } return INT_CANCEL; } Handlers are always called before the function that engendered them returns control to the application. Error Handling Notes When first writing a handler, pay careful attention to the precise type of each parameter. Only by carefully matching them will you convince a modern C compiler that the address of your function is of the type accepted by dbmsghandle(). Back in K&R days, that wasn't such a problem. But there were other problems, some much worse. Some messages don't convey much, as though the server gets lonely sometimes. You're not obliged to print every one. Severities are defined in the server documentation, and can be set by the T-SQL RAISERROR statement. Message handlers always and only ever return zero. When first writing the handler, pay careful attention to the precise type of each parameter. Only by carefully matching them will you convince a modern C compiler that the address of your function is of the type accepted by dberrhandle(). If that advice sounds familiar, it's because it bears repeating. Some messages are so severe they provoke &dblib; into calling the error handler, too! If you have both installed — and of course you do, right? — then you can skip those lacking an error number. While INT_CANCEL is the most common return code, it's not the only one. For one thing, the error handler's return code can control how long &dblib; keeps retrying timeout errors. See the documentation for details. No matter what the error handler says or does, it can't remedy the error. It's still an error and usually the best that can happen is that the function will return FAIL. The exception is timeout conditions, when the handler can stave off failure by requesting retries. You may be asking yourself, OK, fine, I can print the error message. But what if I want to communicate something back to line in my program where the error occurred? How to do that? First of all, remember the calling function — that's your application — will learn of an error from the return code. If it needs more detail, though, there are two ways to pass it. Set a global variable. Use setuserdata() and getuserdata(). If your application is written in C++, you may be tempted to use throw(). Don't! Your handler is a C function and, more important, it's an extension of &dblib;. You can put a throw() in your handler and it will compile. But when it executes, it's going to rip through &dblib;'s stack. Your application will be unuseable at that point, if it doesn't cause a segment fault. Last Remarks We've reached the end of our &dblib; tour. The almost 300 lines of C above constitute program with these features: Sample Code features</> <listitem><para>Accepts command-line parameters and SQL.</para></listitem> <listitem><para>Checks for errors and server messages.</para></listitem> <listitem><para>Processes any number of results..</para></listitem> <listitem><para>Prints results in columns of suitable widths.</para></listitem> </itemizedlist> There are things it doesn't do, in the name of simplicity. <itemizedlist><title> Sample Code nonfeatures</> <listitem><para>No BCP (bulk copy) mode</para></listitem> <listitem><para>No RPC (remote procedure call) mode, preventing it from retrieving output parameters.</para></listitem> </itemizedlist> Your humble author hopes you found it worthwhile. Happy Hacking.</para> </sect2> </sect1> </chapter> <!-- ////////////////// CHAPTER /////////////////////// --> <chapter id="acknowledgments"> <title>Acknowledgments Codesmyths Many people, too many to mention, have contributed patches and located bugs. The primary names are: Brian Bruns (brian@bruns.org) Started this crazy thing Mihai Ibanescu (misa@dntis.ro) GNUified the packet Gregg Jensen (greggj@savvis.com) Message handlers and extra datatype support and some sybperl stuff James K. Lowden (jklowden@schemamania.org) Wrote most of the documentation. Helped out here and there. Steve Murphree (smurph@smcomp.com) Added more ODBC functionality. Arno Pedusaar (psaar@fenar.ee) Donated his TDS4.2 code to the cause Mark Schaal (mark@champ.tstonramp.com) Cleaned up message handling, more datatype support, bug fixes Craig Spannring (cts@internetcds.com) Wrote the JDBC and DBI drivers Bill Thompson (thompbil@exchange.uk.ml.com) Completer of the &dblib; bcp API and author of freebcp. Frediano Ziglio (freddy77@gmail.com) Extended the ODBC library, and added many, many fixes and enhancements to libtds. Contributors This user guide owes at least 100 words each to the following people. Brian Bruns James Cameron Allen Grace James K. Lowden Bill Thompson Frediano Ziglio On Linkers &freetds; is a library, obviously, its functions invoked by an application. How the application finds the library can be mysterious. In the interest of making &freetds; easier to use, this appendix discusses how it all works. This appendix focusses on using &freetds; in your application. It isn't intended to help in building &freetds;, although the background information it provides might be useful.
What is a C function? A C function is a named bit of code. A C compiler recognizes function names in source code by parsing the C language. When it encounters a function name, it looks for a definition for the function — i.e. actual code implementing it — in the current file. If it finds one, it creates machine instructions to push any parameters on the stack, jump to the named address, and clear the stack after the functions returns. If it doesn't find one, it shrugsYou have to watch carefully. Modern compilers shrug quickly. and adds that name to the list of names to be resolved later. We'll get to what that means in a minute. The compiler's job ends where the linker's begins. Compiler's job Convert source code into object code Put in jumps to defined functions Create a list of defined functions, and their addresses Create a list of undefined functions The nm utility displays function names. Here are the ones defined by bsqldb.c (in bsqsldb.o): $ nm bsqldb.o | grep -wi t 0000000000000000 T err_handler 0000000000000270 T get_login 00000000000001d0 t get_printable_size 0000000000000940 T main 00000000000000a0 T msg_handler 00000000000007d0 t next_query 00000000000006c0 t set_format_string 0000000000000080 t usage GNU nm marks with a lower-case letter functions that are locally defined, not intended to be used outside the file. The C programmer marked those functions static. Note how closely the source code corresponds to the object code: $ grep ^static src/bsqldb.c static int next_query(DBPROCESS *dbproc); static void print_results(DBPROCESS *dbproc); static int get_printable_size(int type, int size); static void usage(const char invoked_as[]); static int set_format_string(struct METADATA * meta, const char separator[]); (Order doesn't matter. It's a set, not a list.) Here are some functions used, but not defined, by bsqldb.o: $ nm bsqldb.o | grep -w U | head U __assert_fail U __ctype_b_loc U __errno_location U __strdup U __xpg_basename U asprintf U calloc U dbaltbind U dbaltcolid U dbaltlen Two things to note. First, the functions defined by bsqldb.o have addresses, and undefined functions don't. Second, only the name identifies the function. It's been that way since about 1978, and it's one reason C libraries are so useful: to find a function, the tool need only resolve the name, i.e. convert the name into an address. The caller (the programmer, really) has to know the function's inputs and semantics (how it behaves), but the tool's job is bone simple. Which turns out to be quite handy.
What is a C library? A C library is a set of named functions, for example dbinit() or SQLConnect(). Or, for that matter, fopen(3)The Unix convention is to put in parentheses behind the name the section of the manual in which the function is documented. &freetds; functions don't get numbers because they're not in the manual. Yet. . Libraries come in two flavors: static and dynamic.
Static libraries Static libraries (also known as archives) have been around as long as C itself. Like a .zip file, they're just a bag of object files — containing functions, of course — with a table of contents in front giving the address of each nameOr, depending on how you look at it, the name of each address.. Static libraries are created from object files using a librarian utility of some kind. One such programs is ar, for archive. Static libraries are part of the build environment. Functions in static libraries are joined to a program's main module by a static linker at build time to produce an executable program. The executable incorporates the libraries' object code into its own body, making it completely self-sufficient.
Dynamic libraries Dynamic libraries are the new kid on the block, as these things go, arriving on the Unix scene circa 1985. Like a static library, a dynamic library is a collection of functions with a table of contents. They are referenced at build time to give the executatble information about how they will eventually be used, but they aren't used until run time. Dynamic libraries are part of the run-time environment. When a program is run, the run-time linker finds the dynamic libraries needed by the program, finds the addresses of the required functions, and assembles a runable image in memory. Missing libraries and/or missing functions — or the wrong versions of them — can lead to head-scratching and other amusing behavior. In Windows® dynamic libraries are called dynamic link libraries (DLLs). In Unix they're normally called shared objects. But they're roughly the same thing. What about <literal>.h</literal> files? C header files include functional prototypes, declarations (not definitions) of functions. Functional prototypes describe to the compiler each function's parameters, allowing the compiler to confirm that the function is being called correctly. Most of the functions declared in header files are implemented in libraries. However, there's no mechanical or automatic relationship between the functional prototypes in the header files and their implementation in a library. The .h file is maintained by hand, by the programmer, and is used to generate a library. The header file and associated library are distributed and installed together (one hopes), but correct installation and subsequent use by the compiler & linker require human beings to keep track of the pair. Failure to do so leads to interesting development and even run-time problems, especially with libraries whose functions' parameters change from version to version. For example, imagine a function f(int g) defined in library libf.so and declared in f.h. In a later version of libf.so, the function's parameter is changed to use a pointer, f(int *p), and f.h is likewise updated. Possible errors that cannot be prevented by the linker include: An old program could use the new library. Probably the integer it passes will be interpreted as an out-of-bounds address, resulting in a segmentation violation. A new program could use the old library, passing an address that the library interprets as an integer. Hillarity ensues. Existing source code could be compiled using the old header file but linked to the new library. If you've never done that, give it time. These errors are possible because C functions are identified to the linker by name only. On the upside, that makes the tools simple and easy to implement and, by the same token, simplifies the use of C libraries by other languages. The downside is that the work of ensuring that the right libraries are used becomes an administrative task instead of a technical one.
Checking if a Library Provides a Function A linker, any linker, knits together object files (some of which may be in libraries) such that every function needed by the program has a definition. If the linker fails to locate a definition for even one function, it will fail and the program will not run. Returning to bsqldb.o, we can use nm to see which functions are unresolved, and determine whether or not a particular library contains them. We'll ignore the symbols that start with an underscore, marking them per the C standard as being provided by the implementationWhy and how leading underscores enter into this discussion is just one more example of arcane historical practices one needs to know to master the subject. For our purposes, though, it's enough to know that implementation-provided functions like these — functions provided by the C standard library — often have an underscored prepended. , and focus on the last five in this abbreviated list. Some unresolved functions in <filename>bsqldb.o</filename> asprintf basename Normally provided by the standard C library, but if not by &freetds;'s replacements library: $ nm /usr/lib/libc.a | grep -w T | grep -E 'asprintf|basename' 0000000000000000 T _basename 0000000000000000 T _asprintf calloc Provided by the standard C library: $ nm /usr/lib/libc.a | grep -w T | grep calloc 0000000000004240 T calloc dbaltbind dbaltcolid Provided by &dblib;: $ nm libsybdb.a | grep -Ew 'dbaltbind|dbaltcolid' 0000000000007140 T dbaltbind 0000000000003590 T dbaltcolid Although these examples refer to static libraries, nm works just as well with dynamic libraries, too. There are other tools besides nm. Windows®, for instance, has dumpbin, and the GNU bintools include objdump.
How Dost Thy Linker Link? Now at last we come to how the linker performs its magic. Once again the discussion divides between static and dynamic linking.
Static Linker Static linking happens at build time. Object files are collected together; a distinct list of all function names is created, and the linker is tasked with finding a definition for each one. Different linkers have different command-line options to support OS-specific features. This document isn't intended to teach how to use any particular linker. Our task here is to understand the principles involved, so that you can apply them to your particular situation. The static linker needs three kinds of information: Static linker inputs Object modules to be linked, including libraries Locations of libraries Search order
Knitting together the object modules The static linker merges your object files into one executable. Your project's object files may refer freely (usually) to each other's functions, and the linker will match them up. It will catenate them together, compute every function's offset from the start of the executable, and replace every function reference with the actual address needed for the executable it's constructing. For library functions, definitions are copied from the library and appended to the output file (executable). The placeholder addresses left by the compiler are similarly replaced by offsets.
Specifying libraries An application programmer using &freetds; will need to mention the name fo the &freetds; library being used. Failure to do so will provoke the dread undefined reference linker error: Missing library name $ gcc -o bsqldb bsqldb.o bsqldb.o: In function `get_login': ../../../src/apps/bsqldb.c:816: undefined reference to `dblogin' ../../../src/apps/bsqldb.c:823: undefined reference to `dbsetlname' ../../../src/apps/bsqldb.c:874: undefined reference to `dbsetlname' ../../../src/apps/bsqldb.c:884: undefined reference to `dbsetlname' ../../../src/apps/bsqldb.c:889: undefined reference to `dbsetlname' …
Finding libraries Specifying the library is necessary but may be insufficient. The linker may need to be told where to look for the library. This is often the case for the application programmer using &freetds; because the &freetds; libraries may be installed in a location not on the linker's default search path. Linkers are usually pretty blunt about missing libraries: Library not found $ gcc -o bsqldb bsqldb.o -l sybdb ld: cannot find -lsybdb Order matters. Linkers tend to be fussy about library search order, some more than others. It's good practice to tell the linker to search project libraries first, third-party libraries (e.g. iconv or kerberos) next, and finally system libraries.
Dynamic Linker The dynamic linker — also known as the runtime linker — is, like the rest of dynamic linking, more complicated than its static counterpart. Whereas it's impossible even to generate an executable with missing static function references, an executable that uses dynamic libraries depends on the runtime environment to have its references satisfied. When a dynamically linked application is launched, the OS invokes the runtime linker to resolve any undefined references. Much as the static linker does, the runtime linker consults a list of dynamic libraries along its configured search path. The names of the libraries to search for are embedded in the executable. Sometimes, not always, the search path is found in the executable too. Usually any embedded path can be overridden.
Information in the executable Exactly what information is in the executable and how to display it depends on the format of the executable. Different OSes use different formats and most Unix derivatives actually support at least two. The most commonly encountered format for the &freetds; programmer is the ELF format. In the interest of your time and mine, that's the one we'll examine here. The GNU bintool utility readelf displays the information in the executable that is input to the runtime linker: $ readelf -d src/apps/.libs/bsqldb Dynamic section at offset 0x6028 contains 20 entries: Tag Type Name/Value 0x0000000000000001 (NEEDED) Shared library: [libsybdb.so.5] 0x0000000000000001 (NEEDED) Shared library: [libpthread.so.0] 0x0000000000000001 (NEEDED) Shared library: [libc.so.12] 0x000000000000000f (RPATH) Library rpath: [/usr/pkg/lib:/usr/local/lib] … What is this telling us? First, the bsqldb executable uses three shared libraries, namely sybdb for &dblib;, pthread for POSIX threads, and c, the C standard library. The runtime linker is going to have to find those somewhere, and it's going to use only those libraries to resolve unresolved references in the executable. Second, readelf displays the RPATH. The runtime linker searches for the required dynamic libraries in the directories listed in the RPATH, if extant. The RPATH is placed in the executable by the static linker. It can be thought of as a hint from the application builder to the system administrator. If an executable is built with an appropriate RPATH, the runtine linker will have all the information it needs to find the required libraries.
Information outside the executable Runtime linkers differ. The advice and observations that follow apply in many situations, but not all. The best way to know how yours works is to consult your system's documentation. RTFM! The NetBSD and GNU linkers both (as of this writing on machines used by the author) honor a configuration file and environment variables. They also have compiled-in default search locations. At a minimum, the default is /usr/lib. Sometimes a configuration file extends this to /usr/local/lib. The primary environment variable is LD_LIBRARY_PATH. On some systems this overrides the RPATH in the executable. In others it doesn't. Where ineffective, specific libraries (not their paths) can be forceably used with LD_PRELOAD.
Displaying what the Runtime linker will do The ldd(1) shows which dynamic libraries an executable requires and where, if at all, they'll be found: $ ldd $(command -v bsqldb) /usr/local/bin/bsqldb: -lc.12 => /usr/lib/libc.so.12 -lpthread.0 => /usr/lib/libpthread.so.0 -lsybdb.5 => /usr/local/lib/libsybdb.so.5 Important to understand: ldd is not figuring out this information by itself. It just reports the results of its interrogation of the runtime linker. As the configuration of the runtime linker is changed, so changes the output of ldd.
A Word about Windows® WIndows executables use the older COFF format, which has no provision for an RPATH. The runtime linker searches the PATH instead, after some built-in locations that usually include the current working directory. Neither ldd nor any similar utility is included in the basic product. It has been said that Unix is for programmers and Windows is for users, and perhaps that roughly describes the intention. But the Unix features listed above — RPATH and ldd — as well as a canonical filesystem hierarchy and dynamic library versioning, all promote a better user experience. Because of them, the problem of DLL conflicts in Windows hardly exists in Unix. Yet they are neither new nor secrect nor patented nor complicated; Microsoft could have adopted them years ago (as Apple finally did). We therefore know that the 20-year old phenomemon known as “DLL hell” is not inevitable, but a choice signifying nothing so much as Microsoft's indifference to its customers.
Advice for the lazy To avoid tinkering with your runtime linker, embed an RPATH in your executable commensurate with its intended runtime environment. If ldd doesn't show the libraries you want, or some are not found, use readelf to see which libraries are used and the RPATH. Relink with a better RPATH if needed. When testing with new libraries, use LD_PRELOAD to override the default, taking care that the semantics haven't changed.
Keep in Mind The compiler's job ends on the last line of each source code file. A header file describes a function for the compiler, not the linker. The linker, static or runtime, uses only the function's name to resolve references. Function parameters and semantics are invisible to it. The programmer and, to a lesser degree, the sysadmin direct the choice of which library to link to an executable. A missing function will prevent execution. A wrong function will promote wrong execution. Don't do that.
The <filename>interfaces</filename> File The interfaces file is retained for compatibility with Sybase environments. It is recommended that new users use the freetds.conf format instead. Where it came from Under Sybase OpenClient there is a file called interfaces that defines servers available to the software. &freetds; inherited this file structure with minor alterations. The interfaces remains supported for backward compatibility, and for those running in a mixed &freetds;/Sybase environment. The interfaces is not read by &freetds; unless it does not find &freetdsconf;. Note also that make install will install a skeleton &freetdsconf;, which you'll have to remove if you want to use interfaces instead. Where it goes Anywhere. The SYBASE environment variable must contain the location of interfaces; that is how &freetds; will find it. What it does The interfaces file aliases a servername to the hostname and port number of the servername's machine. When &freetds; receives a request to connect to a database server, it looks up the servername in interfaces. There, it finds the machine name (or address) and port number to connect to, that is, the port where the database server is listening. How's that again? The interfaces file sometimes trips people up. It seems innocuous enough, but it's also a pretty good example of it's easy if you know how. Keep in mind: The servername is the name of the database server. When a database client specifies the name of the server to connect to, it's the servername that is used. The host name is the name of the host (machine) where the database server is running. It has an IP address, and in almost any environment, you can ping the machine name to see if you've got it right. After it uses the servername to look up the host name, &freetds; will do the same thing ping does to get the IP address of the machine to connect to. Finally, the port number is frequently overlooked. From the network's point of view, knowing the IP address without the port number is a little like knowing the address of an apartment building without knowing the apartment number. In both cases, it will be hard to find what you came for. Make sure you know the port number, and that it's correctly entered in the interfaces file. What it looks like The format of the interfaces file is borrowed directly from that used by Sybase on Unix platforms (Windows has a different format). Additionally, we have overloaded one of the fields to add the ability to set the protocol version. An example interfaces file looks like this. An <filename>interfaces</filename> file example myserver query tcp 4.2 127.0.0.1 4000 master tcp ether 127.0.0.1 4000 The entry starts with the servername beginning in the first column (no whitespace preceding it). Following the servername are one or more services lines which must be indented with whitespace. &freetds; uses only the query line, although others may be present to retain compatibility with Sybase. The fields in the services lines are as follows. Services Line Name Example Meaning service query The only supported service transport tcp The transport protocol to use. Only tcp is supported by &freetds;. physical 4.2 Historically this field referred the physical/datalink layer, however it appears to simply a comment field. Therefore, &freetds; optionally uses it to specify the protocol version to connect with. hostname/IP 127.0.0.1 The hostname or IP address where the SQL Server resides. port 4000 The TCP port where the SQL Server is listening.
In the example above, the hostname was entered as an IP address. It needn't be; it could just as well be a name. &freetds; can use a name rather than an address; it will just let the network (specifically, the resolver get the address. About Unicode, UCS-2, and UTF-8 For better or worse, &freetds; brings the otherwise innocent programmer into contact with the arcane business of how data are stored and transported. &freetds; is a data communications library that of course connects to databases, which are charged with storing information in a way that is neutral to all architectures and languages. On the surface, that might not seem very complex, even worth discussing. Under the surface, things are not so simple.
<acronym>ASCII</>: What everyone knows The world we are all familiar with, programmingwise, is ASCII. Our email (mostly), our text files, our web pages (mostly), all use ASCII to represent English (or English-like) text. Perhaps because ASCII czyborra.com is offline at the time of this writing (December 2003). It contained good information, so it's still included here, in case it comes back to life. was standardized back in 1972 by the ISO, it seems like the natural way to store information. But let's look under the hood a little bit, and examine our assumptions. Our so-called text files are nothing special, nothing but a little agreement we enter into with our operating system. The only reason we can read them with cat or vi is that the operating system and its tools are in on the agreement. A file is only a stream of bytes, after all, no more text than an executable. The only thing distinguishing a text file from any other, is our understanding to treat it like one. We agree that the number 65 will represent the letter A, 66, B, and so on, 127 values in all. See man ascii for further details. The important thing to understand is that the designation of 65 for A and so on is a choice. It's an encoding standard, made necessary by the old simple fact that computers store numbers, not letters. ASCII is so ubiquitous these days that it's hard sometimes to remember there was a time when it was but one of a set of competing encoding standards. Others you probably have heard of include EBCDIC and the Baudot systems, but they are by no means the only historical alternatives, nor the only modern ones.
The <acronym>ASCII</> Compact UNIX® and unix-like systems bought into ASCII big time. Program code, filenames, string constants (and variables), configuration files, everything but everything is encoded in ASCII. Practically every utility, command, and library assumes the text data will be ASCII. At the dawn of the 21st century, there is widespread recognition that ASCII will no longer suffice, but the art of upgrading all the computers and computer programmers is, well, an unfinished work.
ISO 8859: What everyone would like to forget ASCII won, it would seem, but the race goes not to the swift. ASCII has many limitations, the most egregious of which is, it's not much good for anything besides English. It encodes all the letters and punctuation (almost) of the English alphabet, but is useless for German, Russian, and Greek, to say nothing of Chinese. ASCII assigns one byte to every character, but deals with only 7 of the 8 available bits, the range 0-127 (with the high bit always zero). Demand for computers that could display and print languages besides English — even English with em dashes and cent (¢) signs — arrived soon enough, with the Marketing Department way out in front of the propeller heads. The predictable result was an array of 8-bit ASCII encoding standards for a wide variety of alphabets. Eventually, they were standardized (or at least enumerated and documented) by the ISO. These are what our friendly database vendors are referring to when they talk about character sets. More information on this subject can be found at webreference.com. The upshot is, there is no uniform standard, no agreement on the meaning of a byte, particularly if that byte's value is greater than 127. Let's say your client machine sends HELLO and your database stores it as 72 69 76 76 79. When another client retrieves that value, it will convert it into human-readable form by applying an encoding standard. If everything's tightly wrapped, it will use the very same encoding that your database used (and the same one you had in mind when you sent it), and that client will also see HELLO. If things are not so tightly wrapped but that client is fortunate enough to be using a similar standard to what you were using, say, ISO 8859-1, he'll still see HELLO. Most languages based on the Roman alphabet can be represented by ISO 8859-1, and are thus interchangeable. Beyond that, things get quickly messy. Greek clients, for one, are not so lucky: there are three ISO 8859 standards for Greek, all mutually incompatible. For more information, see ISO 8859 Alphabet Soup. Roman Czyborra's site is very informative; take your time there if you don't want your head to spin. Database servers need to know what encoding standard to employ, too. It's not obvious at first, but notions like uppercase and lowercase, trailing blanks, and collation rules all depend on what letter is meant by what number. (Collation even depends on what culture is interpreting the letters.)
Unicode: East meets West ASCII and its 8-bit cousins are on the way out, and with them the assumption that a character can be represented by a single byte. The new kid on the block is Unicode, similar to but not precisely the same as ISO 10646. Unicode (despite its name) is a set of standards. The most widely implemented is the 16-bit form, called UCS-2. As you might guess, UCS-2 uses two bytes per character, allowing it to encode most characters of most languages. Because most is far from all, there are nascent 32-bit forms, too, but they are neither complete nor in common use. In the same sense that 7-bit ASCII was extended to 8 bits, Unicode extends the most prevalent 8-bit ASCII, ISO 8859-1, to 16 and 32 bits. The first 256 values remain in Unicode as in ISO 8859-1: 65 is still A, except instead of being 8 bits (0x40), it's 16 bits (0x0040). Unlike the 8-bit extensions, Unicode has a unique 1:1 map of numbers to characters, so no language context or character set name is needed to decode a Unicode string. UCS-2 is the system employed by Microsoft NT-based systems. Microsoft database servers store UCS-2 strings in nchar and nvarchar datatypes. Microsoft also designed version 7.0 (and up) of the TDS protocol around UCS-2: all metadata (table names and such) are encoded according to UCS-2 on the wire.
Unicode's Pluses and Minuses You will read from time to time that Unicode is not perfect. Surprise, surprise: it's true. From a linguistic point of view, Unicode is incomplete; in particular, UCS-2 is demonstrably too small (!) to hold all the forms of Chinese ideographs used over the centuries. (It is, however, quite useful and widely employed in representing modern Chinese.) Of more common concern to programmers are Unicode's technical problems, or rather, Unix's technical shortcomings vis-a-vis any encoding more complex than ISO 8859-x. The basic problem, from a programmer's perspective, is the ancient agreement Unix entered into 30 years ago, the ASCII Compact, alluded to earlier. Assumptions about ASCII are littered throughout Unix-like systems, beginning with C's convention of representing strings as arrays of characters ending in a zero. Returning to our HELLO example earlier, C will store HELLO as 72 69 76 76 79 0, in very nice ASCII. Many many parts of the operating system and its associated tools and applications will recognize that as a 5-letter word because it's terminated by a null (zero). In UCS-2 Unicode, though, that same HELLO uses 2 bytes for every character and becomes 72 0 69 0 76 0 76 0 79 0 0 0. Practically the whole OS will think that's a 1-letter word, H. Not a good thing. Even if every OS were magically rid of all ASCII assumptions and C strings, there would still be the problem of Endianism. Technical explanations on the subject are not hard to find. The long and short of it is, given a 16-bit integer (2 bytes), different hardware architectures will store the value differently. Asked to store our friend A, (0x41), for instance, a Sparc processor will put the least significant byte at the higher address (00 41) whereas an Intel processor will put it in the lower address (41 00). Put aside the questions of left, right, and wrong; architectures are a fact of life. Endianism shows up wherever integers are stored and retrieved in heterogeneous environments. The Unicode folks knew about Endianism, of course, and had to address it. A Unicode bytestream is supposed to begin with a byte-order mark. Needless to say, perhaps, many don't.
Unicode Transformation Format: UTF-8 The presence of nulls embedded in character data and of byte order issues make straight Unicode i.e., UCS-2 or UCS-4 hard to work with in a heterogeneous environment. Too many opportunities arise for the data to be truncated or misinterpreted, and too many systems would fail even to transmit such data. In short, when 16-bit data are thrust into a multi-architecture 8-bit world, it frequently bodes ill for the data. To answer that problem, to make Unicode transmissible and unambiguous to most machines, several transformation formats were adopted. Their goals were generally similar: to create a generally recognized format that would unambiguously and safely convey Unicode information between machines and across the Internet. To do that, they sought to remove nulls and endianism from the data stream. The most popular one — practically the only one used — is known as UTF-8. UTF-8 found wide acceptance for many reasons. UTF-8 represents any Unicode character as a combination of 1-4 bytes. The number of bytes required depends on the integer value of the Unicode character, and only one byte is used to represent the old ASCII range (0-127). UTF-8 does not use zero to represent any part of any character (except for the ASCII NUL). In consequence, UTF-8 is efficient with respect to space, has no endianism issues, and embeds no nulls. UTF-8 strings can be treated as plain old ASCII strings. These properties make UTF-8 data relatively easy for systems accustomed to processing ASCII data. Here's a small example showing the difference between UCS-2 and UTF-8. <quote>HELLO</quote> in UCS-2 and UTF-8 $ echo HELLO | iconv -f ascii -t UCS-2 | hexdump -C 00000000 00 48 00 45 00 4c 00 4c 00 4f 00 0a |.H.E.L.L.O..| 0000000c $ echo HELLO | iconv -f ascii -t utf-8 | hexdump -C 00000000 48 45 4c 4c 4f 0a |HELLO.| 00000006 $ echo HELLO | hexdump -C 00000000 48 45 4c 4c 4f 0a |HELLO.| 00000006 It is the similarity of the last two outputs that makes UTF-8 so attractive. It behaves like ASCII when ASCII's all that's needed. But it lacks ASCII's limitations. While UTF-8 solves many technical problems, it doesn't magically transform every ASCII-assuming system into a Unicode system. For example, to display Unicode data correctly — even Unicode data in UTF-8 format — the system still needs a suitable font. And it must distinguish the buffer size (and byte count) from the character count.
Unicode and FreeTDS Microsoft servers using TDS 7.0 and above (anything since SQL Server 6.5) transmit their data in UCS-2 format (16-bit integers). Because most applications linked to &freetds; are not prepared to deal with UCS-2 data, &freetds; can convert the data to something more acceptable, including ASCII. To do so, it employs an iconv library. &freetds; determines the server's encoding from the TDS protocol and information reported by the server (generally per connection, but in the case of TDS 7.1, per result set column). It discovers the client's encoding in &freetdsconf;. &freetds; will happily convert and convey your data in any single-byte format that iconv can provide. In practice, this normally means some form of ISO 8859-x or UTF-8. At some future time, &freetds; aims to support Unicode and other multi-byte character sets. It does not do so at the current time. Sybase servers, by the way, adhere to a server makes right policy: they transmit their data in whatever character set the client requested at login time. The list of available character sets is fairly short, but includes UTF-8. While FreeTDS could convert Sybase data streams as easily as it does Microsoft data streams, to our knowledge no one is doing so. The Sybase server can perform the conversion itself, making FreeTDS's capability in this regard largely redundant and irrelevant.
For further information UTF-8 and Unicode FAQ for Unix/Linux, by Markus Kuhn. As the man says, very comprehensive. ASCII: American Standard Code for Information Infiltration, by Tom Jennings. Everything you ever wanted know about ASCII, but didn't know whom to ask. A Brief History of Character Codes, by Steven J. Searle. Includes useful references. Unicode Home Page.
GNU Free Documentation License Version 1.1, March 2000 PREAMBLE The purpose of this License is to make a manual, textbook, or other written document "free" in the sense of freedom: to assure everyone the effective freedom to copy and redistribute it, with or without modifying it, either commercially or noncommercially. Secondarily, this License preserves for the author and publisher a way to get credit for their work, while not being considered responsible for modifications made by others. This License is a kind of "copyleft", which means that derivative works of the document must themselves be free in the same sense. It complements the GNU General Public License, which is a copyleft license designed for free software. We have designed this License in order to use it for manuals for free software, because free software needs free documentation: a free program should come with manuals providing the same freedoms that the software does. But this License is not limited to software manuals; it can be used for any textual work, regardless of subject matter or whether it is published as a printed book. We recommend this License principally for works whose purpose is instruction or reference. APPLICABILITY AND DEFINITIONS This License applies to any manual or other work that contains a notice placed by the copyright holder saying it can be distributed under the terms of this License. The "Document", below, refers to any such manual or work. Any member of the public is a licensee, and is addressed as "you". A "Modified Version" of the Document means any work containing the Document or a portion of it, either copied verbatim, or with modifications and/or translated into another language. A "Secondary Section" is a named appendix or a front-matter section of the Document that deals exclusively with the relationship of the publishers or authors of the Document to the Document's overall subject (or to related matters) and contains nothing that could fall directly within that overall subject. (For example, if the Document is in part a textbook of mathematics, a Secondary Section may not explain any mathematics.) The relationship could be a matter of historical connection with the subject or with related matters, or of legal, commercial, philosophical, ethical or political position regarding them. The "Invariant Sections" are certain Secondary Sections whose titles are designated, as being those of Invariant Sections, in the notice that says that the Document is released under this License. The "Cover Texts" are certain short passages of text that are listed, as Front-Cover Texts or Back-Cover Texts, in the notice that says that the Document is released under this License. A "Transparent" copy of the Document means a machine-readable copy, represented in a format whose specification is available to the general public, whose contents can be viewed and edited directly and straightforwardly with generic text editors or (for images composed of pixels) generic paint programs or (for drawings) some widely available drawing editor, and that is suitable for input to text formatters or for automatic translation to a variety of formats suitable for input to text formatters. A copy made in an otherwise Transparent file format whose markup has been designed to thwart or discourage subsequent modification by readers is not Transparent. A copy that is not "Transparent" is called "Opaque". Examples of suitable formats for Transparent copies include plain ASCII without markup, Texinfo input format, LaTeX input format, SGML or XML using a publicly available DTD, and standard-conforming simple HTML designed for human modification. Opaque formats include PostScript, PDF, proprietary formats that can be read and edited only by proprietary word processors, SGML or XML for which the DTD and/or processing tools are not generally available, and the machine-generated HTML produced by some word processors for output purposes only. The "Title Page" means, for a printed book, the title page itself, plus such following pages as are needed to hold, legibly, the material this License requires to appear in the title page. For works in formats which do not have any title page as such, "Title Page" means the text near the most prominent appearance of the work's title, preceding the beginning of the body of the text. VERBATIM COPYING You may copy and distribute the Document in any medium, either commercially or noncommercially, provided that this License, the copyright notices, and the license notice saying this License applies to the Document are reproduced in all copies, and that you add no other conditions whatsoever to those of this License. You may not use technical measures to obstruct or control the reading or further copying of the copies you make or distribute. However, you may accept compensation in exchange for copies. If you distribute a large enough number of copies you must also follow the conditions in section 3. You may also lend copies, under the same conditions stated above, and you may publicly display copies. COPYING IN QUANTITY If you publish printed copies of the Document numbering more than 100, and the Document's license notice requires Cover Texts, you must enclose the copies in covers that carry, clearly and legibly, all these Cover Texts: Front-Cover Texts on the front cover, and Back-Cover Texts on the back cover. Both covers must also clearly and legibly identify you as the publisher of these copies. The front cover must present the full title with all words of the title equally prominent and visible. You may add other material on the covers in addition. Copying with changes limited to the covers, as long as they preserve the title of the Document and satisfy these conditions, can be treated as verbatim copying in other respects. If the required texts for either cover are too voluminous to fit legibly, you should put the first ones listed (as many as fit reasonably) on the actual cover, and continue the rest onto adjacent pages. If you publish or distribute Opaque copies of the Document numbering more than 100, you must either include a machine-readable Transparent copy along with each Opaque copy, or state in or with each Opaque copy a publicly-accessible computer-network location containing a complete Transparent copy of the Document, free of added material, which the general network-using public has access to download anonymously at no charge using public-standard network protocols. If you use the latter option, you must take reasonably prudent steps, when you begin distribution of Opaque copies in quantity, to ensure that this Transparent copy will remain thus accessible at the stated location until at least one year after the last time you distribute an Opaque copy (directly or through your agents or retailers) of that edition to the public. It is requested, but not required, that you contact the authors of the Document well before redistributing any large number of copies, to give them a chance to provide you with an updated version of the Document. MODIFICATIONS You may copy and distribute a Modified Version of the Document under the conditions of sections 2 and 3 above, provided that you release the Modified Version under precisely this License, with the Modified Version filling the role of the Document, thus licensing distribution and modification of the Modified Version to whoever possesses a copy of it. In addition, you must do these things in the Modified Version: Use in the Title Page (and on the covers, if any) a title distinct from that of the Document, and from those of previous versions (which should, if there were any, be listed in the History section of the Document). You may use the same title as a previous version if the original publisher of that version gives permission. List on the Title Page, as authors, one or more persons or entities responsible for authorship of the modifications in the Modified Version, together with at least five of the principal authors of the Document (all of its principal authors, if it has less than five). State on the Title page the name of the publisher of the Modified Version, as the publisher. Preserve all the copyright notices of the Document. Add an appropriate copyright notice for your modifications adjacent to the other copyright notices. Include, immediately after the copyright notices, a license notice giving the public permission to use the Modified Version under the terms of this License, in the form shown in the Addendum below. Preserve in that license notice the full lists of Invariant Sections and required Cover Texts given in the Document's license notice. Include an unaltered copy of this License. Preserve the section entitled "History", and its title, and add to it an item stating at least the title, year, new authors, and publisher of the Modified Version as given on the Title Page. If there is no section entitled "History" in the Document, create one stating the title, year, authors, and publisher of the Document as given on its Title Page, then add an item describing the Modified Version as stated in the previous sentence. Preserve the network location, if any, given in the Document for public access to a Transparent copy of the Document, and likewise the network locations given in the Document for previous versions it was based on. These may be placed in the "History" section. You may omit a network location for a work that was published at least four years before the Document itself, or if the original publisher of the version it refers to gives permission. In any section entitled "Acknowledgements" or "Dedications", preserve the section's title, and preserve in the section all the substance and tone of each of the contributor acknowledgements and/or dedications given therein. Preserve all the Invariant Sections of the Document, unaltered in their text and in their titles. Section numbers or the equivalent are not considered part of the section titles. Delete any section entitled "Endorsements". Such a section may not be included in the Modified Version. Do not retitle any existing section as "Endorsements" or to conflict in title with any Invariant Section. If the Modified Version includes new front-matter sections or appendices that qualify as Secondary Sections and contain no material copied from the Document, you may at your option designate some or all of these sections as invariant. To do this, add their titles to the list of Invariant Sections in the Modified Version's license notice. These titles must be distinct from any other section titles. You may add a section entitled "Endorsements", provided it contains nothing but endorsements of your Modified Version by various parties--for example, statements of peer review or that the text has been approved by an organization as the authoritative definition of a standard. You may add a passage of up to five words as a Front-Cover Text, and a passage of up to 25 words as a Back-Cover Text, to the end of the list of Cover Texts in the Modified Version. Only one passage of Front-Cover Text and one of Back-Cover Text may be added by (or through arrangements made by) any one entity. If the Document already includes a cover text for the same cover, previously added by you or by arrangement made by the same entity you are acting on behalf of, you may not add another; but you may replace the old one, on explicit permission from the previous publisher that added the old one. The author(s) and publisher(s) of the Document do not by this License give permission to use their names for publicity for or to assert or imply endorsement of any Modified Version. COMBINING DOCUMENTS You may combine the Document with other documents released under this License, under the terms defined in section 4 above for modified versions, provided that you include in the combination all of the Invariant Sections of all of the original documents, unmodified, and list them all as Invariant Sections of your combined work in its license notice. The combined work need only contain one copy of this License, and multiple identical Invariant Sections may be replaced with a single copy. If there are multiple Invariant Sections with the same name but different contents, make the title of each such section unique by adding at the end of it, in parentheses, the name of the original author or publisher of that section if known, or else a unique number. Make the same adjustment to the section titles in the list of Invariant Sections in the license notice of the combined work. In the combination, you must combine any sections entitled "History" in the various original documents, forming one section entitled "History"; likewise combine any sections entitled "Acknowledgements", and any sections entitled "Dedications". You must delete all sections entitled "Endorsements." COLLECTIONS OF DOCUMENTS You may make a collection consisting of the Document and other documents released under this License, and replace the individual copies of this License in the various documents with a single copy that is included in the collection, provided that you follow the rules of this License for verbatim copying of each of the documents in all other respects. You may extract a single document from such a collection, and distribute it individually under this License, provided you insert a copy of this License into the extracted document, and follow this License in all other respects regarding verbatim copying of that document. AGGREGATION WITH INDEPENDENT WORKS A compilation of the Document or its derivatives with other separate and independent documents or works, in or on a volume of a storage or distribution medium, does not as a whole count as a Modified Version of the Document, provided no compilation copyright is claimed for the compilation. Such a compilation is called an "aggregate", and this License does not apply to the other self-contained works thus compiled with the Document, on account of their being thus compiled, if they are not themselves derivative works of the Document. If the Cover Text requirement of section 3 is applicable to these copies of the Document, then if the Document is less than one quarter of the entire aggregate, the Document's Cover Texts may be placed on covers that surround only the Document within the aggregate. Otherwise they must appear on covers around the whole aggregate. TRANSLATION Translation is considered a kind of modification, so you may distribute translations of the Document under the terms of section 4. Replacing Invariant Sections with translations requires special permission from their copyright holders, but you may include translations of some or all Invariant Sections in addition to the original versions of these Invariant Sections. You may include a translation of this License provided that you also include the original English version of this License. In case of a disagreement between the translation and the original English version of this License, the original English version will prevail. TERMINATION You may not copy, modify, sublicense, or distribute the Document except as expressly provided for under this License. Any other attempt to copy, modify, sublicense or distribute the Document is void, and will automatically terminate your rights under this License. However, parties who have received copies, or rights, from you under this License will not have their licenses terminated so long as such parties remain in full compliance. FUTURE REVISIONS OF THIS LICENSE The Free Software Foundation may publish new, revised versions of the GNU Free Documentation License from time to time. Such new versions will be similar in spirit to the present version, but may differ in detail to address new problems or concerns. See http://www.gnu.org/copyleft/. Each version of the License is given a distinguishing version number. If the Document specifies that a particular numbered version of this License "or any later version" applies to it, you have the option of following the terms and conditions either of that specified version or of any later version that has been published (not as a draft) by the Free Software Foundation. If the Document does not specify a version number of this License, you may choose any version ever published (not as a draft) by the Free Software Foundation. How to use this License for your documents To use this License in a document you have written, include a copy of the License in the document and put the following copyright and license notices just after the title page: If you have no Invariant Sections, write "with no Invariant Sections" instead of saying which ones are invariant. If you have no Front-Cover Texts, write "no Front-Cover Texts" instead of "Front-Cover Texts being LIST"; likewise for Back-Cover Texts. If your document contains nontrivial examples of program code, we recommend releasing these examples in parallel under your choice of free software license, such as the GNU General Public License, to permit their use in free software.