Perl DBD is a driver which we can use in order to connect through DBI to a specific database (such as MySQL, Oracle, MS Access, etc). DBI is a database-independent interface for the Perl programming language. I’ll exemplify using PerlActive installed on a Windows platform and I’ll connect to a Paradox database through a DBD::ODBC driver. I’ll show you how to use Perl DBD module in a demonstrative example, step by step.
My ActivePerl application is installed on my computer in C:\Perl and has the 5.8.8 version. I had also installed the DBI module, 1.602 version (see DBI Perl Module ).
From Perl point of view, I need only to install the Perl DBD driver DBD-ODBC, but I’ll do this later.
In order to connect to a Paradox database, I had installed the BDE (Borland Database Engine) on my computer, too. My database is called Shop and includes only 2 tables: Customers and Orders and has the location F:\Shop. If you want to see the structures of the two tables, please see Perl Database Programming (the relational database example).
In order to create an ODBC connection to our database (Shop), I must choose the driver to use and give this connection a name. Usually, a database is uniquely identified by a DSN (Data Source Name) – an “alias” that points to the file or server that the database is held in. We need an ODBC connection to connect from a Perl script to the Shop database.
To make an ODBC connection, please follow the steps below:
- click button Windows “Start”
- select “Control Panel”
- double click the icon “Administrative Tools”
- double click the icon “Data Sources (ODBC)”
- click the tab “System DSN”
- click the button Add
- select the driver “Microsoft Paradox Driver (*.db)”
- click the button Finish
- in “Data Source Name” field type in your database name (in our example Shop)
- clear the option “Use Current Directory”
- click the button “Select Directory…”
- select the directory where the database is located (in our example F:\Shop – is located on the same computer where ActivePerl is installed) and confirm by clicking OK
- confirm through OK the configuration of this DSN
- close the “ODBC Data Source Administrator” window
That’s all, we have now an ODBC connection to our Shop database.
Let’s first check up if we have the Perl DBD::ODBC module installed. In a Command Prompt run the the following command:
perl –e "use DBD::ODBC;"
and if you’ll receive the message error “Can’t locate DBD/ODBC.pm in @INC …” that means DBD::ODBC is not installed.
If this package is already installed, you can find out what version it is by running the following command:
perl –MDBD::ODBC –e “print $DBD::ODBC::VERSION;”
For demonstrative purpose, I uninstalled this module, so let’s show you how to install it.
In a Command Prompt run the ppm command without paramaters and it will open the Perl Package Manager window, where you will see what packages are available and which ones are installed. In order to install a package from here, you must have an open connection through Internet.
Right click the package name DBD-ODBC, mark the install option and then run the marked actions. In the Status window you will see the following dialog messages:
Preparing install to site area of:
Downloading DBD-ODBC-1.15 … DONE
Unpacking DBD-ODBC-1.15 … DONE
Generating HTML for DBD-ODBC-1.15 … DONE
Installing to site area … DONE
and after that I right click again the package name and I choose the verify option:
Verifying DBD-ODBC …
9 files verified
I think it is better to install a Perl DBD driver using this ppm window, than search CPAN for the appropriate version and choose the right one.
Well, everything looks OK, we can close the PPM window and do the next step.
The next step is the Perl script example itself:
# PERL MODULES WE WILL BE USING
# PERL DBI & Perl DBD
# CONFIG VARIABLES
$platform = “ODBC”;
$database = “Shop”;
$host = “localhost”;
$port = “3000”;
$tablename = “customers.db”;
# We don’t need user and password for our database
# $user = “username”;
# $pw = “password”;
#DATA SOURCE NAME
$dsn = “dbi:ODBC:Shop”;
# PERL DBI CONNECT
# $dbshop = DBI->connect($dsn, $user, $pw)
# We don’t use user and password
$dbshop = DBI->connect($dsn) ||
die “Could not connect to database: $DBI::errstr”;
# PREPARE THE QUERY (add a record in Customers table)
$query = “INSERT INTO Customers
(CustomerId, Name, Phone, ZipCode)
VALUES (3, ‘Jeff Johnson’, ‘216-522-2267′,’44108’)”;
$queryHandle = $dbshop->prepare($query);
# EXECUTE THE QUERY
# DBI DISCONNECT
$dbshop->disconnect if ($dbshop)
Keep in mind that this script is only a demonstrative example to show you how to connect from a Perl script to a specific database through Perl DBD:ODBC driver.