Applying network functions, or MySQL without DLL: Part I - Connector
21 861
5
Contents
- Introduction
- Sockets
- Wireshark traffic analyzer
- Data exchange
- 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:
- 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).
- Packets. Here we can see the connection setup process, server greeting, authorization request and subsequent exchange.
- Selected packet content in hexadecimal form. In this case, we can see the contents of the MySQL server greeting packet.
- Transport level (TCP). We are located here when using functions for working with sockets.
- 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:
- The ERR_NETSOCKET_TOO_MANY_OPENED error signals that more than 128 sockets are open.
- 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.
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:
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
- 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 |
| 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 Ltd.
Original article: https://www.mql5.com/ru/articles/7117
Library for easy and quick development of MetaTrader programs (part XXXIV): Pending trading requests - removing and modifying orders and positions under certain conditions
How to create 3D graphics using DirectX in MetaTrader 5
Applying network functions, or MySQL without DLL: Part II - Program for monitoring changes in signal properties
Library for easy and quick development of MetaTrader programs (part XXXIII): Pending trading requests - closing positions under certain conditions
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
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
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.
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