MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Perl DBI’ Category

AlmaLinux Install & Configuration

without comments

This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.

I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.

If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.

Written by maclochlainn

December 21st, 2022 at 5:29 pm

Posted in AlmaLinux,Apache,Database,DBA,Java,Java 17,LAMP,Linux,MySQL,MySQL 8,MySQL Connect/J,Oracle,Oracle 11g,Oracle DBA,Oracle Developer,Oracle XE,pdo,Perl,Perl DBI,pgAdmin4,PHP,Postgres,PostgreSQL,PostgreSQL 15,Python 3.x,Rust,VMWare,VSCode

Tagged with , , , , , , , ,

AlmaLinux MySQL+Perl

without comments

A quick primer on Perl programs connecting to the MySQL database. It’s another set of coding examples for the AlmaLinux instance that I’m building for students. This one demonstrates basic Perl programs, connecting to MySQL, returning data sets by reference and position, dynamic queries, and input parameters to dynamic queries.

  1. Naturally, a hello.pl is a great place to start: 

    #!/usr/bin/perl
 
# Hello World program.
print "Hello World!\n";

    After setting the permissions to -rwxr-xr-x. with this command:

    chmod 755 hello.pl

    You call it like this from the Command-Line Interface (CLI):

    ./hello.pl

    It prints:

    Hello World!
  2. Next, a connect.pl program lets us test the Perl::DBI connection to the MySQL database. 

    #!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name should have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1);   # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";

    After setting the permissions to -rwxr-xr-x. you call it with this CLI command:

    ./connect.pl

    It prints:

    Perl MySQL Connect Attempt.
Connected to the MySQL database.
  3. After connecting to the database lets query a couple columns by reference notation in a static.pl program. This one just returns the result of the MySQL version() and database() functions. 

    #!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name must have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1); # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Creaet a static SQL statement or query.
my $sth = $dbh->prepare("SELECT version() AS version \
	                 ,      database() AS db_name");
 
# Execute the static statement.
$sth->execute() or die "Execution failed: $dbh->errstr()";
 
# Read data and print by reference.
print "----------------------------------------\n";
while (my $ref = $sth->fetchrow_hashref()) {
  print "MySQL Version:  $ref->{'version'}\nMySQL Database: $ref->{'db_name'}\n";
}
print "----------------------------------------\n";
 
# Close the statement.
$sth->finish;
 
# Disconnect from database connection.
$dbh->disconnect();
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";

    After setting the permissions to -rwxr-xr-x. you call it with this CLI command:

    ./static.pl

    It prints:

    Perl MySQL Connect Attempt.
----------------------------------------
MySQL Version:  8.0.30
MySQL Database: sakila
----------------------------------------
Connected to the MySQL database.
  4. After connecting to the database and securing variables by reference notation, lets return the value as an array of rows in a columns.pl program. This one just returns data from the film table of the sakila database. It is a static query because all the values are contained inside the SQL statement. 

    #!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name must have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1); # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Creaet a static SQL statement or query.
my $sth = $dbh->prepare("SELECT title         \
	                 ,      release_year  \
			 ,      rating        \
			 FROM   film          \
			 WHERE  title LIKE 'roc%'");
 
# Execute the static statement.
$sth->execute() or die "Execution failed: $dbh->errstr()";
 
# Read data and print by comma-delimited row position.
print "----------------------------------------\n";
while (my @row = $sth->fetchrow_array()) {
  print join(", ", @row), "\n";
}
print "----------------------------------------\n";
 
# Close the statement.
$sth->finish;
 
# Disconnect from database connection.
$dbh->disconnect();
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";

    After setting the permissions to -rwxr-xr-x. you call it with this CLI command:

    ./columns.pl

    It prints:

    Perl MySQL Connect Attempt.
----------------------------------------
ROCK INSTINCT, 2006, G
ROCKETEER MOTHER, 2006, PG-13
ROCKY WAR, 2006, PG-13
----------------------------------------
Connected to the MySQL database.
  5. After connecting to the database and securing variables by reference notation, lets return the value as an array of rows in a dynamic.pl program. This one just returns data from the film table of the sakila database. It is a dynamic query because a string passed to the execute method and that value is bound to a ? placeholder in the SQL statement. 

    #!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name must have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1); # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Creaet a static SQL statement or query.
my $sth = $dbh->prepare("SELECT title         \
	                 ,      release_year  \
			 ,      rating        \
			 FROM   film          \
			 WHERE  title LIKE CONCAT(?,'%')");
 
# Execute the dynamic statement by providing an input parameter.
$sth->execute('roc') or die "Execution failed: $dbh->errstr()";
 
# Read data and print by comma-delimited row position.
print "----------------------------------------\n";
while (my @row = $sth->fetchrow_array()) {
  print join(", ", @row), "\n";
}
print "----------------------------------------\n";
 
# Close the statement.
$sth->finish;
 
# Disconnect from database connection.
$dbh->disconnect();
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";

    After setting the permissions to -rwxr-xr-x. you call it with this CLI command:

    ./dynamic.pl

    It prints:

    Perl MySQL Connect Attempt.
----------------------------------------
ROCK INSTINCT, 2006, G
ROCKETEER MOTHER, 2006, PG-13
ROCKY WAR, 2006, PG-13
----------------------------------------
Connected to the MySQL database.
  6. After connecting to the database and securing variables by reference notation, lets return the value as an array of rows in a input.pl program. This one just returns data from the film table of the sakila database. It is a dynamic query because an input parameter is passed to a local variable and the local variable is bound to a ? placeholder in the SQL statement. 

    #!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Get the index value of the maximum argument in the
