Accessing MySQL Servers from Perl Programs

A tutorial example is provided on what programs and modules are needed to access MySQL servers from Perl programs, and how to install them.

Here is what we need to access MySQL servers from Perl programs:

For Perl implementation, I have installed ActivePerl. See my other book "Perl Tutorials - Herong's Tutorial Examples" at herongyang.com/Perl for detailed information.

To install MySQL server, see the previous chapters of this book.

Perl DBI (Database Interface) module is the basic abstraction layer for working with external database servers. DBI module seems to be included in the default ActivePerl 5.16 installation. No need to download and install it yourself. If DBI module is not available on your Perl installation, like ActivePerl 5.6, you could to go to http://search.cpan.org/dist/DBI/ and download it as .tar file. But installing DBI module from the .tar file seems to be too complicated. You can also install the DBI module using the PPM (Perl Package Management) tool as shown below:

herong> \local\perl\bin\ppm
PPM interactive shell (2.1.5) - type 'help' for available commands.
PPM> install DBI
Install package 'DBI?' (y/N): y
Installing package 'DBI'...
Bytes transferred: 435739
Installing D:\Perl\site\lib\auto\DBI\dbd_xsh.h
Installing D:\Perl\site\lib\auto\DBI\DBI.bs
......
Installing D:\Perl\html\site\lib\DBI.html
Installing D:\Perl\html\site\lib\Win32\DBIODBC.html
......
Installing D:\Perl\site\lib\DBI.pm
Installing D:\Perl\site\lib\Win32\DBIODBC.pm
......
Installing D:\Perl\bin\dbiprof
Installing D:\Perl\bin\dbiprof.bat
Installing D:\Perl\bin\dbiproxy
Installing D:\Perl\bin\dbiproxy.bat
Writing D:\Perl\site\lib\auto\DBI\.packlist
PPM> quit
Quit!

The DBD:mysql module is the database driver for MySQL required by the DBI module. It can also be installed by PPM, if you have ActivePerl installed. Here is what did with ActivePerl 5.18:

herong> \local\perl\bin\ppm install DBD-mysql
Downloading DBD-mysql-4.033...done
Unpacking DBD-mysql-4.033...done
Generating HTML for DBD-mysql-4.033...done
Updating files in site area...done
  11 files installed

As a comparison, the following shows the same installation session with ActivePerl 5.6:

herong> \local\perl\bin\ppm
PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 179124
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.bs
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.dll
......
Installing D:\Perl\html\site\lib\Mysql.html
Installing D:\Perl\html\site\lib\DBD\mysql.html
......
Installing D:\Perl\site\lib\Mysql.pm
Installing D:\Perl\site\lib\Mysql\Statement.pm
......
Writing D:\Perl\site\lib\auto\DBD\mysql\.packlist

But if you want to download the DBD:mysql module and install it yourself, you can go to http://www.mysql.com and download it.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

Perl Programs and MySQL Servers

Accessing MySQL Servers from Perl Programs

 HelloMySQL.pl - Sample Perl Program to Use MySQL

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB