Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Connecting With Perl Using Win32 : ODBC

By Brian Kelley, (first published: 2002/12/03)

Connecting With Perl - Using Win32::ODBC

Perl has been a popular language for Unix administrators for years. It is flexible, easy to learn, and capable of doing some very powerful things with relatively few lines of code. Perl has been ported to the Windows environment and is currently supported on Windows by the ActiveState Tool Corporation. ActiveState released its first beta version on December 4, 1996. Since that time, companies like O'Reilly and Associates and Microsoft have partnered with ActiveState. As of the writing of this article, ActivePerl 5.6.1.633 is the current production version. However, ActivePerl 5.8.0 Beta 1 is also available for download. Since this is the first of a series on using Perl to connect to SQL Server, I'll talk a bit about Perl here that I'll forego in the later articles.

Why Use Perl

Perl has a broad base of support that spans operating systems, users, communities, cultures. It's a very simple language to learn, but as with any robust language, one will never feel like one has complete mastery over all the intricacies of it. The port for Windows is very similar to the ones for other operating systems, meaning a lot of code re-use, a lot of cross-platform capability, a lot of others who can be solicited for aid, regardless of what they happen to be running on. Also, Perl is fast. It's efficient. And development efforts in Perl don't generally take a long time. Combine this with the power of Perl, and it is a great choice as a development language.

Is Perl necessarily better than other languages? The answer is, of course, that it depends. There are some things that Perl does better than typical development languages like VBScript and JScript. One of these things is handling regular expressions. Perl is a monster language when it comes to dealing with strings and string processing. Because Perl is so strong in this suit, it is the most popular web programming language. Major companies like Amazon.com use Perl to run their web sites. However, it's not better in "all cases." No language is. 

Why I Use Perl

When I first started looking at Perl, I will admit I was amazed with its flexibility and its simplicity. I was able to write quick scripts in a very short period of time. When I compared my development time on Perl with what I knew it would take in other languages, I was sold. Perl has become more and more a daily tool for me as my work responsibilities have expanded from being a DBA to overseeing the architecture and health of our enterprise. Monitoring event logs, server processes, etc. can all be done with scripts in very little time. For instance, when we started having problems with our external mail server refusing connections, a quick 10-minute script let me check its status every 5 minutes and if I detected a refused connection, I shot an email message to one of our internal servers notifying the appropriate personnel. I don't even want to think about how long it would have taken in another language.

With that being said, let me say one of the reason a lot of my development times have been reduced are because of those who have written the packages I've used in the process of my efforts. I monitor event logs. I check the status of services. I determine changes in the users and groups within the domain. I attempt to open an SMTP connection to the external mail server... all through packages that have been written, tested, and heavily used by others. The broad base of support and the activity of those using this language are two things I really love. There are numerous news groups and mailing lists for this language. There are very active ones devoted to Windows alone. If you have a question about Perl, getting an answer is easy and quick. Chances are someone on one of the forums or mailing lists has done what you are attempting to do (actually it seems more like 50 people most of the time).

Where I Won't Use Perl

I won't use Perl (unless the functionality I need isn't available in another scripting language) in an ActiveX data transform in DTS. According to Microsoft, VBScript is about 10 % faster than JScript, which is about 10% faster than PerlScript. An ActiveX data transform is anywhere between two and four times slower than a normal copy operation, and I want all the performance I can get. As a result, VBScript is my ActiveX scripting language of choice in DTS. When Microsoft updated Books Online, it included these numbers under the topic "Enhancing Performance of DTS Packages."

Getting Started in Perl

If you've heard about Perl on Windows but aren't familiar with it and want to see what all the fuss is about, I've written a Getting Started page just for you. ActiveState Perl has a .MSI installer package that really simplifies life. Once it is installed, Perl is at your fingertips. You can use it with IIS, from the command-line, through a SQL Server Agent job, or in an ActiveX data transform within DTS (though as I just mentioned, VBScript is probably best for DTS). PerlScript is, after all, an ActiveX scripting language. So if you learn Perl, you can use it throughout your environment.

If you have a previous programming background, especially if it's C, C++, JavaScript, or Java, you should have a relatively short ramp-up time. There are some differences, as with any language, but for those who have programmed before, Perl isn't in a world all its own. My previous experience with these languages meant I was looking at structures I'm very familiar with. If you're used to Visual Basic or VBScript, the syntax is different and that'll take a little getting used to. However, the basic flow controls and subroutine keywords are essentially the same.

If you've never programmed before, Perl is fairly easy to learn. The plethora of books available as well as the tutorial resources on the web means a lot of help is out there. Also, Perl is not a very esoteric language. After all, it keeps in mind the system administrator who needs to write a script to do something but doesn't have time to become a developer. There is even a book out for those researchers in the biological sciences who want to use Perl to help process some of their data. So if people who aren't primarily computer people can use it, so can you!

If you are used to the Unix world and have used Perl, say hello to an old friend! The ActivePerl releases Perl are based off the main distributions of Perl. You'll have to learn some of the Windows specific packages, but the basic language and packages are the same. You'll find it very, very similar to what you've experience in the past, to include file handling, regular expressions, and the like.

Connecting to SQL Server via Perl

There are three typical methods used to connect to SQL Server from Perl:

  • Through Win32::ODBC
  • Through ADO (ActiveX Data Objects) using Win32::OLE
  • Through Perl DBI (the standard database interface for Perl)

This series of articles will look at all three connection methods. This article will focus on connecting through ODBC using the package Win32::ODBC. The Win32::ODBC package is written by David Roth, but it is included in the core distribution of ActiveState Perl. David Roth has written two books on using Perl in the Windows environment and they are both great resources for the system administrator (or DBA). His Perl website contains information on the packages Roth Consulting has developed for this language. He even has a FAQ for Win32::ODBC available.

ODBC is a standard connection method and it can be used both on a Windows system as well as a Unix-based one. There are quite a few ODBC toolkits available for Unix now, some free, some not. As a result, ODBC is always a viable option when it comes to connecting to SQL Server from Perl. However, Win32::ODBC is only available on the Windows platform as the name would imply. DBI has an ODBC interface built as well, but it's not the same as Win32::ODBC. There are actually several options for connecting to SQL Server via DBI so ODBC isn't the only option there. I'll look at those other options in future articles.

Prerequisites for Using Win32::ODBC

The main prerequisite for using Win32::ODBC to retrieve data is to have a valid ODBC connection defined. You'll need a valid Data Source Name (DSN). If you aren't familiar with creating DSNs, I've written a small walk-through document on Creating a New ODBC Data Source Name (DSN).

The other prerequisitive is to have the Win32::ODBC package installed along with your ActivePerl installation. Since Win32::ODBC is a standard package with ActivePerl by default, it should already be installed. If it isn't, check your ActivePerl documentation on how to use PPM to obtain and install Win32::ODBC.

Establishing a Connection

Since I'm using a package, I'll have to specify that with the use keyword (keep in mind that Perl is a case-sensitive language like C or C++ so use should be in all lower-case). Since I'm going to use the Win32:ODBC package, I tell Perl I'm going to use that package by the following line of code:

use Win32::ODBC;

Once I have specified the package, all the methods and properties of the package are now available. One of those methods is a constructor that creates a new ODBC connection, appropriately called new(). If I have a DSN called MyDSN, I can open a new connection by:

my $connection = new Win32::ODBC("MyDSN");

Also, I could keep my DSN connection in a variable (to modify easily) and pass my variable to the new() method in order to create a new ODBC connection:

my $DSN = "MyDSN";

my $connection = new Win32::ODBC($DSN);

If I prefer, I can use a standard method call syntax like so:

my $connection = Win32::ODBC->new("MyDSN");

or

my $DSN = "MyDSN";

my $connection = Win32::ODBC->new($DSN);

Connection Options

As with any ODBC connection, I have the standard options available with respect to the ODBC connection string. For instance, If I am using a SQL Server login and I need to pass username and password credentials:

my $DSN = "DSN=MyDSN;UID=MyUser;PWD=password;";

my $connection = new Win32::ODBC($DSN);

I can also use a File DSN:

my $DSN = "FileDSN=MyFileDSN.dsn";

my $connection = new Win32::ODBC($DSN);

So all of the options available through a normal ODBC connection (provided it is a DSN connection) are available.

Checking the Connection

One of the things I always do in any application where I make a database connection is check to make sure I have a do in fact have a valid connection. I never assume I do. After all, the server could be down, my credentials might no longer be valid, or the connection between my client and the server could be broken. These are just a few of the possibilities that would prevent me from getting a valid connection to the database.

