|Using Microsoft Access and other ODBC Datasources|
|Written by Philip L Yuson|
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.
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:
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:
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:
Copyright: © 2017 Philip Yuson