ODBTP: Good solution for connecting to MSSQL from Linux/UNIX

Go to URL for details about a very good solution for connecting to a MS SQL Server database from Linux or UNIX.

Anyone care to comment on how this compares to FreeTDS

FreeTDS is primarily focused on the implementation of the TDS protocol on UNIX/Linux. The problem is that Microsoft has not publically documented their flavor of TDS, AFAIK. As a result, FreeTDS is always 1 or more steps behind, and susceptible to bugs caused by SQL Server upgrades and future service packs. Another issue is that many applications on Linux/UNIX machines rely on unixODBC and the FreeTDS ODBC driver. Neither of which is as good and stable as their Win32 counterparts.

ODBTP is actually more comparable to EasySoft's ODBC-ODBC bridge. The goal of ODBTP is to provide the best and most reliable way to communicate with Win32-based databases from any platform. It uses ODBC on the Win32 only server-side, and a special C library for EASILY building client-side applications. Unlike the FreeTDS and ODBC API's, the ODBTP API is designed to make it easy to create modules for scripting languages like PHP, Perl and Python. At the momentt, only a module (extension) exists for PHP.

Basically, the difference is that FreeTDS uses a reengineered version of Microsoft's TDS protocol to talk to SQL Server, while ODBTP uses Microsoft's version of Microsoft's TDS protocol. ODBTP is also considerably easier to install, and is faster than FreeTDS.

Just to let you know, we've been using FreeTDS for quite a while now since SQL Server 7.0 (most of our DB servers are now SQL 2000) and we haven't seen too many problems with it. Right now, we connect to SQL Server from *NIX with programs written in PHP, Perl, C and C++. Also, not all our servers are Linux -- we run quite a few FreeBSD servers and a couple of OpenBSD boxes as well. I also have a NetBSD server at home, with FreeTDS installed. I'll give ODBTP a try and see if they compile on these operating systems.

Also, ODBTP only has an extension for PHP, and quite a few of our programs are in perl, I don't think we'll be using it at work in the near future. However, I'm interested enough to write some C code and compare the performance. I'll run a few tests when I find some time .

So far, ODBTP has been successfully installed on Linux, Solaris and MacOS X.

ODBTP is fairly new, and was created because PHP's mssql support was inadequate and inconsistent across platforms. This is the reason for the bias towards PHP. The ODBTP protocol and C library were designed to facilitate the creation of a full featured PHP database extension. The odbtp extension supports all the functions from PHP's mssql extension. This allows PHP scripts that use the mssql extension to work with ODBTP with very little, if any, modifications. For example, PHP's PEAR DB mssql driver works with the odbtp extension without any changes.

Although ODBTP was primarily intended for SQL Server connectivity, it is also a great solution for connecting to MS Access, Visual FoxPro and other Win32-based databases from any platform.

In regards to a Perl module, I am hoping to find someone with expertise in that area to provide assistance.

>> PHP's mssql support was inadequate and inconsistent across platforms
Would you care to clarify this statement?

Many of the databases that I work with have nvarchar and ntext fields. The mssql extension does not work very well with UNICODE data, especially on Windows. The odbc ext also is not adequate.

On UNIX, the mssql ext uses FreeTDS, and on Windows it uses the obsolete and completely unsupported DB-Library. My initial experience with PHP/FreetDS was on Solaris over a year ago. Not only was it unable to easily process UNICDOE data, but it would crash in response to certain queries. While PHP/DB-Library did not crash, it was impossible to use it for ntext fields. Any attempt will produce the following error:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier

So, all of this led to the creation of ODBTP. Which IMHO provides the best connectivity between PHP and MSSQL. In fact it is superior to the odbc extension, which suffers from the fact that it uses ODBC 2 instead of ODBC 3. I am sure that you will determine this for yourself. :-)