MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
HelloMySQL.pl - Sample Perl Program to Use MySQL
This section provides a sample Perl program, HelloMySQL.pl, which connects the MySQL server on the local machine, creates a table and inserts a record into the table.
Now we are ready to write a Perl program using the DBI module to access MySQL server. Here my first example, HelloMySQL.pl:
#- HelloMySQL.pl # Copyright (c) 2005 HerongYang.com. All Rights Reserved. # use DBI; # $dbh = DBI->connect("DBI:mysql:test","root","TopSecret"); $dbh = DBI->connect("DBI:mysql:test","herong","TopSecret"); $dbh->do("create table hello (message varchar(80))"); $dbh->do("insert into hello (message) values ('Hello world!')"); $sth = $dbh->prepare("select * from hello"); $sth->execute(); while ((@row) = $sth->fetchrow_array()) { print "$row[0]\n"; } $sth->finish(); $dbh->do("drop table hello"); $dbh->disconnect(); exit;
If you are running the above script with the default "root" user created on MySQL Server 8.0, you will get the following error:
BI connect('test','root',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at HelloMySQL.pl line 5.
Here is why. By default, all user accounts on MySQL Server 8.0 are created with the new authentication method called: caching_sha2_password. Unfortunately, caching_sha2_password is not supported by the "mysqli" module yet.
One solution is to change the user's authentication method back to "mysql_native_password", or create a new user with "mysql_native_password".
Here is how to create a new user "herong" with the old authentication method, "mysql_native_password" with ALL privileges on all databases and tables:
herong> %mysql%\bin\mysql.exe --user=root --password Enter password: TopSecret mysql> CREATE USER herong IDENTIFIED mysql> WITH mysql_native_password BY 'TopSecret'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL ON *.* TO herong; Query OK, 0 rows affected (0.01 sec)
Run the script again with user "herong", you will see the "Hello world!" message.
herong> \local\perl\bin\perl -version This is perl 5, version 18, subversion 1 (v5.18.1) built for MSWin32-x86-multi-thread-64int (with 1 registered patch, see perl -V for more detail) herong> \local\perl\bin\perl HelloMySQL.pl Hello world!
Congratulations, the sample Perl program is working. This proves that all required software and modules were installed correctly.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
►Perl Programs and MySQL Servers
Accessing MySQL Servers from Perl Programs
►HelloMySQL.pl - Sample Perl Program to Use MySQL
Java Programs and MySQL Servers
Character Strings and Bit Strings
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
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux