Using the PurePerl MySQL API Result Set
Using the PurePerl MySQL API Part 4
Writing a Perl Module
Foreword: In this part of the series, I show you the basics on accessing the PurePerl MySQL API result set.
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
Remember, you begin your script that will use the API with:
use Mysql;
use strict;
If you are not using the Windows Perl, then you need a preceding statement before the above two, which is something like:
#!/usr/bin/perl
Also remember that your script has the extension, .pl and not .pm.
The @Result Array Variable
The variable, @Result holds an array. It has the result set. To access the array rows from your script the syntax is,
$Mysql::Result[$row_no]
You begin with Mysql since the variable is declared in the Mysql package (Mysql.pm file). This has to be followed with the double colon and then the text name of the array. Do not forget to precede the expression with $. Row number ($row_no) counting begins from zero (not 1).
This array is actually an array of hashes. To refer to the array alone, you would type:
@Mysql::Result
To refer to each row (of hashes) the syntax is:
$Mysql::Result[$row_no]
which returns a reference to the hash that forms the row.
To access a cell value of the result set, the syntax is:
$Mysql::Result[$row_no]{'column_name'}
where column-name is the name of the column of the result set.
You need a for-loop to read the contents of a number of rows of the result set. Before that you should know the maximum number of rows that have been sent by the server. This is stored in the scalar variable, No_of_Rows in the Mysql package. It is accessible similarly to the above as,
$Mysql::No_of_Rows
Do not forget to precede the expression, with $ as you would, for accessing all variables in the Mysql package. In your script, you can print out the number of rows to the console with:
print $Mysql::No_of_Rows;
If you want to read all contents of all the rows, of the table we are dealing with in this series, and print to the console, your for-loop would be like:
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";
}
Note how the column headings have been used. You can use other Perl techniques to modify the for-loop. I tried the code for the SQL statement, "SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'", and I had:
Chirpy, Susan, bird, f, 2008-09-11, ,
Singer, Susan, bird, , 2007-12-09, ,
Fat, Benny, snake, m, 2006-04-29, ,
Null values returned are coded as undef in the Mysql API; they are not printed.
You can print a sequence of rows, less than the total sequence. For this you will modify the first two expressions, my $i=0 and $Mysql::No_of_Rows in the first line of the for-loop. You can print selected rows one-by-one, by using the row index for $row_no.
Note: you can choose the cell to print by choosing the column name.
You can print out the column headings before printing the rows that follow. To do this you will precede the for-loop structure with a new for-loop, like:
for (my $j=0; $j<$Mysql::No_of_Columns; ++$j)
{
print $Mysql::Fields[$j][0], ' ';
}
print "\n";
The Mysql package has a variable, No_of_Columns, which is the number of columns in the result set. There is also a two-dimensional array in the Mysql package, called @Fields. The number of rows in the 2D array is the number of columns, one row for each column. The order of the rows from top to bottom is the order of the columns in the result set from left to right. The first cell of each row in the 2D array has the name of the column. The last print statement in the above code sends the cursor _ of the console, to the next line.
You can print a particular column name using its row position in the array (row positioning begins from zero). If you want to print the third column name for example, you would type,
print $Mysql::Fields[2][0], ' ';
The value of the second square brackets, [] should always be zero for column names. Note the [2] for the third column name.
To access a single value for a particular row and column, the syntax is:
$Mysql::Result[$row_no]{'column_name'}
In the previous part of the series, the single value of row 2 and the column with the heading, 'owner' was read with:
$Mysql::Result[1]{'owner'}
Note: row indexing in the result set begins from zero.
Example of Selecting and Displaying
A select and display code can be something like:
my $sel = "SELECT * FROM pet";
if (Mysql::query($sel) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
for (my $i=0; $i<2; ++$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";
}
}
The code prints the first 2 rows.
The query_num() Function
This function is the same as the query() function, but instead of returning an array of hashes, it returns an array of arrays. The array of arrays is @Result_Num. You should use this function when you already know the columns that are returned and their positions in the result set. The result set (array of arrays) for this function occupies less memory space than the result set (array of hashes) for the query() function. The following code illustrates the use of the function and the returned result set.
my $sel = "SELECT * FROM pet";
if (Mysql::query_num($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)
{
print $Mysql::Result_Num[$i][$j], ', '; ;
}
print "\n";
}
}
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