OK and Error Packets of PurePerl MySQL API

Developing a PurePerl MySQL API – Part 6

Writing a Perl Module

Foreword: In this part of the series I explain the OK and Error Packets of PurePerl MySQL API.

By: Chrysanthus Date Published: 28 Jan 2015

Introduction

This is part 6 of my series, Developing a PurePerl MySQL API. In this part of the series I explain the OK and Error Packets of PurePerl MySQL API. PurePerl stands for Pure Perl, meaning Perl software without any C software underneath. You should have read the previous parts of the series before reaching here, as this is a continuation.

The OK Packet
An OK packet is sent to indicate that the server successfully completed the command. It is sent in response to the following commands:

- COM_PING
- COM_QUERY if the query does not need to return a result set; for example, INSERT, UPDATE, or ALTER TABLE
- COM_REFRESH (see later)
- COM_REGISTER_SLAVE (see later)

I have talked about the ping command already. I will talk about the query command in the next part of the series. I will not talk about the last two commands (refresh and register_slave) in this series.

The OK packet can be feedback for commands that do not return a result set. Its format, however, permits sending some extra status information, such as the number of modified records, the value of the automatically generated primary key, or a custom status message in a string format. Remember, each packet has a header segment and a body segment. The structure of the packet body (segment) is given in Table 6.1 below:

**Table 6.1. Format of Server's OK packet Body**
Offset in the body	Length	Description
0	1	A byte with the value of 0, indicating that the packet has no fields.
1	rows_len	The number of records that the query has changed in the “field length” format described in the "Data Field" section, below. Its length varies depending on the value. I refer to its length as rows_len to express the subsequent offsets.
1 + rows_len	id_len	The value of the generated auto-increment ID for the primary key. Set to 0 if not applicable in the context. The value is stored in the “field length” format of a data field. I refer to the length of this value as id_len.
1 + rows_len + id_len	2	Server status bit mask, low byte first. For details on different values, consult some other document.
3 + rows_len + id_len	2	Present only in the protocol of version 4.1 and later. Contains the number of warnings the last command has generated. For example, if the command was COM_QUERY with LOAD DATA INFILE, and some of the fields or lines could not be properly imported, a number of warnings will be generated. The number is stored with the low byte first.
5 + rows_len + id_len in version 4.1 and later protocol.	msg_len	An optional field for the status message if one is present in the standard data field format with the field length followed by field value, which in this case is a character string.

Data Field
A data field is a sequence of bytes that forms a unit. Each column above can be considered as a data field. However, some data fields are further divided into two sub data fields. In this case, the first sub field is the length sub field and the second sub field is the value sub field. The value occupies a number of bytes. The value in the length sub field gives the number of bytes in the value sub field. In other words, the length sub field gives you the length of the value sub field. The length sub field is referred to as the length specifier sequence. The value in the value sub field is the data value.

The value field can be of any length depending on its value. The length specifier sequence, which has the length, in bytes, of the data value, can itself be of 1 byte, or 3 bytes or 5 bytes or 9 bytes.

If the number of bytes of the value sub field is less than 251, the length specifier sequence consists of 1 byte. This 1 byte is the length (decimal number of bytes) of the data value. If the data value is made up of 251 or more bytes, then the length specifier sequence consists of 3 bytes. The first of these three bytes is the specifier code and has the value, 252. The next two bytes of the length specifier sequence have the number of bytes (length) of the data value. If these two bytes were not enough to hold the number of bytes for the data value, then the length specifier sequence would consist of 5 bytes. The first byte is a specifier code and has the value, 253. The next 4 bytes have the number of bytes of the data value. If these four bytes were not enough to hold the number of bytes for the data value, then the length specifier sequence would consist of 9 bytes. The first byte is a specifier code and has the value, 254. The next 8 bytes have the number of bytes of the data value.

If the specifier code is, 251, it means the length specifier sequence has just one byte. It also means that there is no data value and no length for the data value. This is used to indicate that the actual value from the cell of the table in the database is NULL, which is not transmitted.

It must be noted that all length values following the specifier code are stored with the low byte first.

The function code to handle the lengths of the data field has two arguments: the first is the specifier code as a decimal number; the second is the right portion of the packet string, beginning from the specifier code (inclusive) in question. The function returns a hash, with three elements. The first element has the length of the length sub field, which may be 1 or 3 or 5 or 9. The second element has the length of the data value. The third element has the total length of the field. The Perl package (module) with the function is:

package Field_Lengths;
our $VERSION = "1.01";

