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 |