Applying network functions, or MySQL without DLL: Part I - Connector

Serhii Shevchuk | 30 April, 2020

Contents

• Introduction
• Sockets
• Wireshark traffic analyzer
• Data exchange
• Receipt
• Sending
• MySQL transaction class
• Application
• Documentation
• Conclusion

Introduction

About a year ago, MQL5 network functions were replenished with functions for working with sockets. This opened up great opportunities for programmers developing products for the Market. Now they can implement things that required dynamic libraries before. We will consider one of such examples in this series of two articles. In the first article, I am going to consider the MySQL connector principles, while in the second one, I will develop the simplest applications using the connector, namely the service for collecting properties of signals available in the terminal and the program for viewing their changes over time (see Fig. 1).

Fig. 1. The program for viewing changes in signal properties over time

Sockets

A socket is a software interface for exchanging data between processes. The processes can be launched both on a single PC and on different ones connected into a network.

MQL5 provides client TCP sockets only. This means we are able to initiate a connection but we cannot wait for it from the outside. Therefore, if we need to provide a connection between MQL5 programs via sockets, we need a server which is to act as an intermediary. The server waits for a connection to the listened port and performs certain functions at the client's request. To connect to the server, we need to know its ip address and port.

A port is a number ranging from 0 to 65535. There are three port ranges: system (0 - 1023), user (1024-49151) and dynamic ones (49152-65535). Some ports are allocated to work with certain functions. The allocation is performed by IANA – an organization that manages IP address zones and top-level domains, as well as registers MIME data types.

The port 3306 is allocated to MySQL by default. We will connect to it when accessing the server. Please note that this value can be changed. Therefore, when developing an EA, the port should be set in the inputs along with the IP address.

The following approach is used when working with sockets:

• Create a socket (get a handle or an error)
• Connect to the server
• Exchange data
• Close the socket

When working with multiple connections, keep in mind the limitation of 128 simultaneously open sockets for a single MQL5 program.

Wireshark traffic analyzer

The traffic analyzer facilitates debugging the code of a program applying sockets. Without it, the entire process resembles repairing electronics without an oscilloscope. The analyzer captures data from the selected network interface and displays it in a readable form. It tracks the size of the packets, the time gap between them, the presence of retransmits and connection drops, as well as many other useful data. It also decrypts many protocols.

Personally, I use Wireshark for these purposes.

Fig. 2. Wireshark traffic analyzer

Figure 2 shows the traffic analyzer window with captured packets where:

1. Display filter line. "tcp.port==3306" means that only packets with a local or remote TCP port of 3306 are displayed (default MySQL server port).
2. Packets. Here we can see the connection setup process, server greeting, authorization request and subsequent exchange.
   
3. Selected packet content in hexadecimal form. In this case, we can see the contents of the MySQL server greeting packet.
   
4. Transport level (TCP). We are located here when using functions for working with sockets.
   
5. Application level (MySQL). This is what we are to consider in this article.
   

The display filter does not limit packet capture. This is clearly visible in the status bar stating that 35 captured packets out of 2623 ones located in the memory are being handled at the moment. To reduce the load on PC, we should set the capture filter when selecting the network interface, as shown in Fig. 3. This should be done only if all the other packages are really not useful.

Fig. 3. Packet capture filter

To familiarize ourselves with the traffic analyzer, let's try to establish a connection with the "google.com" server and track the process. To do this, write a small script.

void OnStart()
  {
//--- Get socket handle
   int socket=SocketCreate();
   if(socket==INVALID_HANDLE)
      return;
//--- Establish connection
   if(SocketConnect(socket,"google.com",80,2000)==false)
     {
      return;
     }
   Sleep(5000);
//--- Close connection
   SocketClose(socket);
  }

So, first we create a socket and get its handle using the SocketCreate() function. The reference says that in this case, you can get an error in two cases which are almost impossible:

1. The ERR_NETSOCKET_TOO_MANY_OPENED error signals that more than 128 sockets are open.
2. The ERR_FUNCTION_NOT_ALLOWED error appears when trying to call a socket creation from an indicator, in which this feature is disabled.

After receiving the handle, try to establish the connection. In this example, we connect to the "google.com" server (do not forget to add it to the allowed addresses in the terminal settings), namely, to port 80 with the timeout of 2 000 milliseconds. After establishing the connection, wait for 5 seconds and close it. Now let's see how it looks in the traffic analyzer window.

Fig. 4. Establishing and closing a connection

In Figure 4, we can see the data exchange between our script and the "google.com" server with the "172.217.16.14" ip address. DNS queries are not displayed here since the filter line features the "tcp.port==80" expression.

The upper three packets represent establishing a connection, while the lower three ones represent its closure. The Time column displays the time between packets, and we can see 5 seconds of downtime. Please note that the packets are colored green, unlike the ones in Figure 2. This is because in the previous case, the analyzer detected the MySQL protocol in the exchange. In the current case, no data was passed and the analyzer highlighted the packets with the default TCP color.

Data exchange

According to the protocol, the MySQL server should send a greeting after establishing a connection. In response to it, the client sends an authorization request. This mechanism is described in detail in the Connection Phase section on the dev.mysql.com website. If the greeting is not received, the IP address is invalid or the server is listening to another port. In any case, this means we have connected to something that is definitely not a MySQL server. In a normal situation, we need to receive data (read it from the socket) and parse it.

Receipt

In the CMySQLTransaction class (to be described in detail a bit later), data receipt has been implemented as follows:

//+------------------------------------------------------------------+
//| Data receipt                                                     |
//+------------------------------------------------------------------+
bool CMySQLTransaction::ReceiveData(ushort error_code=0)
  {
   char buf[];
   uint   timeout_check=GetTickCount()+m_timeout;
   do
     {
      //--- Get the amount of data that can be read from the socket
      uint len=SocketIsReadable(m_socket);
      if(len)
        {
         //--- Read data from the socket to the buffer
         int rsp_len=SocketRead(m_socket,buf,len,m_timeout);
         m_rx_counter+= rsp_len;
         //--- Send the buffer for handling
         ENUM_TRANSACTION_STATE res = Incoming(buf,rsp_len);
         //--- Get the result the following actions will depend on
         if(res==MYSQL_TRANSACTION_COMPLETE) // server response fully accepted
            return true;   // exit (successful)
         else
            if(res==MYSQL_TRANSACTION_ERROR) // error
              {
               if(m_packet.error.code)
                  SetUserError(MYSQL_ERR_SERVER_ERROR);
               else
                  SetUserError(MYSQL_ERR_INTERNAL_ERROR);
               return false;  // exit (error)
              }
         //--- In case of another result, continue waiting for data in the loop
        }
     }
   while(GetTickCount()<timeout_check && !IsStopped());
//--- If waiting for the completion of the server response receipt took longer than m_timeout,
//--- exit with the error
   SetUserError(error_code);
   return false;
  }

Here m_socket is a socket handle obtained earlier when creating it, while m_timeout is data reading timeout used as the SocketRead() function argument for accepting a fragment of data, as well as in the form of the timeout of receiving the entire data. Before entering the loop, set a timestamp. Reaching it is considered the data receipt timeout:

uint   timeout_check=GetTickCount()+m_timeout;

Next, read the SocketIsReadable() function result in a loop and wait till it returns a non-zero value. After that, read data to the buffer and pass it for handling.

      uint len=SocketIsReadable(m_socket);
      if(len)
        {
         //--- Read data from the socket to the buffer
         int rsp_len=SocketRead(m_socket,buf,len,m_timeout);
         m_rx_counter+= rsp_len;
         //--- Send the buffer for handling
         ENUM_TRANSACTION_STATE res = Incoming(buf,rsp_len);

        ...

        }

We cannot count on the ability to accept the entire packet if there is data in the socket. There are a number of situations where data can arrive in small portions. For example, it may be a poor connection via a 4G modem with a large number of retransmits. Therefore, our handler should be able to collect data into some indivisible groups it is possible to work with. Let's use MySQL packets for that.

The CMySQLTransaction::Incoming() method is used to accumulate and handle data:

   //--- Handle received data
   ENUM_TRANSACTION_STATE  Incoming(uchar &data[], uint len);

The result it returns lets us know what to do next — continue, complete or interrupt the process of receiving data:

enum ENUM_TRANSACTION_STATE
  {
   MYSQL_TRANSACTION_ERROR=-1,         // Error
   MYSQL_TRANSACTION_IN_PROGRESS=0,    // In progress
   MYSQL_TRANSACTION_COMPLETE,         // Fully completed
   MYSQL_TRANSACTION_SUBQUERY_COMPLETE // Partially completed
  };

In case of an internal error, as well as when getting a server error or completing data receipt, reading data from the socket should be stopped. In all other cases, it should be continued. MYSQL_TRANSACTION_SUBQUERY_COMPLETE value indicates that one of the server responses to a client's multiple query has been accepted. It is equivalent to MYSQL_TRANSACTION_IN_PROGRESS for the reading algorithm.

Fig. 5. MySQL packet

MySQL packet format is displayed in Fig. 5. The first three bytes define the size of a useful load in the packet, while the next byte means the serial number of the packet in the sequence and is followed by data. The serial number is set to zero at the beginning of each exchange. For example, the greeting packet is 0, client authorization request — 1, server response — 2 (end of the connection phase). Next, when sending a client query, the value of the sequence number should be set to zero again and is increased in each server response packet. If the number of packets exceeds 255, the number value passes zero.

The simplest packet (MySQL ping) looks as follows in the traffic analyzer:

Fig. 6. Ping packet in the traffic analyzer

The Ping packet contains one byte of data with the value of 14 (or 0x0E in hexadecimal form).

Let's consider the CMySQLTransaction::Incoming() method which gathers data to packets and passes them to handlers. Its abridged source code is provided below.

ENUM_TRANSACTION_STATE CMySQLTransaction::Incoming(uchar &data[], uint len)
  {
   int ptr=0; // index of the current byte in the 'data' buffer
   ENUM_TRANSACTION_STATE result=MYSQL_TRANSACTION_IN_PROGRESS; // result of handling accepted data
   while(len>0)
     {
      if(m_packet.total_length==0)
        {
         //--- If the amount of data in the packet is unknown
         while(m_rcv_len<4 && len>0)
           {
            m_hdr[m_rcv_len] = data[ptr];
            m_rcv_len++;
            ptr++;
            len--;
           }
         //--- Received the amount of data in the packet
         if(m_rcv_len==4)
           {
            //--- Reset error codes etc.
            m_packet.Reset();
            m_packet.total_length = reader.TotalLength(m_hdr);
            m_packet.number = m_hdr[3];
            //--- Length received, reset the counter of length bytes
            m_rcv_len = 0;
            //--- Highlight the buffer of a specified size
            if(ArrayResize(m_packet.data,m_packet.total_length)!=m_packet.total_length)
               return MYSQL_TRANSACTION_ERROR;  // internal error
           }
         else // if the amount of data is still not accepted
            return MYSQL_TRANSACTION_IN_PROGRESS;
        }
      //--- Collect packet data
      while(len>0 && m_rcv_len<m_packet.total_length)
        {
         m_packet.data[m_rcv_len] = data[ptr];
         m_rcv_len++;
         ptr++;
         len--;
        }
      //--- Make sure the package has been collected already
      if(m_rcv_len<m_packet.total_length)
         return MYSQL_TRANSACTION_IN_PROGRESS;

      //--- Handle received MySQL packet
      //...
      //---      

      m_rcv_len = 0;
      m_packet.total_length = 0;
     }
   return result;
  }

The first step is to collect the packet header — the first 4 bytes containing data length and serial number in the sequence. To accumulate the header, use the m_hdr buffer and m_rcv_len byte counter. When 4 bytes are collected, get their length and change the m_packet.data buffer based on it. Received packet data is copied to it. When the packet is ready, pass it to the handler.

If len length of received data is still not zero after receiving the packet, this means we have received several packets. We can handle either several whole packets, or several partial ones in a single Incoming() method call.

Packet types are provided below:

enum ENUM_PACKET_TYPE
  {
   MYSQL_PACKET_NONE=0,    // None
   MYSQL_PACKET_DATA,      // Data
   MYSQL_PACKET_EOF,       // End of file
   MYSQL_PACKET_OK,        // Ok
   MYSQL_PACKET_GREETING,  // Greeting
   MYSQL_PACKET_ERROR      // Error
  };