# argument.
my $argc = $#ARGV;
 
# Accept first argument value as parameter.
my $param = $ARGV[$argc];
 
# Verify variable value assigned.
if (not defined $param) {
  die "Need parameter value.\n";
}
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name must have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1); # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Creaet a static SQL statement or query.
my $sth = $dbh->prepare("SELECT title         \
	                 ,      release_year  \
			 ,      rating        \
			 FROM   film          \
			 WHERE  title LIKE CONCAT(?,'%')");
 
# Execute the static statement.
$sth->execute($param) or die "Execution failed: $dbh->errstr()";
 
# Read data and print by comma-delimited row position.
print "----------------------------------------\n";
while (my @row = $sth->fetchrow_array()) {
  print join(", ", @row), "\n";
}
print "----------------------------------------\n";
 
# Close the statement.
$sth->finish;
 
# Disconnect from database connection.
$dbh->disconnect();
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";

    After setting the permissions to -rwxr-xr-x. you call it with this CLI command:

    ./input.pl ta

    It prints:

    Perl MySQL Connect Attempt.
----------------------------------------
TADPOLE PARK, 2006, PG
TALENTED HOMICIDE, 2006, PG
TARZAN VIDEOTAPE, 2006, PG-13
TAXI KICK, 2006, PG-13
----------------------------------------
Connected to the MySQL database.

I think these examples cover most of the basic elements of writing Perl against the MySQL database. If I missed something you think would be useful, please advise. As always, I hope this helps those working with the MySQL and Perl products.

Written by maclochlainn

November 17th, 2022 at 12:01 am

Posted in AlmaLinux,Database,DBA,DevOps,MySQL,MySQL 8,MySQL Client,Perl,Perl DBI,sql

Tagged with ,

Perl-MySQL Program

with 2 comments

Configuring Perl to work with MySQL is the last part creating a complete Fedora Linux LAMP stack for my students. Perl is already installed on Fedora Linux.

I’ve also shown how to use PHP, Python, and Ruby languages to query a MySQL database on Linux. After installing this additional Perl DBI library, my students will have the opportunity to choose how they implement their LAMP solution.

You can find the Perl version with the following version.pl program:

1
2
3
4

#!/usr/bin/perl -w
 
# Print the version.
print "Perl ".$]."\n";

The first line lets you call the program without prefacing the perl program because it invokes a subshell of perl by default. You just need to ensure the file has read and execute privileges to run. It prints:

Perl 5.018004

You need to install the perl-DBD-MySQL library to enable Perl to work with MySQL. The following command loads the library:

yum install -y perl-DBD-MySQL

It prints the following log file:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.024-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package               Arch          Version                Repository     Size
================================================================================
Installing:
 perl-DBD-MySQL        x86_64        4.024-1.fc20           fedora        142 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 142 k
Installed size: 332 k
Downloading packages:
perl-DBD-MySQL-4.024-1.fc20.x86_64.rpm                      | 142 kB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : perl-DBD-MySQL-4.024-1.fc20.x86_64                           1/1 
  Verifying  : perl-DBD-MySQL-4.024-1.fc20.x86_64                           1/1 
 
Installed:
  perl-DBD-MySQL.x86_64 0:4.024-1.fc20                                          
 
Complete!

The following item_query.pl Perl program is consistent with the PHP, Python, and Ruby examples provided in other blog posts. It shows you how to use the Perl DBI library to query and return a data set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

#!/usr/bin/perl -w
 
# Use the DBI library.
use DBI;
use strict;
use warnings;
 
# Create a connection.
my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306","student","student",{'RaiseError' => 1});
 
# Create SQL statement.
my $sql = "SELECT item_title FROM item";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  print "$item_title\n";
}
 
# Close resources.
$sth->finish();

You call it like this from the present working directory:

./mysql_query.pl

It returns:

The Hunt for Red October
Star Wars I
Star Wars II
Star Wars II
Star Wars III
The Chronicles of Narnia
RoboCop
Pirates of the Caribbean
The Chronicles of Narnia
MarioKart
Splinter Cell
Need for Speed
The DaVinci Code
Cars
Beau Geste
I Remember Mama
Tora! Tora! Tora!
A Man for All Seasons
Hook
Around the World in 80 Days
Harry Potter and the Sorcerer's Stone
Camelot

Alternatively, there’s a different syntax for lines 20 and 21 that you can use when you’re returning multiple columns. It replaces the two statements inside the while loop as follows:

20
21

  my ($item_title, $item_rating) = @row;
  print "$item_title, $item_rating\n";

It returns:

The Hunt for Red October, PG
Star Wars I, PG
Star Wars II, PG
Star Wars II, PG
Star Wars III, PG13
The Chronicles of Narnia, PG
RoboCop, Mature
Pirates of the Caribbean, Teen
The Chronicles of Narnia, Everyone
MarioKart, Everyone
Splinter Cell, Teen
Need for Speed, Everyone
The DaVinci Code, Teen
Cars, Everyone
Beau Geste, PG
I Remember Mama, NR
Tora! Tora! Tora!, G
A Man for All Seasons, G
Hook, PG
Around the World in 80 Days, G
Harry Potter and the Sorcerer's Stone, PG
Camelot, G

As always, I hope this helps those learning how to use Perl and Linux against the MySQL Database. If you want a nice tutorial on Perl and MySQL, check The tutorialspoint.com web site.

Written by maclochlainn

April 13th, 2015 at 2:14 am

Posted in Fedora,LAMP,Linux,MySQL,Perl,Perl DBI,sql

Tagged with , ,