use strict;

    sub field_lens
        {
            my %Ha;
            my $len_code = $_[0];

            if ($len_code < 251)
                {
                    $Ha{'len_len'} = 1;
                    $Ha{'val_len'} = $len_code;
                    $Ha{'field_len'} = 1+$len_code;
                }
            if ($len_code == 0)
                {
                    $Ha{'len_len'} = 1;
                    $Ha{'val_len'} = 0;
                    $Ha{'field_len'} = 1;
                }
            if ($len_code == 252)
                {
                    $Ha{'len_len'} = 3;
                    my ($dummy,$len_val_LBF) = unpack('AA2', $_[1]);
                    my $len_val_D = unpack('S<',$len_val_LBF);
                    $Ha{'val_len'} = $len_val_D;
                    $Ha{'field_len'} = 3 + $len_val_D;
                }
            if ($len_code == 253)
                {
                    $Ha{'len_len'} = 5;
                    my ($dummy,$len_val_LBF) = unpack('AA4', $_[1]);
                    my $len_val_D = unpack('L<',$len_val_LBF);
                    $Ha{'val_len'} = $len_val_D;
                    $Ha{'field_len'} = 5 + $len_val_D;
                }
            if ($len_code == 254)
                {
                    $Ha{'len_len'} = 9;
                    my ($dummy,$len_val_LBF) = unpack('AA8', $_[1]);
                    my $len_val_D = unpack('Q<',$len_val_LBF);
                    $Ha{'val_len'} = $len_val_D;
                    $Ha{'field_len'} = 9 + $len_val_D;
                }
            if ($len_code == 251)
                {
                    $Ha{'len_len'} = 1;
                    $Ha{'val_len'} = 0;
                    $Ha{'field_len'} = 1;
                }

            return %Ha;

        }

1;

The packet has one function. The function is not only used with the OK packet, it is used with the result set packets (see later).

The OK Packet Code
The aim of this code is to return the field values in a hash where the hash values are more user-friendly. To do this, you have to get the length of the body from the header (we know the header is already four bytes long). From the body length gotten from the header, you can then extract the different values in the body, based on the field lengths (byte sequences).  You can know the length of the whole packet using the Perl length() function. Another way to know the length of the body, is to subtract 4 bytes from the whole packet length. I did not quite use these rules; however, you should know them.

The OK package (module) has one function, called the ok() function. This function calls the field_lens() function, for field lengths from the Field_Lengths package. The OK module (package) is long and you will only see it when you download the whole library. The ok() function has one argument, which is the received OK packet in the form of a string.

This OK packet is in the file, OK.pm.

The Error Packet
When something goes wrong with the processing of a command, the server responds with an error packet. The format of the body segment is given in table 6.2.

**Table 6.2. Format of Server's Error Packet Body**
Offset in the body	Length	Description
0	1	A byte containing decimal 255, i.e ff in hex.
1	2	The error code. Low byte first.
3	2	Character '#' followed by the byte containing the value of the ODBC/JDBC SQL state.
5	Varies	Zero-terminated text of the error message.

The Error Packet Code
The Error package has one function. The function receives as argument the error packet in string form. It returns a hash where the fields of the error packet have been put in user-friendly form.

The code is:

package Error;
our $VERSION = "1.01";

use strict;

    sub error
        {

            my $error_len = length($_[0]);
            my ($body_len_LBF, $seq_no, $error_no) = unpack('A3A1A1', $_[0]);
            my ($body_len1,$body_len2,$body_len3) = unpack('AAA', $body_len_LBF);
            my $body_len_HBF = pack('AAA', $body_len3,$body_len2,$body_len1);
            my $body_len_hex_str = unpack('H*', $body_len_HBF); #\0 byte is substituted with 0x20 for space
            $body_len_hex_str =~ s/20/00/g;
            my $body_len_hex_32str = "00" . $body_len_hex_str;
            my $body_len_B = pack('H8', $body_len_hex_32str);
            my $body_len_D = unpack('L>',$body_len_B);

            #the error code
            my ($dummy0, $error_code1_B, $error_code2_B) = unpack('A5AA', $_[0]);
            my $error_code_HBF = $error_code2_B . $error_code1_B;

            #database type status
            my ($dummy1, $db_type_status) = unpack('A7A2', $_[0]);

            #erroe message
            my ($dummy2, $error_msg) = unpack('A9A*', $_[0]);

            my %Ha = (
                body_len_D => $body_len_D,
                seq_no => $seq_no,
                error_no => $error_no,
                error_code => $error_code_HBF,
                db_type_status => $db_type_status,
                error_msg => $error_msg
            );

            return %Ha;

        }

1;

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

OK and Error Packets of PurePerl MySQL API

Developing a PurePerl MySQL API – Part 6

Writing a Perl Module

Introduction

Related Links

Comments