Administrative and Utility Statements with PurePerl MySQL API
Using the PurePerl MySQL API – Part 5
Writing a Perl Module
Foreword: In this part of the series, I talk about Administrative and Utility Statements with the PurePerl MySQL API.
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
Administrative Statements
Here I explain how to create a user and grant him a privilege. I also talk about the SHOW TABLES statement. The other administrative statements follow the examples here. I will create the user “john”, with password, “smith”. After that I will grant him the select privilege to the pet table of the database PetStore. The following code segment does this:
my $SQL_cr_user = "CREATE USER 'john' IDENTIFIED BY 'smith'";
if (Mysql::query($SQL_cr_user) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print $Mysql::Message, "\n";
}
If you know how to construct the MySQL SQL statement, then the rest is simple: just place the SQL statement without the ending semicolon, as a string argument to the query function.
I tried the code and there was no feedback. It still means the user with password was created. The server sent no feedback. If there were an error, the server would have sent an error message, back. In your script, you will have to type a custom confirmation message in the place of the variable, $Mysql::Message. You do not have to type any error custom message for $Mysql::Error_msg, because the server would always send back an error message whenever an error occurs.
After creating the user, you can grant him the select privilege for the pet table as follows:
my $SQL_sel_priv = "GRANT SELECT ON pet TO 'john'";
if (Mysql::query($SQL_sel_priv) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print $Mysql::Message, "\n";
}
Again, if you know how to construct the SQL statement, then the rest is simple: just place the SQL statement without the ending semicolon, as a string argument to the query function.
The server does not send any feedback when the privilege is granted. However, if an error occurred, it would send an error message, back. So, it is up to you to type a custom confirmation message in place of $Mysql::Message, for the ordinary user.
The granting of privileges does not necessarily have to be done at the same time the user is created; it can be done after.
The following code segment connects the user, “john” with password, “smith” to the server and selects the rows of the pet table where species = 'snake' OR species = 'bird':
if (Mysql::connect("john", "smith", "localhost", 3306) != 1)
{
print $Mysql::Error_msg;
}
else
{
#select a new database to work with
if (Mysql::select_db("PetStore") != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
my $sel = "SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'";
if (Mysql::query($sel) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
{
print $Mysql::Result[$i]{'name'}, ', ';
print $Mysql::Result[$i]{'owner'}, ', ';
print $Mysql::Result[$i]{'species'}, ', ';
print $Mysql::Result[$i]{'sex'}, ', ';
print $Mysql::Result[$i]{'birth'}, ', ';
print $Mysql::Result[$i]{'death'}, ', ';
print "\n";
}
}
}
}
Remember, once you create a user or grant privileges, the effects are saved in the server; you do not have to create the same user or grant the same privileges the next time to connect.
After selecting the PetStore database, root (user) can go one to show all the tables in the database as follows:
my $shw = "SHOW TABLES";
if (Mysql::query($shw) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
{
for (my $j=0;$j<$Mysql::No_of_Columns; ++$j)
{
my $column_name = $Mysql::Fields[$j][0];
print $Mysql::Result[$i]{$column_name}, ', ';
}
print "\n";
}
}
When you do not know the particular columns and number of columns returned, the technique of the inner for-loop here, becomes handy. Always use this technique when you do not know the particular columns and number of columns returned. You can modify the inner for-loop code to remove the comma printed at the end of each line.
I tried the code in my computer and I had, pet as the only table in the database, PetStore. That is correct.
The root user can also type SHOW DATABASES in place of SHOW TABLES to see the databases present in the server.
I talk only about the DESCRIDE Table_Name statement here. This statement returns a special kind of result set, which present certain information about the table. Just try the following code as root, to see the output (after selecting the database):
my $sel = "DESCRIBE pet";
if (Mysql::query($sel) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
{
for (my $j=0;$j<$Mysql::No_of_Columns; ++$j)
{
my $column_name = $Mysql::Fields[$j][0];
print $Mysql::Result[$i]{$column_name}, ', ';
}
print "\n";
}
}
We do not know the number and names of the returned columns, that is why the inner for-loop is what it is. I tried the code in my computer and I had:
name, varchar(20), YES, , , ,
owner, varchar(20), YES, , , ,
species, varchar(20), YES, , , ,
sex, char(1), YES, , , ,
birth, date, YES, , , ,
death, date, YES, , , ,
You can precede the outer for-loop with the following for-loop to have the special column headings:
for (my $j=0; $j<$Mysql::No_of_Columns; ++$j)
{
print $Mysql::Fields[$j][0], ' ';
}
print "\n";
I tried that and I had:
Field Type Null Key Default Extra
name, varchar(20), YES, , , ,
owner, varchar(20), YES, , , ,
species, varchar(20), YES, , , ,
sex, char(1), YES, , , ,
birth, date, YES, , , ,
death, date, YES, , , ,
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT