Other ways to connect to MS SQL Server on Windows

Here is a brief summary of another alternatives to access MS SQL Server from Windows (see the Unix page for Unix alternatives). Most of these alternatives offer better portability, if you need to access other RDBMSs as well.  If you feel something is missing, please drop me a line on esquel@sommarskog.se.

Below follows details on these.

DBI and DBD::ODBC

DBI is the de-facto standard in the Perl world for talking to database servers. DBI is a standardized high-level interface, which you supplement with a driver whose name always start with DBD::. The advantage with this is obvious: if you move from one DB engine to another, you just change DBD drivers. If you only move from one OS to another, you may not have to do even that. And if you use DBD::ODBC your Perl code might be practically immune to portability problems. (Provided that your SQL code is portable, that is!)

You find further information on the DBI home page. More DBD drivers are mentioned below.

Win32::ODBC

There used to be the days when almost all database questions on the Perl-Win32 mailing lists concerned Dave Roth's Win32:: ODBC. Win32::ODBC comes with ActiveState's binary distribution of Perl for Windows.

How widely it is used in this day and age, I don't know. The latest version is from 1998, which means that it is not likely to provide full support for SQL 2000, even less SQL 2005.

The advantage with Win32::ODBC over DBD::ODBC is that you get more direct access to the ODBC API. On the other hand Win32::ODBC requires more careful programming. For instance, you have no option but to check for errors yourself, in difference to DBD::ODBC and Win32::SqlServer. And it appears that you can only retrieve the last error message, which is my opinion is a very serious restriction.

In terms of portability Win32::ODBC permits you to talk from any DB engine with an ODBC driver from a Microsoft platform, but it does not run on Unix or other OS:s as far as I know.

ADO

Since ADO is a COM library, this means that you could use Win32::OLE to access SQL Server as well anything else you can access with ADO.

Rather than using ADO direclty through Win32::OLE, there is a DBD::ADO on CPAN. There is also an Win32::ADO, but this is a version 0.03 from 1999, so it may not be full-fledged.

I don't have any experience of DBD::ADO or Win32:ADO, but I have used ADO from Visual Basic, and I find it difficult to offer very many kind words for this API. ADO is fond of doing things behind your back, and error handling is flaky.  (I have section on ADO in my articles on error handling in SQL Server on my main web site.) See also this KB article from Microsoft about some quite unlogical restrictions with error handling in ADO.

Sybperl

MSSQL::DBlib is evolved from Michael Peppler's Sybperl, but if you have Sybase's client libraries on your NT machine, you can use Sybperl directly. The Sybperl family offers three modules: Sybase::DBlib, Sybase::CTlib and DBD::Sybase. As the same restrictions that apply to the MSSQL::DBlib apply to Sybperl, and you would need to purchase Open Client from Sybase, I don't really see any point of using Sybperl for a new project. However, if you are migrating from Sybase to MS SQL Server, and want to keep your scripts this option might be worthwhile.

FreeTDS

TDS -- Tabular Data Stream -- is the protocol that SQL Server talks with its clients. This is a proprietary protocol, owned by Microsoft (and Sybase, who have their version). Nevertheless there is a project out there which aims at cracking (or whatever they do) this protocol and put the work in the public domain. Check out the FreeTDS home page for further details. There appears to be a DBD::FreeTDS that goes along with it.

Others?

I have to admit that I follow the arena of SQL Server access from Perl poorly. If you want to find inquire about other alternatives, the mailing list Perl-win32-database is probably a good place. This, and other mailing lists about Perl is hosted by ActiveSate. And if you discvoer something which is not on this page – please tell me!

Back to the MSSQL home page.

Last updated 05-11-27 17:40