PREVIOUS  TABLE OF CONTENTS  NEXT 

Win32::ODBC

Joe Casadonte

I was once told, "Look to your hobbies to start a business." About three years ago, I thought about starting a company that combined my hobbies: Perl and the Internet. I found a store in Delaware that sold thousands of hot sauces, another of my passions, and an idea came to me: I could sell the hot sauces for the store online. I would put the product data into a database and take orders over the Web, using Perl to generate the HTML and keep track of sales.

One part was missing. How would I connect to a database? Outside work, I had access only to a Win95 machine. It ran Perl for Win32 from ActiveState (then called hip Communications). OraPerl, DBI, and other solutions hadn't yet been ported to the Win32 platform. For a long time I was stuck.

What I really wanted was an ODBC solution. ODBC (Open Database Connectivity) is Microsoft's attempt to standardize database access. If a program needs to connect to an Oracle database, an Informix database, and an Access database, it needs three different versions of the database code for each, because each of those databases has its own proprietary access scheme. With ODBC, you simply write your program to conform to the ODBC API and let the ODBC Manager and Driver take care of the database language specifics. Initially just a Windows solution, ODBC is now finding its way onto many Unix boxes.

Along came Dave Roth's Win32::ODBC module. Dave married the ODBC toolkit with Perl, finally giving me (and countless others) access to databases from Perl on Windows. I began to put together the pieces necessary to run a store, starting with the product database, data extraction routines, and the creation of on-the-fly HTML pages via Perl.

However, I found that the documentation a little sparse. After talking to Dave about it, I set out to document the module more thoroughly. Since then, I've gotten many individual requests asking for help and examples. This article is an attempt to satisfy a lot of you at once.

Tutorial

This tutorial will walk you through the basics of Win32::ODBC programming. Some of the examples will use Lincoln Stein's CGI.pm module to make the applications more immediately useful. Because the purpose of this tutorial is to introduce you to Win32::ODBC, I'll assume a basic understanding of Perl, CGI concepts, HTML, and SQL (but see the next page for a gentle introduction). I'll also assume that your Web server, scripts, ODBC data sources, and everything else are already set up and working.

We'll use the following two tables. Both the tables and the SQL are very simple, to keep the focus on the module. I'm importing these tables from a MS-Access database running on my computer, but the joy of ODBC is that this could be any database running under any operating system. The State table will be explained later.

Table 1: Sauces

Field Name Type Length Notes
ProductID AutoNumber n/a Sequence/Primary key
ProductName Text 25  
Quantity Number Double in ounces
Price Number Double  
PepperType Text 20 main type of pepper in sauce
Scoville Text Long rating of heat content; Jalapeņo pepper = 3-4,000 units, Scotch Bonnets (Habañero) = 2-300,000 units!

Table 2: State

Field Name Type Length Notes
PID Number Integer Process ID - Multi-key Primary key
ProductID Number Long Foreign Key into Sauces table - Multi-key Primary key
Sequence Number Integer Multi-key Primary key

Installing Win32::ODBC

To install Win32::ODBC, you first need to determine which implementation of Perl you're running: type perl -version to find out. If you're running the core Perl distribution (that is, not from ActiveState), the module is included in the libwin32 bundle, available from the CPAN. You won't need a C compiler unless you're using a development release or built your own Perl using Visual C.

If you have the ActiveState version, you'll need to locate the Perl installation directory is (referred to as $PERL below), and a temporary directory ($TEMP below). Then follow these installation steps:

Getting Started

To get started, let's write a simple script that extracts all the data from the database and writes it out to a table. The first thing to do, as with any Perl module, is to use it: 

use Win32::ODBC;

Next we create a new ODBC object: 

my ($db) = new Win32::ODBC("Article");

This creates an object ($db) that connects to the data source named Article. If there were a user name and password associated with Article, we could supply them in an ODBC connect string: 

my($db) = new Win32::ODBC("dsn=Article; uid=foo; pwd=bar");

where Article is the name of the data source, foo is the user ID, and bar is the password. Next, we use SQL to extract the data: 

$db->Sql("SELECT * FROM Sauces");

and then loop to fetch the data, one row at a time: 

while ($db->FetchRow()) { ... }

Listing 1: Demystifying SQL