Each of them has its own handler, which parses their sequence and contents according to the protocol. Values received during the parsing are assigned to members of the corresponding classes. In the current connector implementation, all data received in packets is parsed. This may seem somewhat redundant since the properties of the "Table" and "Original table" fields often coincide. Besides, the values of some flags are rarely needed (see Fig. 7). However, the availability of these properties allows to flexibly build the logic of interacting with the MySQL server at the application layer of the program.

Fig. 7. Field description packet

Sending

Sending data is a bit easier here.

//+------------------------------------------------------------------+
//| Form and send ping                                               |
//+------------------------------------------------------------------+
bool CMySQLTransaction::ping(void)
  {
   if(reset_rbuf()==false)
     {
      SetUserError(MYSQL_ERR_INTERNAL_ERROR);
      return false;
     }
//--- Prepare the output buffer
   m_tx_buf.Reset();
//--- Reserve a place for the packet header
   m_tx_buf.Add(0x00,4);
//--- Place the command code
   m_tx_buf+=uchar(0x0E);
//--- Form a header
   m_tx_buf.AddHeader(0);
   uint len = m_tx_buf.Size();
//--- Send a packet
   if(SocketSend(m_socket,m_tx_buf.Buf,len)!=len)
      return false;
   m_tx_counter+= len;
   return true;
  }

The source code of the ping sending method is provided above. Copy data to the prepared buffer. In case of the ping, this is the code of the 0x0E command. Next, form the header considering the amount of data and the packet serial number. For a ping, the serial number is always equal to zero. After that, try to send the assembled packet using the SocketSend() function.

The method of sending a query (Query) is similar to sending a ping:

//+------------------------------------------------------------------+
//| Form and send a query                                            |
//+------------------------------------------------------------------+
bool CMySQLTransaction::query(string s)
  {
   if(reset_rbuf()==false)
     {
      SetUserError(MYSQL_ERR_INTERNAL_ERROR);
      return false;
     }
//--- Prepare the output buffer
   m_tx_buf.Reset();
//--- Reserve a place for the packet header
   m_tx_buf.Add(0x00,4);
//--- Place the command code
   m_tx_buf+=uchar(0x03);
//--- Add the query string
   m_tx_buf+=s;
//--- Form a header
   m_tx_buf.AddHeader(0);
   uint len = m_tx_buf.Size();
//--- Send a packet
   if(SocketSend(m_socket,m_tx_buf.Buf,len)!=len)
      return false;
   m_tx_counter+= len;
   return true;
  }

The only difference is that the useful load consists of the (0x03) command code and the query string.

Sending data is always followed by the CMySQLTransaction::ReceiveData() receipt method we have considered before. If it returns no error, transaction is considered successful.

MySQL transaction class

It is now time to consider the CMySQLTransaction class in more detail.

