Who is this for
This article is for those who want to know how to use Microsoft Access databases through Perl
This assumes that you already know these:
If you need to know more about these topics, go to any of the sites that offer free courses listed in my links.
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:
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.
Inserting, Deleting and Updating a Row
You will need to pass the INSERT, DELETE, UPDATE SQL statements to the Run method to insert, delete and update a table respectively.
Closing the Connection
After using the ODBC Connection, you will have to close it. Use the Close method to close the connection.
Where to Get More Information
For more information on:
Accessing ODBC data sources, look at the Perl documentation in your system or go to the ActivePerl site.
|