Programs using Win32::ODBC typically repeat two actions: first a row of the database is fetched, and then the data from the row is extracted. There are two flavors of this second step: Data() and DataHash(). Data() places the data in an array, in the order it was selected by the SQL statement. The latter places the data in a hash, in the form $hash{FieldName} = DataValue. If you select all fields using the * wildcard, then the field order of the returned data is undefined; it therefore makes more sense to use DataHash() than Data(). Finally, we close the ODBC handle. Putting everything together: 

use Win32::ODBC;

my($db) = new Win32::ODBC('Article');

$db->Sql("SELECT * FROM Sauces");

while ($db->FetchRow()) { 
   my(%data) = $db->DataHash();

   print HTML "<TR><TD>$data{'ProductName'}"; 
   print HTML "    <TD>$data{'Quantity'} ounces"; 
   print HTML "    <TD>\$$data{'Price'}"; 
   print HTML "    <TD>$data{'PepperType'}"; 
   print HTML "    <TD>$data{'Scoville'} Scoville Units";
}

$db->Close();

Debugging

Note the lack of error checking after calling the Win32::ODBC methods. That's poor programming practice, and I only do it here because column space is precious. You should check for errors religiously.

When we called the Sql() method, we "compiled" the SQL statement. Had we asked for a nonexistent column or table, an error would have been generated. If we do get an error, we should display the error text and die gracefully: 

my($stmt) = "SELECT HasGarlic FROM Sauces"; 
if ($db->Sql($stmt)) { 
    my ($err) = $db->Error; 
    warn "Sql() ERROR\n"; 
    warn "\t\$stmt: $stmt\n"; 
    warn "\t\$err: $err\n";

    exit;
}

In a scalar context, the Error() method returns a string with the following format: ErrorNumber ConnectionNumber ErrorText.

The example above asked for a column that does not exist. On my system, this generated the following message: 

Sql() ERROR 

    $stmt: SELECT HasGarlic FROM Sauces
    $err: [-3010] [1] [0] "[Microsoft][ODBC Microsoft Access 97 Driver] 
                           Too few parameters. Expected 1."

The value of $err is generated by the ODBC Manager, not the Win32::ODBC module. As you see, the error messages can be a little cryptic.

Two other functions may help diagnose why you're not getting back the data you expected: FieldNames() and DumpData(). FieldNames() returns an array of field names in the current result set; if you're wondering why $data{'scoville'} is empty, FieldNames() will tell you that you actually meant $data{'Scoville'}. DumpData() dumps all field names and row data to the currently selected output filehandle - typically STDOUT.

If you are convinced that you have found a bug, let me know. Include the Perl and Win32::ODBC versions. To determine your Win32::ODBC version, use this: 

  my($pm, $pll) = $db->Version(); 
  die "PM: $pm, PLL: $pll\n";

CRUD

The standard database functions are sometimes abbreviated as CRUD: Create (insert), Read (select), Update, and Delete. We'll see examples of each throughout the remainder of the article.

Suppose that instead of displaying all records in the Sauces database, we only wanted to display records whose last name contained a particular word. After all, the database might have millions of records. Our previous SELECT statement would now look like this: 

$db->Sql("SELECT * FROM Sauces WHERE PepperType LIKE '%chile%'");

or put into a program using CGI.pm: 

# Extract the pattern submitted through the browser
$pattern = $query->param('pattern'); 

# Retrieve the data matching the pattern
$db->Sql("SELECT * FROM Sauces WHERE PepperType LIKE '$pattern'");

Both examples use the LIKE conditional, which takes a single-quoted string: a SQL wildcard, which is similar in spirit to a Perl regular expression. The differences are outlined in the box below.

SQL Wildcards vs. Perl Regular Expressions
Perl has great pattern matching; SQL doesn't. The ANSI standardization of SQL has two basic pattern matching operators: the percent sign ('%'), which matches zero or more of any character, and the underscore ('_'), which matches any single character. Here are some equivalencies:

SQL Wildcard Perl Regex
LIKE 'L%' m/^L/
LIKE '%ing' m/ing$/
LIKE '%foo%' m/foo/
LIKE 'fo_' m/^fo.$/

The WHERE clause just shown cuts down on the number of rows displayed. But let's assume that we have a lot of data to display (think of an AltaVista search) and we only want to display ten rows per page. This is tricky with CGI because of its statelessness. (But see Lincoln and Doug's article for a mod_perl solution.) We'll store the state in a State table to keep the query results between accesses.