In Perl, if an object creates successfully, I can "check" it by just running a Boolean comparison on it. If it returns true, the object was successfully created. If the Boolean comparison returns false, I don't have an object. When I issue a Win32::ODBC->new() method call to create an ODBC connection, if the connection is successfully made, the object is created. If for whatever reason the object isn't created, a Boolean comparison will return false (actually it returns undef but for our purposes it's equivalent to not true ~ false). I can use this knowledge in one of two ways: I can either do the check as part of the assignment operator or separately after the assignment.

As part of the assignment operator:

my $DSN = "MyDSN";

my $connection = new Win32::ODBC($DSN)
     or die "Could not open connection to DSN because of [$!]";

After the assignment:

my $DSN = "MyDSN";

my $connection = new Win32::ODBC($DSN);

if (!$connection)
{
    die "Could not open connection to DSN because of [$!]";
}

The die keyword will end the program and also returns where I'm exiting the program. I can use $! to return the exact error I encountered. In the case where I fail to make a data source connection, I'll see:

  No such file or directory

The error isn't exactly intuitive with Win32::ODBC, but I can still check the connection and exit out the script if I don't have a valid one. So putting all of this together, if I fail to make a successful data source connection I'll see something like the following returned from my Perl script:

  Could not open connection to DSN because of [No such file or directory] at odbc-conn.pl line 18.

Retrieving Data

Retrieving the data is very easy as well. The appropriately named Sql() method submits a SQL statement to the ODBC connection and can get back a resultset. If I want to issue the following SQL statement:

SELECT CustomerID, CompanyName FROM Customers

I can do so by:

$SQL = "SELECT CustomerID, CompanyName FROM Customers";

$connection->Sql($SQL);

Again, I probably want to trap for errors. The Sql() call is a method call that returns a result code; it doesn't return an object. Since I'm returning a result code, the standard result code of 0 for no errors is what's used here. If Sql() encounters an error it won't return 0. Also, since zero (0) also typically means false in a Boolean comparison and a non-zero value means true, I can verify whether or not I was successfully able to execute the SQL command by:

$SQL = "SELECT CustomerID, CompanyName FROM Customers";

if ($connection->Sql($SQL))
{
    print "I could not execute the following statement:\n $SQL\n";
    print "I encountered this error:\n";
    print $connection->Error() . "\n";

    ## Closing the database connection 
    $connection->Close();

    ## Exiting the program 
    die;
}

This bit of code brings up three more points. The first is the hash (#) mark. In Perl, the hash mark means the beginning of a comment (through the end of the line), much like a single quote (') in Visual Basic indicates such. A single hash mark is all that's needed to mark a comment. I tend to use double hash marks just to make the comments stand out more. The second one concerns the Error() method. Error() returns a string containing the error message returned from ODBC. The final point is the Close() method call. As would be expected, this closes the ODBC connection. 

As an example, if I "fat-finger" the table name and call it CustomersA instead of Customers, the code would result in similar to the following:

  I could not execute the following statement: 
  SELECT CustomerID, CompanyName FROM CustomersA
  I encountered this error:
  [208] [1] [0] "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'CustomersA'."
  Died at odbc-conn.pl line 38.

Reading the Result Set

The Sql() method will open the equivalent of a cursor if a result set is returned. To retrieve a row of data, I can use the FetchRow() method. This is like the FETCH NEXT command from T-SQL or  MoveNext method from ADO. If data was returned, FetchRow() will be true. If no data could be retrieved (i.e. I'm at the end of the cursor), FetchRow() will be false. I can use this in a looping structure much in the same way I use Do While Not oRst.EOF in Visual Basic. 

Once I have moved to a new row, I have two ways to parse the row: Data() and DataHash(). Data() returns the data in an array. DataHash() returns the data in what is known as a hash in Perl. It is much like an array, except instead of referring to elements by a number, I refer to the elements by a key word. More on this in the specific examples.

Using Data()

Since I'm dealing with an array, the array index starts at zero. So if I've assigned the parsed data to a variable like @Data, I refer to the two columns as $Data[0] and $Data[1]. If the @Data and $Data are throwing you off, think of $Variable as representing a single occurrence of an array @Variable. So I may have an array of @Data, however, to retrieve an individual element of the array I have to use $Data such as $Data[1]. Here's an example:

while ($connection->FetchRow())
{
    my @dataRow = $connection->Data();

    print $dataRow[0] . " : " . $dataRow[1] . "\n";
}

In this snippet of code I'm cycling through every row in the returned result set, parsing the data using the Data() method, and then printing the parsed data in the format of CustomerID : CompanyName. Unless I look back at the original SQL statement, though, I may not remember that the first element of the array corresponds to CustomerID and the second element to CompanyName. This is where DataHash() is more legible.

Using DataHash()

I can use DataHash() in much the same way I used Data(). The only difference is that instead of assigning the results to a variable of the form @Variable, I have to assign to a variable of the form %Variable. Also, instead of referring to the column as $Variable[<Column Number>] (such as $dataRow[0]) I refer to an element by the key in the form $Variable{<key>} where in this case the key is the column name. So for the first column, I would use $Variable{CustomerID} which is certainly more clear than just $Variable[0]. Here's an example of using DataHash():

while ($connection->FetchRow())
{
    my %dataRow = $connection->DataHash();

    print $dataRow{CustomerID} . " : " . $dataRow{CompanyName} . "\n";
}

Closing the ODBC Connection

Though I covered this in trapping for an error when submitting a SQL statement, I'll cover the Close() method one more time for completeness. The Close() method does just what it looks like, close the ODBC connection. For instance:

$connection->Close();

That's all there is to closing the connection.

Putting It All Together

If I put together everything I've talked about, I'll can write a short Perl script that'll connect to SQL Server, retrieve data, and do something with it. Below is an example heavily commented to show how Perl and Win32::ODBC are used putting together everything I've talked about:

use Win32::ODBC;

## This is a demonstration script which makes a connection to a SQL Server database.
## It uses the Win32::ODBC package, which comes standard with the ActivePerl 
## download.

## Establish the connection to SQL Server using a known DSN Connection
## The DSN is configured for Windows security, so I won't specify any login
## parameters. It is set to point to the Northwind database on the server.
my $DSN = "Northwind";

my $connection = new Win32::ODBC($DSN);

## Check to make sure the connection is valid
if (!$connection)
{
    die "Could not open connection to DSN because of [$!]";
}


## Define a SQL Query and retrieve a result set based on that query.
## I want to return the name of all companies and the customerID they've been assigned.
my $SQL = "SELECT CustomerID, CompanyName FROM Customers";

if($connection->Sql($SQL))
{
    ## A Call to the connection method will return a value. A non-zero value 
    ## is treated as true by Perl. The Sql method will only return a non-zero 
    ## value if it ran into an error.
    print "I could not execute the following statement:\n $SQL\n";
    print "I encountered this error:\n";
    print $connection->Error() . "\n";

    ## Closing the database connection 
    $connection->Close();

    ## Exiting the program 
    die;
}

## Print out a result of our query. Typically I'd do something more, here, but
## since this is script simply to show how to connect and retrieve data, I'll
## just list the contents to show retrieval has taken place. Fetch row is exactly
## as it sounds. It fetches the next row if used without parameters
print "The query returns the following list of customers:\n";

while ($connection->FetchRow())
{
    ## Translate the row into something usable. In this case I am changing
    ## to a hash so I can use key names (CustomerID, CompanyName) rather than
    ## integer values (0, 1). If I wanted to use integer values, I'd use the 
    ## Data() method instead of the DataHash() method.
    my %dataRow = $connection->DataHash();

    print $dataRow{CustomerID} . " : " . $dataRow{CompanyName} . "\n";
}

## Closing the database connection
$connection->Close();

Here is a snippet of the results of this script:

The query returns the following list of customers:
ALFKI : Alfreds Futterkiste
ANATR : Ana Trujillo Emparedados y helados

Finding Out More

I've touched briefly on how to connect to a data source using Win32::ODBC but I've not gone into much detail on any of the methods I've described in this article. I've also intentionally left out the debugging methods that are also available. If you are interested in using them, please refer to the Win32::ODBC documentation or the FAQ. This article was intended to be a brief introduction on how to use Win32::ODBC and hopefully I've covered enough to whet your appetite for connecting to SQL Server with Perl and the Perl language itself.

Scripts

For your convenience I'm including scripts and a File DSN of what I've talked discussed in this article. You may need to right-click on the link and choose Save Target As... in order to download the file(s).

Connect via Windows Authentication: odbc-nt.pl
Connect via SQL Server Login: odbc-sql.pl
Connect via File DSN (Windows Authentication): odbc-file.pl
File DSN: Northwind-File.dsn
ZIP Archive of all 4 files: PerlODBC.zip
Total article views: 39570 | Views in the last 30 days: 15
 
Related Articles
FORUM

Simple value method help

How to return text contents to .value method?

FORUM

Render method not returning any data

ReportExecution2005 web server render method

FORUM

SQL Server 2005 language

SQL Server 2005 language

FORUM

Connecting With Perl Using Win32 : ODBC

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

FORUM

MS Access - ADO Connection - Method or Member not found Error

MS access - ADO Connection - Method or Member not found Error

Tags
ado    
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones