Cooperation with MySQL

Perl
database
here

I will explain how to use "MySQL" in Perl.

Explanation of DBI and DBI

First, I will explain about DBI and DBI as basic knowledge about dealing with MySQL. When connecting to a database programmatically, it is common to use something called DBI. Also, the actual interaction with the RDBMS is done through the DBD.

Common API DBI that can use various RDBMS

DBI is an API that does not depend on RDBMS (relational database management system). (DBI stands for Database Interface)

RDBMS includes MySQL, Oracle, DB2, Postgre SQL, etc., but since they are created by different people, their usage is slightly different. DBI allows you to connect and request SQL with a common description for different beta bases.

Functions for connecting to the database DBI

There is a module with the same name in Perl, so install it.

cpan DBI

Driver DBD that actually interacts with the RDBMS

DBI provides functions, but in reality DBD interacts with the database internally. There are various types of DBD, such as for MySQL, PostgreSQL, Oracle, and SQLite.

Install the DBD for MySQL. MySQL must be installed.

cpan DBD::mysql

Image of interaction between DBI, DBD and database server

DBI does not change when connecting to any database. This is because DBI is a collection of functions for connections that are designed to be database independent. The DBD actually exchanges data with the database.

| - --- | | - -- -- -- -- | | - -- -- -- -- |
| | | | | |
| | - -- -- | DBD::mysql | - -- -- - | MySQL |
| | | | | |
| | | - -- -- -- -- | | - -- -- -- -- |
DBI |
| | | - -- -- -- -- | | - -- -- -- -- |
| | | | | |
| | - -- -- | DBD::Oracle | - -- -- - | Oracle |
| | | | | |
| - --- | | - -- -- -- -- | | - -- -- -- -- |

Connect to MySQL server

Use the connect method to connect to the MySQL server.

use DBI;
my $dbh = DBI->connect("DBI: mysql: $database", $user, $password);

To connect to a remote MySQL server, specify the host name

use DBI;
my $dbh = DBI->connect(
  "DBI: mysql: $database: $hostname",
  $user,
  $password
);

To do.

You can also specify the port number.

use DBI;
my $dbh = DBI->connect(
  "DBI: mysql: $database: $hostname: $port",
  $user,
  $password
);

Database handle

The connect method returns a database handle used for database operations as a return value.

Use the database handle to interact with the MySQL server.

Error handling

If the database name is incorrect, the password is incorrect, or the database cannot be connected for some reason, undef will be returned in the return value. The content of the error at that time is

$DBI::errstr;

You can get it at. err is a number and errstr is the content of the error.

Since the database is a system outside the program, error handling is essential. In the first example, error handling is not performed, but error handling is always performed.

use DBI;
my $dbh = DBI->connect(
  "DBI: mysql: $database: $hostname",
  $user,
  $password
) or die "cannot connect to MySQL: $DBI::errstr";

Convert current time to MySQL datetime type

Use the localtime and strftime functions to convert the current time to a MySQL datetime type.

use POSIX 'strftime';

# MySQL datetime type (datetime type)
my $current_datetime = strftime("%Y-%m-%d%H:%M:%S", localtime);

# MySQL date type (date type)
my $current_date = strftime("%Y-%m-%d", localtime);

Extract "database definition difference" with "MySQL::Diff" module

When I was looking for a tool to automatically generate commands that eliminate the differences from the differences in the MySQL database, I found MySQL::Diff module. This module comes with a command line tool called mysqldiff, so it's usually best to use it.

Very useful for bridging the gap between development and production environments. It doesn't show all the differences, it seems to be only the part related to the table definition, so it seems that you need to set the trigger and index yourself.

The documentation doesn't seem to explain the options, so you can check with the - help command.

mysqldiff - help

You need mysql - devel to install DBD::mysql

If you want to connect to MySQL from DBI with Perl, you need a module called DBD::mysql . This module depends on the C language library and will fail to install on its own. .. For example, if you have a Red Hat OS such as Cent OS, you need to install the package mysql-devel .

yum -y install mysql-devel

Once "mysql-devel" is installed, the DBD::mysql installation will be successful.

cpan DBD::mysql

Perl ABC