The first time we access the database, instead of displaying the results of the query, we'll store them in the State table. Inside the while loop we could do one of two things: store the results in an array and then insert them later into the State table, or insert them into the State table as we fetch them. The former uses one ODBC object: 

# SQL statement to fetch the data 
$db->Sql("SELECT ProductID FROM Sauces ". 
         "WHERE Scoville > 10000 ". 
         "ORDER BY Scoville, ProductName");

# Fetch each row 
my ($cnt, $id, @ids) = 0; 
while ($db->FetchRow) { 
    # Store for later use 
    push(@ids, $db->Data); 
}

# Use stored IDs to populate State table
foreach $id (@ids) { 
    $db->Sql("INSERT INTO State (PID, ProductID,
                                     Sequence) ". 
             "VALUES ($$, $id, ". $cnt++ . ")"); 
}

The latter uses a second ODBC object (one for each SQL statement): 

# New statement, same connection 
my ($ins) = new Win32::ODBC($db);

# SQL statement to fetch the data 
$db->Sql("SELECT ProductID FROM Sauces " . 
         "WHERE Scoville > 10000 ". 
         "ORDER BY Scoville, ProductName");

# Fetch each row 
my ($cnt, $id) = 0; 
while ($db->FetchRow) { 
    ($id) = $db->Data;

    # Immediately store in State table 
    $ins->Sql("INSERT INTO State (PID, ProductID, Sequence)"
                 ."VALUES ($$, $id, ". $cnt++ . ")");
}

The Insert takes place as soon as $ins->Sql() is called. It's immediately committed (a database term meaning unequivocally completed) unless you specify otherwise; see the Transactions section below.

After filling in the State table, we can then pull rows out of it in sets of ten, using two ODBC objects: 

# Get CGI parameters 
$pid   = $query->param('pid'}; 
$start = $query->param('start'};

# Fetch data out of State table 
$dbstate->Sql("SELECT ProductID FROM State ". 
    "WHERE PID = $pid AND Sequence >= $start ".
    "AND Sequence < ". ($start + 10) . " " . 
    "ORDER BY Sequence");
