jul2005.tar

FreeTDS for Database Connectivity

Kevin M. Lyons

It has often been said that the world would be a nicer place for programmers and administrators if everyone used the same operating system, if only one database were needed, only one programming language... Let us pause for a moment while you stop laughing.

Sooner or later in your career, you will most likely need to handle communication between a Unix system and a Windows system, particularly in the world of databases. Indeed, Oracle and MySQL get quite a bit of press, but what about systems such as Sybase and Microsoft's SQL Server? There is a solution in the form of FreeTDS, the open source emulation of the Tabular Data Stream (TDS) protocol.

Our office has successfully used the FreeTDS libraries for C/C++, Perl, and PHP connectivity to MSSQL systems for Web-based applications since 1999. We discovered the project, which was begun by Brian Bruns, while doing work for the Nebraska Secretary of State's office and have continued using it in a variety of other systems including the State Electrical Division's online licensing and permitting.

FreeTDS for MSSQL/Sybase connectivity has gained popularity on a wide variety of platforms. So, in an effort to shed some light on these less-used, but still important, database systems, the following is a practical guide to the use of FreeTDS.

What Is TDS/FreeTDS?

The TDS protocol, which FreeTDS was built to emulate, is the underlying protocol used by both Sybase (the original developers) and Microsoft SQL Server for remote data transfer. For some information on the protocol itself, you can check out some basic documentation at:

http://www.freetds.org/tds.html

FreeTDS is a set of open source libraries that implement the TDS protocol and the two common APIs that sit on top of TDS. Those APIs are CT, which is only supported by Sybase, and DB, which is the preferred API for use with both Sybase and MSSQL. As FreeTDS has evolved, support for multiple languages has been added, as well as ODBC support for both iODBC and unixODBC.

Why Use FreeTDS?

The first and biggest question is whether FreeTDS is right for your project. The answer, as in most cases, is ... maybe.

Especially when dealing with applications based on C/C++, FreeTDS is a strong, well supported, and free library that allows easy connectivity to MSSQL and Sybase systems on any *nix type of platform. It's been tested across Solaris, Linux, HP, AIX, and several other platforms with few reliability issues; problems have been noted with the build process, however, especially on AIX and HP systems. Perl and PHP support are also well maintained and have no significant stability issues as of the writing of this article.

The major issues you may encounter will come from servers requiring TDS protocol 4.2 or 5.0, such as older Sybase and MSSQL systems. If you are dealing with a situation where upgrade is not an option, you may wish to consider an alternate solution, although FreeTDS can handle the job.

The primary concern is with endianness, or specifically how improper configuration on either end can cause misreported data in date, time, and money fields. These are especially widespread when dealing with Microsoft's SQL Server 7.0, which can mishandle endian issues unless Service Pack 3 or higher is installed.

Additionally through the years, there have been issues with MSSQL breaking and re-breaking how it handles endianness, so if you are going to be working in an environment where the database server is frequently upgraded (not just patched), you may be in for a bumpy ride. Fortunately, TDS protocol versions 7.0 and higher support automatic little-endian emulation, although older systems may have some serious problems.

The other main concern with 4.2 and 5.0 systems comes from varchar fields with a length of more than 255. The TDS protocol inherently limits varchars to 255 characters, so the fields, while correct on the MSSQL end, will not be correctly reported unless they are converted to text fields instead.

As a smaller, lesser problem, there is a conflict when using FreeTDS and the DBM library at the same time. Both define a dbopen() function, which can confuse things, especially in C/C++ and PHP. This can be worked around, as described later, but has caused confusion and irritation in some projects.

Starting Out with FreeTDS

If you are ready to work with the FreeTDS package, then you first need to grab the source from the FreeTDS Web site at:

http://www.freetds.org/

and use the "Software" link at the top of the page. Or, you can just head straight to:

http://www.freetds.org/software.html

This is the base C library install package on which the Perl and PHP implementations are built. A native (type 4) JDBC driver is also available courtesy of Craig Spanning and CDS Networks, however, that topic is outside the scope of this article.

The package install itself is the standard ./configure && make && make install type of scenario with the usual options. Note that if you wish to use the PHP install, run configure with the --enable-msdblib option. If you do not, you may have problems, especially with PHP systems compiled with DBM support, because both DBM and the DB layer of TDS define a dbopen() function. Once installation is complete, just recompile PHP using the --with-mssql=/path/to/freetds option and support will be enabled.

If you wish to use the Perl DBI implementation, it can be downloaded and installed with the usual perl Makefile.pl or obtained via CPAN with the module name of DBI::Sybase.

Configuration

FreeTDS uses a freetds.conf file for basic configuration options, with a fairly basic layout. You begin with setting your global configuration for all connections. The default setup for this should work, with one exception. Check for the following line under the [global] heading:

tds version = 4.2

The 4.2 protocol is rather outdated and not the preferred method for use on most systems. Table 1 provides a reference guide on which type of system supports which type of connection. It is also helpful to set up connection information about specific servers in this file. Options specified in a section for a specific server will override options in the global; thus, if you are like me and need to connect to several different types of servers, you can do it all within a single configuration file.

For example, say you wanted to connect to the database at foo.com and their new testing system at bar.com using an updated version of Sybase -- you could define these hosts in your freetds.conf as follows:

[foo]
  host = db.foo.com
  port = 1433
  tds version = 7.0

[bar]
  host = 10.0.0.1
  port = 1444
  tds version = 8.0

Then, when establishing connections, you could simply call your connect to foo or bar and have the appropriate information in place, and it could be updated as needed without code changes.

Support is also available for multiple freetds.conf files. When using the DB layer, you can use the dbsetconf() function to set a configuration file. If set, this takes priority and supersedes all other conf files. Otherwise the following steps are used (in this order) to determine which conf file to use:

1. Check for a FREETDSCONF environment variable.

2. Look for $HOME/.freetds.conf.

3. Look for /path/to/freetds/etc/freetds.conf.

For further details on this, you can find a sample configuration file, with associated documentation, showing setups to multiple types of connections with the source for this article (available from the Sys Admin Web site).

Connections

Now that the configuration is complete, we can review how to open connections to the TDS systems via applications.

In the C/C++ venue, there are two separate abstraction layers you can work with -- the CT and DB layers. ODBC connectivity is available as well, although it is outside of the scope of this article. The DB layer is the most robust and well-supported layer and is the preferred API according to the FreeTDS staff.

When connecting using the DB layer, you must first create your login record using the LOGINREC struct. A variety of macros have been provided to allow for easy setup. For example, the following code will establish a connection to the "foo" database listed in our previous example configuration:

LOGINREC *login;
DBPROCESS *dbproc;

/* insert various code here */
dbinit();

/* more code comes here */

login = dblogin();
DBSETLUSER(login, "mylogin");
DBSETLPWD(login, "mypasswd");   
dbproc = dbopen(login, "foo");

/* more code comes here */

To trace this code a little, the call to dbinit sets up the FreeTDS library accordingly, loading information from your configuration, etc. The call to dblogin() then allocates memory and initializes the login structure, while the DBSETLXXX() macros (see FreeTDS documentation for a complete listing) will establish which login parameters you want to use. Finally, the call to dbopen performs the actual login.

In Perl, you would use the normal DBI interface, like so:

use DBI;

# -- insert various code here

my $dbh = \
  DBI->connect("dbi:Sybase:server=foo;", $user, $pass);
if (!$dbh) {
                 # -- error handle here
}

# -- insert various code here

And finally, in PHP, you can use the native mssql_ functions to connect:

// insert various code here

putenv("FREETDSCONF='/path/to/freetds/conf'");

$dbh = mssql_connect("foo", $user, $pass);
if (!$dbh) {
                 // error handle here
}

// insert various code here

Note that for PHP you have the option of using mssql_pconnect for persistent connections as well. It's also advisable to note that if you want to actually use the freetds.conf file setup, you will need to put the location in the environment variable FREETDSCONF. Otherwise, it may or may not load depending on which version of PHP you are using.

Queries and Statements

When doing something like a simple select, you can use the dbcmd and dbsqlexec functions to submit the query, as follows (assume Col1 is an int, Col2 is a varchar, and Col3 is text):

       DBINT Col1;
       char  Col2[MAX_ROW2_LEN];