//+------------------------------------------------------------------+
//| MySQL transaction class                                          |
//+------------------------------------------------------------------+
class CMySQLTransaction
  {
private:
   //--- Authorization data
   string            m_host;        // MySQL server IP address
   uint              m_port;        // TCP port
   string            m_user;        // User name
   string            m_password;    // Password
   //--- Timeouts
   uint              m_timeout;        // timeout of waiting for TCP data (ms)
   uint              m_timeout_conn;   // timeout of establishing a server connection
   //--- Keep Alive
   uint              m_keep_alive_tout;      // time(ms), after which the connection is closed; the value of 0 - Keep Alive is not used
   uint              m_ping_period;          // period of sending ping (in ms) in the Keep Alive mode
   bool              m_ping_before_query;    // send 'ping' before 'query' (this is reasonable in case of large ping sending periods)
   //--- Network
   int               m_socket;      // socket handle
   ulong             m_rx_counter;  // counter of bytes received
   ulong             m_tx_counter;  // counter of bytes passed
   //--- Timestamps
   ulong             m_dT;                   // last query time
   uint              m_last_resp_timestamp;  // last response time
   uint              m_last_ping_timestamp;  // last ping time
   //--- Server response
   CMySQLPacket      m_packet;      // accepted packet
   uchar             m_hdr[4];      // packet header
   uint              m_rcv_len;     // counter of packet header bytes
   //--- Transfer buffer
   CData             m_tx_buf;
   //--- Authorization request class
   CMySQLLoginRequest m_auth;
   //--- Server response buffer and its size
   CMySQLResponse    m_rbuf[];
   uint              m_responses;
   //--- Waiting and accepting data from the socket
   bool              ReceiveData(ushort error_code);
   //--- Handle received data
   ENUM_TRANSACTION_STATE  Incoming(uchar &data[], uint len);
   //--- Packet handlers for each type
   ENUM_TRANSACTION_STATE  PacketOkHandler(CMySQLPacket *p);
   ENUM_TRANSACTION_STATE  PacketGreetingHandler(CMySQLPacket *p);
   ENUM_TRANSACTION_STATE  PacketDataHandler(CMySQLPacket *p);
   ENUM_TRANSACTION_STATE  PacketEOFHandler(CMySQLPacket *p);
   ENUM_TRANSACTION_STATE  PacketErrorHandler(CMySQLPacket *p);
   //--- Miscellaneous
   bool              ping(void);                // send ping
   bool              query(string s);           // send a query
   bool              reset_rbuf(void);          // initialize the server response buffer
   uint              tick_diff(uint prev_ts);   // get the timestamp difference
   //--- Parser class
   CMySQLPacketReader   reader;
public:
                     CMySQLTransaction();
                    ~CMySQLTransaction();
   //--- Set connection parameters
   bool              Config(string host,uint port,string user,string password,uint keep_alive_tout);
   //--- Keep Alive mode
   void              KeepAliveTimeout(uint tout);                       // set timeout
   void              PingPeriod(uint period) {m_ping_period=period;}    // set ping period in seconds
   void              PingBeforeQuery(bool st) {m_ping_before_query=st;} // enable/disable ping before a query
   //--- Handle timer events (relevant when using Keep Alive)
   void              OnTimer(void);
   //--- Get the pointer to the class for working with authorization
   CMySQLLoginRequest *Handshake(void) {return &m_auth;}
   //--- Send a request
   bool              Query(string q);
   //--- Get the number of server responses
   uint              Responses(void) {return m_responses;}
   //--- Get the pointer to the server response by index
   CMySQLResponse    *Response(uint idx);
   CMySQLResponse    *Response(void) {return Response(0);}
   //--- Get the server error structure
   MySQLServerError  GetServerError(void) {return m_packet.error;}
   //--- Options
   ulong             RequestDuration(void) {return m_dT;}                     // get the last transaction duration
   ulong             RxBytesTotal(void) {return m_rx_counter;}                // get the number of received bytes
   ulong             TxBytesTotal(void) {return m_tx_counter;}                // get the number of passed bytes
   void              ResetBytesCounters(void) {m_rx_counter=0; m_tx_counter=0;} // reset the counters of received and passed bytes
  };

Let's have a closer look at the following private members:

• m_packet of CMySQLPacket type — class of the currently handled MySQL packet (source code with comments in the MySQLPacket.mqh file)
  
• m_tx_buf of CData type — class of the transfer buffer created for the convenience of generating a query (Data.mqh file)
• m_auth of CMySQLLoginRequest type — class for working with authorization (password scrambling, storing obtained server parameters and specified client parameters, the source code is in MySQLLoginRequest.mqh)
• m_rbuf of CMySQLResponse type — server repsonse buffer; the response here is the "Ok" or "Data" type packet (MySQLResponse.mqh)
• reader of CMySQLPacketReader type — MySQL packet parser class
  

The public methods are described in detail in the documentation.

For the application layer, the transaction class looks as displayed in Figure 8.

Fig. 8. CMySQLTransaction class structure

where:

• CMySQLLoginRequest — should be configured before establishing a connection when specifying client parameters whose values are different from the predefined ones (optional);
  
• CMySQLResponse — server response if a transaction is completed without errors
• CMySQLField — field description;
  
• CMySQLRow — row (buffer of field values in text form);
  
• MySQLServerError — error description structure in case a transaction failed.
  

There are no public methods responsible for establishing and closing a connection. This is done automatically when calling the CMySQLTransaction::Query() method. When using the constant connection mode, it is established during the first call of CMySQLTransaction::Query() and closed after the defined timeout.

Parameters of connection, user account, as well as special client parameter values should be set before calling CMySQLTransaction::Query().

In general, interaction with the transaction class is performed according to the following principle:

Fig. 9. Working with the CMySQLTransaction class

Application