# Fetch each row from State
my($id); 
while ($dbstate->FetchRow) { 
    ($id) = $dbstate->Data;

    # Fetch actual data from Sauces 
    $dbsauce->Sql("SELECT * FROM Sauces WHERE 
                                ProductID = $id");

    # Output HTML
}

This works, but it's inefficient. We could accomplish it all in one SQL statement with a join: 

# Get CGI parameters 
$pid   = $query->param('pid'}; 
$start = $query->param('start'};

# Fetch Sauces data based on State table data
$db->Sql("SELECT sa.* FROM State st " . 
    "INNER JOIN Sauces sa 
                 ON st.ProductID = sa.ProductID ". 
    "WHERE st.PID = $pid ". 
        "AND st.Sequence >= $start " . 
        "AND st.Sequence < ". ($start + 10) . " " .
    "ORDER BY st.Sequence");

# Fetch each row
while ($db->FetchRow) { 
    my (%data) = $db->DataHash;

    # Output your HTML here
}

As you can see, the complexity is in the SQL, not the ODBC. We used calls to Sql() and FetchRow() just like before.

Updates and Deletes are similar to Inserts. Given that we have this State table, we would need a process to clean it up. We could clean it up all at once: 

    $db->Sql("DELETE FROM State");

or only when a particular user is done with the data: 

    $pid = $query->param('pid'); 
    $db->Sql("DELETE FROM State WHERE PID = $pid");

None of this is effective or efficient as CGI programs go. It's merely a convenient demonstration of basic Win32::ODBC concepts. For a more complete example, see Listing 3: PepperSearch.pl.

Listing 2: Counterpoint: The DBI alternative to Win32::ODBC

Transactions

Some ODBC drivers support transactions, with commits and rollbacks. Rollbacks are another database term: retracting an action that hasn't yet been committed. See the Data Sources section to find out how to determine if your driver supports them. Sometime after connecting (usually immediately after, but it depends on your logic flow) you can set the AutoCommit option off, and then rollback or commit depending upon program flow: 

    $db->SetConnectOptions('SQL_AUTOCOMMIT',
	                            'SQL_AUTOCOMMIT_OFF'); 
    ...
    if ($fatal_error) { 
        $db->Transact('SQL_ROLLBACK'); 
        die "Error - rolling back\n"; 
    } 
    ... 
    $db->Transact('SQL_COMMIT');

Data Sources

Sometimes you won't beforehand which database you need to connect to. You can query the ODBC Manager and get a list: 

print HTML "<SELECT NAME=\"DataSources\" SIZE=1>\n";

my (%dsn, $key) = Win32::ODBC::DataSources();

foreach $key (sort keys %dsn) { 
    print HTML "<OPTION>$key\n"; 
}

print HTML "</SELECT>\n";

This produces an HTML selection box with all of the data sources listed. But suppose you knew you only wanted Oracle databases. The value of the returned hash contains the name of the driver, so you could filter them: 

my (%dsn, $key) = Win32::ODBC::DataSources();

foreach $key (sort keys %dsn) { 
    print HTML "<OPTION>$key\n" 
                      if $dsn{$key} =~ /Oracle/; 
}

Once you've connected to a driver, you can query to see if it can handle a specific ODBC API function, such as transactions: 

my(%data) = $db->GetFunctions($db->SQL_API_SQLTRANSACT); 
my($transOK) = $data{$db->SQL_API_SQLTRANSACT};

$db->SetConnectOptions('SQL_AUTOCOMMIT',
                'SQL_AUTOCOMMIT_OFF') if ($transOK);

You can query the connection for things like the database name and version: 

my($db_ver)  = $db->GetInfo($db->SQL_DBMS_VER);
my($db_name) = $db->GetInfo($db->SQL_DBMS_NAME);

You can also configure new data sources with ConfigDSN():
$db->ConfigDSN($db->ODBC_ADD_DSN, 
               "Microsoft Access Driver (*.mdb)",
               "DSN=foo", "DBQ=c:\\temp\\foo.mdb");

The second argument is the driver name and has to be specified exactly right. The subsequent arguments are all driver specific. One way to determine the name and the arguments is to set up a dummy data source of the correct type, connect to it, and then use GetDSN() to find out what it expects when configuring it: 

my(%data, $key) = $db->GetDSN(); 
foreach $key (keys %data) { 
    print "$key: <$data{$key}>\n"; 
}

Then, specify these same elements when adding a new data source, like we did above.

Data Dictionary

After connecting to a data source, you can find out what tables are in it: 

 my(@tables) = $db->TableList;

This will return all tables regardless of who owns them. You can use the more versatile Catalog() if you wish to find only specific tables or table types: 

# Table-type is VIEW 
my(@views) = $db->Catalog("", "", "", 'VIEW');

# Owner is UBER, type is SYSTEM TABLE 
my(@uber) = $db->Catalog("", 'UBER', "", 
                                  'SYSTEM TABLE');

The corresponding function for fields, SQLColumns(), wasn't implemented in Win32::ODBC, so you can't determine which fields are in a particular table. You can, however, find out what fields or columns are in a specific result set and get their column attributes: 

$db->Sql("SELECT * FROM State"); 
my (@fields) = $db->FieldNames;

my (%type) = $db->ColAttributes($db->SQL_COLUMN_TYPE);
my (%len)  = $db->ColAttributes($db->SQL_COLUMN_LENGTH);
my (%null) = $db->ColAttributes($db->SQL_COLUMN_NULLABLE);

foreach $field (sort @fields) { 
   print HTML "<TR><TD>$field<TD>$type{$field}",
              "<TD>$len{$field}<TD>", 
   $null{$field}==$db->SQL_NO_NULLS ? "NOT NULL" : "","<BR>"; 
}

which for our State table would produce: 

<TR><TD>PID<TD>4<TD>4<TD><BR> 
<TR><TD>ProductID<TD>4<TD>4<TD><BR>
<TR><TD>Sequence<TD>5<TD>2<TD><BR>

Conclusion

You know, I never did start that hot sauce business. Once I had all the pieces together and working, I found that solving the problem was much more interesting than running a company. I now use Perl and Win32::ODBC to help with my Web site, generating hundreds of pages from several different databases.

Also, be sure to check out the Win32::ODBC FAQ, at http://www.roth.net/odbc/odbcfaq.htm.

_ _END_ _

Joe Casadonte (joc@netaxs.com) is an Engineering Manager for Manugistics, Inc. In his spare time, he advocates the use of Perl to anyone who will listen, especially on Win32 systems, which he dislikes intensely but is forced to use. He also delights in writing Perl scripts to do the most trivial tasks. His Perl for Win32 page is at http://www.netaxs.com/~joc/perlwin32.html.

PREVIOUS  TABLE OF CONTENTS  NEXT