Result Set Packet Code for PurePerl MySQL API
Developing a PurePerl MySQL API – Part 8
Writing a Perl Module
Foreword: In this part of the series I talk about the code of result set packets for the PurePerl MySQL API.
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
Recall
Each result set consists of a series of packets. A result set consists of packets in the following order:
- A packet with the body consisting of the standard field-length specifier sequence. However, this time, the meaning of the number is different. It indicates the number of fields (database table column titles) in the result set.
- A group of field description packets (see the upcoming explanation for the format description); one packet for each field, in the field order of the result set. “field” here refers to a database table column title.
- A terminating EOF packet to end the field stream (packet sequence).
- The row packets follow, one packet per row. Each row data packet consists of a sequence of values stored in the standard field data format. Here, “field” means sequence of bytes.
- After all the data rows have been sent, the packet sequence is terminated with an EOF packet.
In this part of the series, I talk about the code for the option flags of the database table field packet, the database table field packet and the row packet.
I will not talk about the EOF packet code in this part of the series, because I talked about it in the previous part of the series.
One of the byte sequences of the result set field packet is the Bit mask of field option flags (low byte first). I mentioned this in the previous part of the series. This is a double-byte whose byte order has to be changed before being used.
When they talk about a flag, they are referring to a bit of 1 in a byte sequence. The byte sequence sent by the server actually has many flags, combined into two bytes interpreted in hexadecimal. You may have something like, 0x0FD3. In this topic, this flags actually have important data, so in code, you have to extract the individual bits of 1 to get the data. The position of the bit of 1 matters in the byte sequence. After extracting the bit, you look at the Option Flags in the previous part of the series, to know the meaning of the bit in the byte sequence.
To extract a bit and its position, you do bit-wise AND between the received byte sequence and a byte sequence where the bit of 1 in its position is the only byte of 1. This bit-wise AND is done with decimal numbers. The function to do that takes one argument which is the received byte sequence reordered to high-byte-first. The function is:
sub flag
{
my @Field_Flad_Meaning;
my $hex_str = unpack('H*',$_[0]);
if (($hex_str & hex("0001")) == hex("0001"))
{
push(@Field_Flad_Meaning, "The field value cannot be NULL.");
}
if (($hex_str & hex("0002")) == hex("0002"))
{
push(@Field_Flad_Meaning, "The field is a part of the primary key.");
}
if (($hex_str & hex("0004")) == hex("0004"))
{
push(@Field_Flad_Meaning, "The field is a part of a unique key.");
}
if (($hex_str & hex("0008")) == hex("0008"))
{
push(@Field_Flad_Meaning, "The field is a part of some non-unique key.");
}
if (($hex_str & hex("0010")) == hex("0010"))
{
push(@Field_Flad_Meaning, "The field is a BLOB or TEXT.");
}
if (($hex_str & hex("0020")) == hex("0020"))
{
push(@Field_Flad_Meaning, "The field was declared with the UNSIGNED attribute.");
}
if (($hex_str & hex("0040")) == hex("0040"))
{
push(@Field_Flad_Meaning, "The field has been declared with the ZEROFILL attribute.");
}
if (($hex_str & hex("0080")) == hex("0080"))
{
push(@Field_Flad_Meaning, "The field has been declared with the BINARY attribute.");
}
if (($hex_str & hex("0100")) == hex("0100"))
{
push(@Field_Flad_Meaning, "The field is an ENUM.");
}
if (($hex_str & hex("0200")) == hex("0200"))
{
push(@Field_Flad_Meaning, "The field has been declared with the AUTO_INCREMENT attribute.");
}
if (($hex_str & hex("0400")) == hex("0400"))
{
push(@Field_Flad_Meaning, "The field is a timestamp.");
}
if (($hex_str & hex("0800")) == hex("0800"))
{
push(@Field_Flad_Meaning, "The field is a SET.");
}
if (($hex_str & hex("8000")) == hex("8000"))
{
push(@Field_Flad_Meaning, "Used with cursors to indicate that the field is numeric.");
}
return @Field_Flad_Meaning;
}
The result set packets are the Number of Fields Packet, the field packet, the row packet and the EOF packet. I talk about the code for these packets in the rest of this article.
The Number of Fields Packet
The number of fields’ packet consists of the header segment and the body segment, which has one byte that is the number of fields. This packet is handled by the code in the Mysql.pm file that produces a two-dimensional array for the column properties. Each row in the 2D array is for a column.
The Field Packet
After the Number of Fields packet has come, the field series of packets come until you have the EOF packet. The aim of any packet package is to convert the packet byte string into a hash (or variable) and return, with the packet field values in a user-friendly manner. Each of the series of field packets is handled by the Field package. It returns a hash of the field values. One of the values of the hash is a reference to a one-dimensional array where each cell in the 1D array has a string that is a characteristic of the column (field).
The packet has two functions: field() and flag(), with the field() function being the main function that calls the flag() function. The flag() function determines the meaning of the field flags, and produces a 1D array. The flag() function is called for each column of the result set.
The Field packet is long; you will just have to see the code when you download the whole library.
The Row Packet
The row packet receives a byte string of a row and returns an array of the row values. It has one main function, which is called from the Mysql package as follows:
my %Ha = Row::row($all_result_Bytes, $No_of_Columns, $templ_row_begins);
Here the argument, $all_result_Bytes is the sequence of all the result set packets. All the result set packets are placed, one immediately following the next. The argument, $No_of_Columns is the number of columns (fields) in the result set. The argument, $templ_row_begins indicates the position in the total sequence of packets, where to start extracting data.
The row package is long; you will just have to see it when you download the whole library.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Internet Sockets and PerlPerl pack and unpack Functions
Writing MySQL Protocol Packets in PurePerl
Developing a PurePerl MySQL API
Using the PurePerl MySQL API
More Related Links
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