Let's consider the simplest example of applying the connector. To do this, write a script sending the SELECT query to the world test database.

//--- input parameters
input string   inp_server     = "127.0.0.1";          // MySQL server address
input uint     inp_port       = 3306;                 // TCP port
input string   inp_login      = "admin";              // Login
input string   inp_password   = "12345";              // Password
input string   inp_db         = "world";              // Database name

//--- Connect MySQL transaction class
#include  <MySQL\MySQLTransaction.mqh>
CMySQLTransaction mysqlt;

//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {
//--- Configure MySQL transaction class
   mysqlt.Config(inp_server,inp_port,inp_login,inp_password);
//--- Make a query
   string q = "select `Name`,`SurfaceArea` "+
              "from `"+inp_db+"`.`country` "+
              "where `Continent`='Oceania' "+
              "order by `SurfaceArea` desc limit 10";
   if(mysqlt.Query(q)==true)
     {
      if(mysqlt.Responses()!=1)
         return;
      CMySQLResponse *r = mysqlt.Response();
      if(r==NULL)
         return;
      Print("Name: ","Surface Area");
      uint rows = r.Rows();
      for(uint i=0; i<rows; i++)
        {
         double area;
         if(r.Row(i).Double("SurfaceArea",area)==false)
            break;
         PrintFormat("%s: %.2f",r.Row(i)["Name"],area);
        }
     }
   else
      if(GetLastError()==(ERR_USER_ERROR_FIRST+MYSQL_ERR_SERVER_ERROR))
        {
         // in case of a server error
         Print("MySQL Server Error: ",mysqlt.GetServerError().code," (",mysqlt.GetServerError().message,")");
        }
      else
        {
         if(GetLastError()>=ERR_USER_ERROR_FIRST)
            Print("Transaction Error: ",EnumToString(ENUM_TRANSACTION_ERROR(GetLastError()-ERR_USER_ERROR_FIRST)));
         else
            Print("Error: ",GetLastError());
        }
  }

Suppose that our task is to get a list of countries with the continent value of "Oceania" sorted by area from largest to smallest with the maximum of 10 items in the list. Let's perform the following actions:

• Declare an instance of the mysqlt transaction class
  
• Set connection parameters
• Make the appropriate query
• If the transaction is successful, make sure the number of responses is equal to the expected value
• Get the pointer to the server response class
• Get the number of rows in the response
• Display the values of rows

The transaction may fail because of one of three reasons:

• A server error - get its description using the CMySQLTransaction::GetServerError() method
• An internal error - use the EnumToString() function to get a description
• Otherwise, get the error code using GetLastError()
  

If inputs are specified correctly, the result of the script operation is as follows:

Fig. 10. Test script operation result

More complex examples applying multiple queries and the constant connection mode will be described in the second part.

Documentation

Contents

• CMySQLTransaction transaction class
  

•     Config
•     KeepAliveTimeout
•     PingPeriod
•     PingBeforeQuery
•     OnTimer
•     Handshake
•     Query
•     Responses
•     Response
•     GetServerError
•     RequestDuration
•     RxBytesTotal
•     TxBytesTotal
•     ResetBytesCounters

• CMySQLLoginRequest authorization management class
  
• CMySQLResponse server response class
• MySQLServerError server error structure
• CMySQLField field class
• CMySQLRow row class   
  

CMySQLTransaction transaction class

List of CMySQLTransaction class methods

Method	Action
Config	Setting connection parameters
KeepAliveTimeout	Setting timeout for Keep Alive mode in seconds
PingPeriod	Setting a ping period for Keep Alive mode in seconds
PingBeforeQuery	Enable/disable ping before a query
OnTimer	Handling timer events (relevant when using Keep Alive)
Handshake	Getting the pointer to the class for working with authorization
Query	Sending a query
Responses	Getting the number of server responses
Response	Getting the pointer to the server response class
GetServerError	Getting the server error structure
RequestDuration	Transaction duration in microseconds
RxBytesTotal	The counter of accepted bytes since the program launch
TxBytesTotal	The counter of bytes sent since the program launch
ResetBytesCounters	Resetting the counters of accepted and sent bytes

Below is a brief description of each method.

Config

Sets connection parameters.

