Question

Metadata is information about the data in the database. Metadata in MySQL contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.

Method name	Description
column_info()	Provides information about columns
table_info()	Provides information about tables
primary_key_info()	Provides information about primary keys in tables
foreign_key_info()	Provides information about foreign keys in tables

The above table lists four Perl DBI methods, which are used to retrieve metadata.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:mysql:dbname=mydb", 
  "user12",              
  "34klq*",              
  { RaiseError => 1 },     
) or die $DBI::errstr;

my $sth = $dbh->primary_key_info(undef, "mydb", "Cars");
my @ary = $sth->fetchrow_array();
    
print join(" ", @ary), "\n";

$sth->finish();
$dbh->disconnect();

In the first example, we will find out information about a primary key in the Cars table.

my $sth = $dbh->primary_key_info(undef, "main", "Cars");

The primary_key_info() returns an active statement handle that can be used to fetch information about columns that make up the primary key for a table.

my @ary = $sth->fetchrow_array();

From the statement handle, we retrieve the information.

$ ./pk_info.pl
 mydb Cars Id 1 PRIMARY

From the output we can see that there is a primary key in the Cars table. The primary key is the first column, named Id.

Next we will print all rows from the Cars table with their column names.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:mysql:dbname=mydb", 
  "user12",              
  "34klq*",              
  { RaiseError => 1 },     
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 8" );  
$sth->execute();

my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf  "%s %-10s %s\n", $id, $name, $price;

my $row;
while($row = $sth->fetchrow_hashref()) {
  printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

$sth->finish();
$dbh->disconnect();

We print the contents of the Cars table to the console. Now, we include the names of the columns too. The records are aligned with the column names.

my $headers = $sth->{NAME};

We get the column names from the statement object.

my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;

The column names are printed to the console. We apply some formatting with the printf function.

my $row;
while($row = $sth->fetchrow_hashref()) {
  printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

The data is retrieved, formatted, and printed to the terminal.

$ ./column_names.pl
Id Name     Price
 1 Audi     52642
 2 Mercedes   57127
 3 Skoda    9000
 4 Volvo    29000
 5 Bentley  350000
 6 Citroen  21000
 7 Hummer   41400
 8 Volkswagen 21601

Output of the column_names.pl script.

In our last example related to metadata, we will list all tables in the test.db database.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:mysql:dbname=mydb", 
  "user12",              
  "34klq*",              
  { RaiseError => 1 },     
) or die $DBI::errstr;

my @tables = $dbh->tables(); 

foreach my $table ( @tables ) {
   print "Table: $table\n"; 
}

$dbh->disconnect();

The code example prints all available tables in the current database to the terminal.

my @tables = $dbh->tables();

The table names are retrieved with the tables() method.

$ ./list_tables.pl
Table: `mydb`.`Cars`
Table: `mydb`.`Friends`
Table: `mydb`.`Images`

These were the tables on our system.

In this part of the MySQL Perl tutorial, we have worked with database metadata.