SQLServerCentral Article

Connecting With Perl Using Win32 : ODBC

,

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

Rate

3.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3.67 (3)

You rated this post out of 5. Change rating