bool  Config(
   string host,         // server name
   uint port,           // port
   string user,         // user name
   string password,     // password
   string base,         // database name
   uint keep_alive_tout // constant connection timeout (0 - not used)
   );

Return value: true if successful, otherwise - false (invalid symbols in string type arguments).

KeepAliveTimeout

Enables the constant connection mode and sets its timeout. Timeout value is a time in seconds from the moment of sending the last query, after which the connection is closed. If queries are repeated more often than the defined timeout value, the connection is not closed.

void  KeepAliveTimeout(
   uint tout            // set the constant connection timeout in seconds (0 - disable)
   );

PingPeriod

Sets the period of sending 'ping' packets in the constant connection mode. It prevents the server from closing the connection. The ping is sent after the specified time upon the last query or the previous ping.

void  PingPeriod(
   uint period          // set the ping period in seconds (for the constant connection mode)
   );

Return value: none.

PingBeforeQuery

Enables sending the 'ping' packet before a query. Connection may be closed or terminated for some reason in the constant connection mode in time intervals between queries. In this case, it is possible to send ping to the MySQL server to make sure the connection is active before sending a query.

void  PingBeforeQuery(
   bool st              // enable (true)/disable (false) ping before a query
   );

Return value: none.

OnTimer

Used in the constant connection mode. The method should be called from the OnTimer event handler. The timer period should not exceed the minimum value of the KeepAliveTimeout and PingPeriod periods.

void  OnTimer(void);

Return value: none.

Handshake

Gets the pointer to the class for working with authorization. It can be used to set the flags of client capabilities and the maximum packet size before establishing a connection to the server. After the authorization, it allows receiving the version and the flags of the server capabilities.

CMySQLLoginRequest *Handshake(void);

Return value: pointer to the CMySQLLoginRequest class for working with authorization.

Query

Sends a query.

bool  Query(
   string q             // query body
   );

Return value: execution result; successful - true, error - false.

Responses

Gets the number of responses.

uint  Responses(void);

Return value: number of server responses.

Packets of "Ok" or "Data" types are considered responses. If the query is executed successfully, one or more responses (for multiple queries) are accepted.

Response

Gets the pointer to the MySQL server response class

CMySQLResponse  *Response(
   uint idx                     // server response index
   );

Return value: pointer to the CMySQLResponse server response class. Passing an invalid value as an argument returns NULL.

The overloaded method without specifying an index is equivalent to Response(0).

CMySQLResponse  *Response(void);

Return value: pointer to the CMySQLResponse server response class. If there are no responses, NULL is returned.

GetServerError

Gets the structure storing the code and the server error message. It can be called after the transaction class returns the MYSQL_ERR_SERVER_ERROR error.

MySQLServerError  GetServerError(void);

Return value: MySQLServerError error structure

RequestDuration

Gets the request execution duration.

ulong  RequestDuration(void);

Return value: query duration in microseconds from the moment of sending till the end of handling

RxBytesTotal

Gets the number of accepted bytes.

ulong  RxBytesTotal(void);

Return value: number of accepted bytes (TCP level) since the program launch. The ResetBytesCounters method is used for a reset.

TxBytesTotal

Gets the number of sent bytes.

ulong  TxBytesTotal(void);

Return value: number of passed bytes (TCP level) since the program launch. The ResetBytesCounters method is used for a reset.

ResetBytesCounters

Resets the counters of accepted and sent bytes.

void  ResetBytesCounters(void);

CMySQLLoginRequest authorization management class

CMySQLLoginRequest class methods

Method	Action
SetClientCapabilities	Sets the client capabilities flags. Predefined value: 0x005FA685
SetMaxPacketSize	Sets the maximum allowable packet size in bytes. Predefined value: 16777215
SetCharset	Defines the set of used symbols. Predefined value: 8
Version	Returns the MySQL server version. For example: "5.7.21-log".
ThreadId	Returns the current connection thread ID. It corresponds to the CONNECTION_ID value.
ServerCapabilities	Gets the flags of the server capabilities
ServerLanguage	Returns the encoding and the database representation ID

CMySQLResponse server response class

A packet of "Ok" or "Data" type is considered a server response. Given that they differ significantly, the class has a separate set of methods for working with each type of packets.

General CMySQLResponse class methods:

Method	Return Value
Type	Server response type: MYSQL_RESPONSE_DATA or MYSQL_RESPONSE_OK

