EOF and Result Set Packets for PurePerl MySQL API

Developing a PurePerl MySQL API – Part 7

Writing a Perl Module

Foreword: In this part of the series I explain EOF and result set packets in the PurePerl MySQL API.

By: Chrysanthus Date Published: 28 Jan 2015

Introduction

This is part 7 of my series, Developing a PurePerl MySQL API. In this part of the series I explain EOF and result set packets in the PurePerl MySQL API. PurePerl stands for Pure Perl, meaning Perl software without any C software underneath. Queries such as SELECT, SHOW, CHECK, REPAIR, and EXPLAIN return a result set, which comprises result set packets. EOF literally means End-of-File, but here it means end of a packet stream (sequence). EOF is actually a packet on its own. You should have read the previous parts of the series before reaching here, as this is a continuation.

The word, field in this article has two meanings. It can mean a byte sequence in a packet (string) and it can also mean the field (column heading) of a table in the database, at the server.

EOF Packet
EOF stands for end-of-file. The EOF packet is a packet. It is send at the end of a stream of packets from the server to indicate the end of the stream of packets. The stream consists of one or more packets.

The EOF packet can also be sent by the server to acknowledge a SHUTDOWN of the client. The EOF packet is also sent by the server as end of rows. The server sends one packet per row.

Any packet has the header segment and the body segment. The body of an EOF packet always starts with a byte containing decimal 254, i.e. “fe” in hex. In the pre-4.1 versions, there was nothing else in the body in addition to this byte.

The format of the EOF body today is:

**Table 7.1. Format of server's EOF packet**
Offset in the body	Length	Description
0	1	Byte with the decimal 254
1	2	Number of warnings
3	2	Server status bit mask

EOF Packet Package Code
The code of the package to handle an EOF packet (convert the string into a hash of user-friendly values) is:

package EOF;
our $VERSION = "1.01";

use strict;

    sub eof
        {

            my $eof_len = length($_[0]);
            my ($body_len_LBF, $seq_no, $eof_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);

            #no. of warnings
            my ($dummy0, $no_warnings_B) = unpack('A5A2', $_[0]);

            #Server status bit mask
            my ($dummy1, $srv_status_bit_mask) = unpack('A7A2', $_[0]);

            #EOF hash to return
            my %Ha = (
                body_len_D => $body_len_D,
                seq_no => $seq_no,
                eof_no => $eof_no,
                no_warnings_B => $no_warnings_B,
                srv_status_bit_mask => $srv_status_bit_mask
            );

            return %Ha;

        }

1;

It has one function with one argument. The argument is the EOF packet in string form.

The Result Set Packets
When a query like SELECT, SHOW, CHECK, REPAIR, or EXPLAIN is sent by the client, the server responds with a result set. 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 (again).

The following table gives the format description of each field packet. In the table the offset column is not given because of the varied lengths of the byte sequences (packet fields). Within the table, the word, “field” refers to the byte sequence.

**Table 7.1. Format of server's Result Set Field Packet**
Length	Description
4	Data field (see the section "Data Field," earlier in this chapter) containing the ASCII string def.
Varies	Database name of the field in the data field format.
Varies	Table name of the field in the data field format. If the table was aliased in the query, contains the name of the alias.
Varies	Table name of the field in the data field format. If the table was aliased in the query, contains the original name of the table.
Varies	Column name of the field in the data field format. If the column was aliased in the query, contains the name of the alias.
Varies	Column name of the field in the data field format. If the column was aliased in the query, contains the original name in the table.
1	Byte containing decimal 12, meaning that 12 bytes of data follow. The idea is to make the sequence look like a standard data field.
2	Character set code of the field (low byte first).
4	Field length (low byte first).
1	Type code of the field according to enum field_types.
2	Bit mask of field option flags (low byte first). See table below for the explanation of the bits.
1	Decimal-point precision of field values.
2	Reserved
Varies	Optional element. If present, contains the default value of the field in the standard field data format.

The following table brings us to the end of the series. After that we continue in the next part.

**Table 7.3. Option Flags in Server's Result Set Packets**
Hexadecimal bit value	Byte Value<	Description
0x0001	0000000000000001	The field value cannot be NULL (it is declared with the NOT NULL attribute at, the database table).
0x0002	0000000000000010	The field is a part of the primary key.
0x0004	0000000000000100	The field is a part of a unique key.
0x0008	0000000000001000	The field is a part of some non-unique key.
0x0010	0000000000010000	The field is a BLOB or TEXT.
0x0020	0000000000100000	The field was declared with the UNSIGNED attribute, which has the same meaning as the unsigned keyword in C.
0x0040	0000000001000000	The field has been declared with the ZEROFILL attribute, which tells the server to pad the numeric types with leading zeros in the output to fit the specified field length.
0x0080	0000000010000000	The field has been declared with the BINARY attribute, which tells the server to compare strings byte-for-byte in a case-sensitive manner.
0x0100	0000000100000000	The field is an ENUM.
0x0200	0000001000000000	The field has been declared with the AUTO_INCREMENT attribute, which enables the automatic generation of primary key values when a new record is inserted.
0x0400	0000010000000000	The field is a timestamp.
0x0800	0000100000000000	The field is a SET.
0x8000	1000000000000000	Used with cursors in MySQL version 4.1 and up to indicate that the field is numeric.

Broad Network

Related Articles

EOF and Result Set Packets for PurePerl MySQL API

Developing a PurePerl MySQL API – Part 7

Writing a Perl Module

Introduction

Related Links

Comments