MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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 ,

«
»