char  Col3[MAX_ROW3_LEN];

/* insert code here */

dbcmd(dbproc, "select Col1, Col2, Col3 from MyTable");
dbsqlexec(dbproc);

if (dbresults(dbproc) != SUCCEED) {
  /* no result set for the query, something bad happened */
} else {
                        if (dbbind(dbproc, 1, INTBIND, -1, \
                           (BYTE*)&Col1) != SUCCESS) {
                                         /* error handle here */
  }
  if (dbbind(dbproc, 2, STRINGBIND, -1, (BYTE*)Col2) != SUCCESS) {
    /* error handle here */
  }
  if (dbbind(dbproc, 3, STRINGBIND, -1, (BYTE*)Col3) != SUCCESS) {
    /* error handle here */
  }
  while (REG_ROW == dbnextrow(dbproc)) {
    printf("Col1 = %s, Col2 = %s, Col3 = %s", (int)Col1, Col2, Col3);
    fflush(stdout);
  }
       }

               /* insert code here */

In this code, the command is prepared with dbcmd (which checks for any SQL errors, etc.), then it is executed with dbsqlexec. We check whether we actually got a result set from the query with dbresults (and, yes -- "no rows" is a valid result set!).

Once all this is done, we "bind" each of the columns to their specific type, which is what the calls to dbbind handle. This means that you're interpreting data from a specific column in a specific fashion.

Finally, to loop through the results, we use dbnextrow. If that doesn't equal REG_ROW, then we don't have a row and we can exit.

In Perl, we accomplish the same thing with the following code:

# -- insert code here

my $stmt = $dbh->prepare("select Col1, Col2, Col3 from MyTable");
if (!$stmt) {
# -- print error here via $DBI::errstr
}

if (!$stmt->execute) {
# -- print error here via $DBI::errstr
}

my @row;
while (@row = $stmt->fetchrow()) {
                 foreach my $val (@row) { print "$val "; }
                 print "\n";
}

# -- insert code here

And in PHP, we accomplish the same task with the following code:

// insert code here

$res = mssql_query("select Col1, Col2, Col3 from MyTable");

for($i=0; $i < mssql_num_rows($res); $i++) {
$line = mssql_fetch_row($res);
  echo("Col1 = $line[0] Col2 = $line[1] Col3 = $line[3]");
}

// insert code here

For further coding information, complete with comments, check the example source that accompanies this article.

Troubleshooting

As with any library, there are a number of problems you can run across when using FreeTDS. Fortunately, the fine FreeTDS team has provided a number of different ways to help you resolve issues. Both the online documentation at:

http://www.freetds.org/userguide/troubleshooting.htm

and the mailing list are great tools for resolving most problems you may have.

Helpful Extras

The FreeTDS library on its own doesn't provide everything that is useful in development. However, there are associated tools that can make life working with Sybase/MSSQL systems much easier.

The first, and foremost, among these is SQSH, the SQL Shell project. Started by Scott C. Gray and now maintained by Michael Peppler (author of the DBI::Sybase Perl module), this is a command-line query tool much like MySQL's mysql client or Oracle's sqlplus. SQSH is easy to use and both stable and reliable. You can find SQSH at:

http://www.sqsh.org/

although the project is housed by SourceForge at this time.

FreeTDS also comes with Qt support in the form of SybSQL. If you have Qt 2.3.1 or higher installed and you want something more graphical than SQSH, this is a good option. Again, this project is maintained by Michael Peppler.

Source Code

Included with this article is the following source code, which can be downloaded from the Sys Admin magazine Web site (http://www.sysadminmag.com/code/):

FreeTDS/tdsexample.c -- C-based example of using FreeTDS

FreeTDS/tdsexample.p -- Perl example of using DBI::Sybase

FreeTDS/tdsexample.php -- PHP example of using mssql_XXX functions

FreeTDS/sample_freetds.conf -- Example FreeTDS configuration file

Kevin Lyons is an admin and developer for Nebraska Interactive LLC, the company behind Nebrask@ Online and a division of NIC. He graduated from Nebraska Wesleyan University with a degree in Computer Science and has been working with a wide variety of Unix flavors since then. He can be reached at: kevin@nol.org.