Methods for Data type packets:

Method	Return Value
Fields	Number of fields
Field	Pointer to the field class by index (overloaded method - getting the field index by name)
Field	Field index by name
Rows	Number of rows in a server response
Row	The pointer to a row class by index
Value	String value by row and field indices
Value	String value by row index and field name
ColumnToArray	The result of reading a column to the string type array
ColumnToArray	The result of reading a column to the int type array with type verification
ColumnToArray	The result of reading a column to the long type array with type verification
ColumnToArray	The result of reading a column to the double type array with type verification

Methods for "Ok" type packets:

Method	Return Value
AffectedRows	Number of rows affected by the last operation
LastId	LAST_INSERT_ID value
ServerStatus	Server status flags
Warnings	Number of warnings
Message	Server text message

MySQLServerError server error structure

MySQLServerError structure elements

Element	Type	Purpose
code	ushort	Error code
sqlstate	uint	State
message	string	Server text message

CMySQLField field class

CMySQLField class methods

Method	Return Value
Catalog	Name of a directory a table belongs to
Database	Name of a database a table belongs to
Table	Pseudonym of a table a field belongs to
OriginalTable	Original name of a table a field belongs to
Name	Field pseudonym
OriginalName	Original field name
Charset	Applied encoding number
Length	Value length
Type	Value type
Flags	Flags defining value attributes
Decimals	Allowed decimal places
MQLType	Field type in the form of the ENUM_DATABASE_FIELD_TYPE value (except for DATABASE_FIELD_TYPE_NULL)

CMySQLRow row class

CMySQLRow class methods

Method	Action
Value	Returns the field value by number as a string
operator[]	Returns the field value by name as a string
MQLType	Returns the field type by number as the ENUM_DATABASE_FIELD_TYPE value
MQLType	Returns the field type by name as the ENUM_DATABASE_FIELD_TYPE value
Text	Gets the field value by number as a string with type verification
Text	Gets the field value by name as a string with type verification
Integer	Gets the int type value by field number with type verification
Integer	Gets the int type value by field name with type verification
Long	Gets the long type value by field number with type verification
Long	Gets the long type value by field name with type verification
Double	Gets the double type value by field number with type verification
Double	Gets the double type value by field name with type verification
Blob	Gets the value in the form of the uchar array by field number with type verification
Blob	Gets the value in the form of the uchar array by field name with type verification

Note. Type verification means that the readable field of the method working with int type should be equal to DATABASE_FIELD_TYPE_INTEGER. In case of a mismatch, no value is received and the method returns 'false'. Converting MySQL field type IDs to ENUM_DATABASE_FIELD_TYPE type value is implemented in the CMySQLField::MQLType() method whose source code is provided below.

//+------------------------------------------------------------------+
//| Return the field type as the ENUM_DATABASE_FIELD_TYPE value      |
//+------------------------------------------------------------------+
ENUM_DATABASE_FIELD_TYPE CMySQLField::MQLType(void)
  {
   switch(m_type)
     {
      case 0x00:  // decimal
      case 0x04:  // float
      case 0x05:  // double
      case 0xf6:  // newdecimal
         return DATABASE_FIELD_TYPE_FLOAT;
      case 0x01:  // tiny
      case 0x02:  // short
      case 0x03:  // long
      case 0x08:  // longlong
      case 0x09:  // int24
      case 0x10:  // bit
      case 0x07:  // timestamp
      case 0x0c:  // datetime
         return DATABASE_FIELD_TYPE_INTEGER;
      case 0x0f:  // varchar
      case 0xfd:  // varstring
      case 0xfe:  // string
         return DATABASE_FIELD_TYPE_TEXT;
      case 0xfb:  // blob
         return DATABASE_FIELD_TYPE_BLOB;
      default:
         return DATABASE_FIELD_TYPE_INVALID;
     }
  }

Conclusion

In this article, we examined the use of functions for working with sockets using the implementation of the MySQL connector as an example. This has been a theory. The second part of the article is to be of more practical nature: we will develop a service for collecting signal properties and a program for viewing changes in them.

The attached archive contains the following files:

• Include\MySQL\ path: connector source codes
  
• Scripts\test_mysql.mq5 file: the example of using the connector considered in the Application section.