MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Ubuntu, Perl & MySQL

without comments

Configuring Perl to work with MySQL is straight forward. While Perl is installed generally, you may need to install the libdbd-mysql-perl library.

You install it as a sudoer user with this syntax:

sudo apt install -y libdbd-mysql-perl

Display detailed console log →

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libmysqlclient21
The following NEW packages will be installed:
  libdbd-mysql-perl libmysqlclient21
0 upgraded, 2 newly installed, 0 to remove and 12 not upgraded.
Need to get 1,389 kB of archives.
After this operation, 7,143 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libmysqlclient21 amd64 8.0.35-0ubuntu0.22.04.1 [1,301 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libdbd-mysql-perl amd64 4.050-5ubuntu0.22.04.1 [87.6 kB]
Fetched 1,389 kB in 1s (1,213 kB/s)     
Selecting previously unselected package libmysqlclient21:amd64.
(Reading database ... 235085 files and directories currently installed.)
Preparing to unpack .../libmysqlclient21_8.0.35-0ubuntu0.22.04.1_amd64.deb ...
Unpacking libmysqlclient21:amd64 (8.0.35-0ubuntu0.22.04.1) ...
Selecting previously unselected package libdbd-mysql-perl:amd64.
Preparing to unpack .../libdbd-mysql-perl_4.050-5ubuntu0.22.04.1_amd64.deb ...
Unpacking libdbd-mysql-perl:amd64 (4.050-5ubuntu0.22.04.1) ...
Setting up libmysqlclient21:amd64 (8.0.35-0ubuntu0.22.04.1) ...
Setting up libdbd-mysql-perl:amd64 (4.050-5ubuntu0.22.04.1) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.5) ...

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 program name with perl. The first line invokes a subshell of perl by default. You just need to ensure the file has read and execute privileges to run by using the

chmod 755 version.pl

You call it with this:

./version.pl

It prints:

Perl 5.034000

The following static_query.pl Perl program uses the Perl DBI library to query and return a data set based on a static query.

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
26
27
28
29
30
31
32
33
34
35
36
37

#!/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 i.item_title
           ,      ra.rating
           ,      cl.common_lookup_meaning
           FROM   item i INNER JOIN common_lookup cl
           ON     i.item_type = cl.common_lookup_id INNER JOIN rating_agency ra
           ON     i.item_rating_id = ra.rating_agency_id
           WHERE  i.item_title LIKE 'Harry%'
           AND    cl.common_lookup_type = 'BLU-RAY'";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
 
# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  my $rating = $row[1];
  my $lookup_meaning = $row[2];
  print "$item_title, $rating, $lookup_meaning\n";
}
 
# Close resources.
$sth->finish();

It returns the following rows from the sample database:

Harry Potter and the Sorcerer's Stone, PG, Blu-ray
Harry Potter and the Chamber of Secrets, PG, Blu-ray
Harry Potter and the Prisoner of Azkaban, PG, Blu-ray
Harry Potter and the Goblet of Fire, PG-13, Blu-ray

The following dynamic_query.pl Perl program uses the Perl DBI library to prepare a query, bind a local variable into the query, and return a data set based on a dynamic query.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

#!/usr/bin/perl -w
 
# Use the DBI library.
use DBI;
use strict;
use warnings;
 
# Mimic a function parameter by using a local variable.
my $item_title_in = 'Star';
 
# Create a connection.
my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306"
                      ,"student","student",{'RaiseError' => 1});
 
# Create SQL statement.
my $sql = "SELECT i.item_title
           ,      ra.rating
           ,      cl.common_lookup_meaning
           FROM   item i INNER JOIN common_lookup cl
           ON     i.item_type = cl.common_lookup_id INNER JOIN rating_agency ra
           ON     i.item_rating_id = ra.rating_agency_id
           WHERE  i.item_title LIKE CONCAT(?,'%')
           AND    cl.common_lookup_type = 'BLU-RAY'";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Bind a variable to first parameter in the query string.
$sth->bind_param(1, $item_title_in);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
 
# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  my $rating = $row[1];
  my $lookup_meaning = $row[2];
  print "$item_title, $rating, $lookup_meaning\n";
}
 
# Close resources.
$sth->finish();

It returns the following rows from the sample database:

Star Wars II, PG, Blu-ray

You can replace lines 34 through 40 with the following to read any number of columns into a comma-delimited row return:

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
 
  # Read through a dynamic column list for column separated display.
  my $result = '';
  foreach(@row) {
    if (length($result) == 0) {
      $result = $_; }
    else {
      $result .= ", " . $_; }
  } 
 
  # Print comma-separted values by row.
  print $result . "\n"
}

It returns the following rows from the sample database:

Star Wars II, PG, Blu-ray

As always, I hope this helps the reader solve a problem.

Written by maclochlainn

January 8th, 2024 at 10:37 pm

Posted in Linux,MySQL,MySQL 8,Ubuntu,Unix

Tagged with , ,

«
»