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

30 April 2020, 15:09
Serhii Shevchuk
4
16 673

Contents

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).


The program for viewing changes in signal properties within a certain time

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.

Traffic analyzer

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.

Packet capture filter

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.

Establishing and closing a connection

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.

MySQL packet

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:

Ping packet 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.


Packets in Wireshark analyzer

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.

Classes

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.

Important: In the constant connection mode, the OnTimer event handler should receive the call of the CMySQLTransaction::OnTimer() method. In this case, the timer period should be less than the ping and timeout periods.

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:

Working with the transaction class

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:

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

Test script operation result

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

    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.

    Translated from Russian by MetaQuotes Software Corp.
    Original article: https://www.mql5.com/ru/articles/7117

    Attached files |
    MQL5.zip (23.17 KB)
    Last comments | Go to discussion (4)
    JansO
    JansO | 4 May 2020 at 01:48

    Hello! Thank you very much for an article, hope parts II,III, e.t.c will follow

    After successful compilation on MT5 and running the script I am continuously getting the ERROR 4014 which is, I have tried to allo DLLs ,but seems those are not necessary for this project. What should I try to do, to make it running.

    Would appreciiate your reply.

    ERR_FUNCTION_NOT_ALLOWED

    4014

    Системная функция не разрешена для вызова

    Kind regards,

    Andrej

    JansO
    JansO | 4 May 2020 at 02:25
    After registering on the website, I tried to run the script with https://dev.mysql.com/doc/index-other.html instead of the IP address, in this case I get.

    ERR_NETSOCKET_CANNOT_CONNECT

    5272

    Ошибка соединения с удаленным хостом

    Seems it tries to connect, this I can say due to a bit of delay before I get the error.




    Phillip Kruger
    Phillip Kruger | 4 May 2020 at 17:56
    JansO:

    Hello! Thank you very much for an article, hope parts II,III, e.t.c will follow

    After successful compilation on MT5 and running the script I am continuously getting the ERROR 4014 which is, I have tried to allo DLLs ,but seems those are not necessary for this project. What should I try to do, to make it running.

    Would appreciiate your reply.

    ERR_FUNCTION_NOT_ALLOWED

    4014

    Системная функция не разрешена для вызова

    Kind regards,

    Andrej

    You should probably insert the desired IP on the "Allowed servers list".
    Phillip Kruger
    Phillip Kruger | 4 May 2020 at 17:57
    Very Good Article, Thank you!
    How to create 3D graphics using DirectX in MetaTrader 5 How to create 3D graphics using DirectX in MetaTrader 5

    3D graphics provide excellent means for analyzing huge amounts of data as they enable the visualization of hidden patterns. These tasks can be solved directly in MQL5, while DireсtX functions allow creating three-dimensional object. Thus, it is even possible to create programs of any complexity, even 3D games for MetaTrader 5. Start learning 3D graphics by drawing simple three-dimensional shapes.

    Library for easy and quick development of MetaTrader programs (part XXXIII): Pending trading requests - closing positions under certain conditions Library for easy and quick development of MetaTrader programs (part XXXIII): Pending trading requests - closing positions under certain conditions

    We continue the development of the library functionality featuring trading using pending requests. We have already implemented sending conditional trading requests for opening positions and placing pending orders. In the current article, we will implement conditional position closure – full, partial and closing by an opposite position.

    Library for easy and quick development of MetaTrader programs (part XXXIV): Pending trading requests - removing and modifying orders and positions under certain conditions Library for easy and quick development of MetaTrader programs (part XXXIV): Pending trading requests - removing and modifying orders and positions under certain conditions

    In this article, we will complete the description of the pending request trading concept and create the functionality for removing pending orders, as well as modifying orders and positions under certain conditions. Thus, we are going to have the entire functionality enabling us to develop simple custom strategies, or rather EA behavior logic activated upon user-defined conditions.

    Applying network functions, or MySQL without DLL: Part II - Program for monitoring changes in signal properties Applying network functions, or MySQL without DLL: Part II - Program for monitoring changes in signal properties

    In the previous part, we considered the implementation of the MySQL connector. In this article, we will consider its application by implementing the service for collecting signal properties and the program for viewing their changes over time. The implemented example has practical sense if users need to observe changes in properties that are not displayed on the signal's web page.