Using Microsoft Access and ODBC Data Sources

After downloading Perl to Windows, you are all ready to go. ActivePerl and IndigoPerl come with some commonly used modules. One of these is the Win32::ODBC module.

The Win32::ODBC module contains functions that allow the Perl program to access any ODBC data source defined in the system. Since Microsoft Access data bases can also be defined as ODBC data sources, you can also use Win32::ODBC to access it.

You need to define an ODBC DSN on the workstation.

At the start of your program, you will have to tell Perl that you want to use this module. You will have to include this statement in your program:

use Win32::ODBC;

Connecting to ODBC DSN

As in other programming languages, you have to open a data connection to your DSN. You do this using the constructor for this module.

$odbc = new Win32::ODBC(DSN);

The DSN can be the data source defined by the ODBC administrator or a complete ODBC connect string. If you did not define an ODBC DSN, then you will have to construct the entire connect string.

After starting a connection, check if the DSN was connected. You can use the Connection method of the module:

unless ($odbc->Connection) { die "Connection not made" }

Retrieving a Row

When a connection is made, you have to Run an SQL "SELECT" statement to read from any table in the data base. Once you have executed a SELECT statement, you will have to FetchRow first before you can retrieve the values of the columns you want.

Only after doing a FetchRow method can then retrieve the column(s) you want. Use the Data method to get the value of one column or the entire row (unformatted). Use the DataHash method to retrieve values of multiple columns onto a hash.

This is the code to Select a table and read all the rows in the table:

# Execute Select statement
$odbc->Run("Select * From Log");

# Fetch the next record
while ($odbc->FetchRow) {

# Get entire row
$row = $odbc->Data;

#Get the value in the Description column
$col = $odbc->Data("Description");

#Get the values of the columns. Keys are the column names
%hash = $odbc->DataHash();

#Get values for Description, Start and End Columns
%hashselect = $odbc->DataHash("Description, Start, End")
}

Note that you can do multiple Data and DataHash without affecting the contents of the column. The row is changed only when you do a FetchRow.