MQL5 : Conectarea la MySQL

[English version] [MQLmagazine.com in english] [Editia romaneasca]

Acest articol este dedicat trimiterii de date din MQL5 catre o baza de date MySQL. Articolul este totodata o exemplificare a functiilor de conversie UNICODE/ANSI prezentate in articolul Iadul DLL, editia MQL5 : UNICODE vs ANSI.
Principalele avantaje ale colectarii datelor in baze de date MySQL ar fi: interogarea datelor, detinerea unor cantitati mari de date intr-un spatiu mai mic. De exemplu tranzactiile stocate intr-o baza de date ocupa probabil sub 10% decat intr-un fisier HTML.
Totusi, dezavantajul principal e ca un asemenea export se face pentru a analiza datele pe o alta platforma sau chiar pe platforme scrise manual, care necesita timp mare de dezvoltare si sunt expuse erorilor ascunse – fie in conceptie, fie in programare.

In primul rand trebuie sa aveti instalat serverul MySQL si un conector. Pentru functionarea codului de mai jos trebuie permis din platforma importul http://www.mqlmagazine.com/files/ro/programarea-in-mql/de dll-uri si sa puneti biblioteca libmysql.dll in folderul ‘libraries’. Am avut o problema cu un libmysql.dll pe 64 de biti. Probleme de dependente, probabil si de import (in mod normal o aplicatie pe 32 de biti nu poate importa un dll pe 64 de biti). Ca sa scapati de problemele aduse de biblioteca pe 64 biti va sfatuim sa folositi o biblioteca pentru sisteme pe baza de 32 biti.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
//+------------------------------------------------------------------+
//|                                                 Mysql-Sample.mq5 |
//|                                          Copyright Bogdan Baltatu|
//|                                           http://mqlmagazine.com |
//+------------------------------------------------------------------+
#property copyright http://www.mqlmagazine.com/files/ro/programarea-in-mql/"Bogdan Baltatuhttp://www.mqlmagazine.com/files/ro/programarea-in-mql/"
#property link      http://www.mqlmagazine.com/files/ro/programarea-in-mql/"http://mqlmagazine.comhttp://www.mqlmagazine.com/files/ro/programarea-in-mql/"
#property version   http://www.mqlmagazine.com/files/ro/programarea-in-mql/"1.00http://www.mqlmagazine.com/files/ro/programarea-in-mql/"
 
#include <stringlib.mqh>
 
#import http://www.mqlmagazine.com/files/ro/programarea-in-mql/"libmysql.dllhttp://www.mqlmagazine.com/files/ro/programarea-in-mql/"
   int mysql_init(int db);
   int mysql_errno(int TMYSQL);
   int mysql_real_connect(int TMYSQL, string& host, string& user, string& password,
                           string& DB,int port,int socket,int clientflag);
   int mysql_real_query(int TMYSQL,string& query,int lenght);
   void mysql_close(int TMSQL);                        
   string mysql_error(int TMYSQL); //string is ansi
#import
 
string MySqlHost   =http://www.mqlmagazine.com/files/ro/programarea-in-mql/"localhosthttp://www.mqlmagazine.com/files/ro/programarea-in-mql/"; //MySql Host:
string MySqlUser   =http://www.mqlmagazine.com/files/ro/programarea-in-mql/"userhttp://www.mqlmagazine.com/files/ro/programarea-in-mql/";     //MySQL User:
string MySqlPass   =http://www.mqlmagazine.com/files/ro/programarea-in-mql/"passpasshttp://www.mqlmagazine.com/files/ro/programarea-in-mql/";      //MySQL Password:
string MySqlDB     =http://www.mqlmagazine.com/files/ro/programarea-in-mql/"forexhttp://www.mqlmagazine.com/files/ro/programarea-in-mql/";     //MySQL Table:
int    MySqlPort   =3306;        //MySQL Port:
string MySqlSocket =http://www.mqlmagazine.com/files/ro/programarea-in-mql/"http://www.mqlmagazine.com/files/ro/programarea-in-mql/";          //MySQL Socket:
input int    MySqlFlag   =0;           //MySQL Flag:
int mysql   =0;
string query=http://www.mqlmagazine.com/files/ro/programarea-in-mql/"http://www.mqlmagazine.com/files/ro/programarea-in-mql/";
MqlTick tick;
 
 
 
//+------------------------------------------------------------------+
//| Expert initialization function                                   |
//+------------------------------------------------------------------+
 
int OnInit()
  {
   string host,user,pass,DB;
   if(MQL5InfoInteger(MQL5_DLLS_ALLOWED)==0)
      {
        Alert(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"Apelarea DLL este interzisa. Permiteti si reincercati!http://www.mqlmagazine.com/files/ro/programarea-in-mql/");
      }
   mysql=mysql_init(0);
   Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"obiectul mysql=http://www.mqlmagazine.com/files/ro/programarea-in-mql/",mysql);
   host=UNICODE2ANSI(MySqlHost);
   user=UNICODE2ANSI(MySqlUser);
   pass=UNICODE2ANSI(MySqlPass);
   DB=UNICODE2ANSI(MySqlDB);
   int res=mysql_real_connect(mysql,host,user,pass,DB,MySqlPort,MySqlSocket,MySqlFlag);
   Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"rezultat conectare=http://www.mqlmagazine.com/files/ro/programarea-in-mql/",res);
   if (res==mysql)
      Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"Conectarea la serverul MySQL reusita!http://www.mqlmagazine.com/files/ro/programarea-in-mql/");
   else
     { 
      string s;
      s=mysql_error(mysql);     
      Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"Eroare de conectare = http://www.mqlmagazine.com/files/ro/programarea-in-mql/",ANSI2UNICODE(s));
      return(0);
     } 
   //Create table
   StringConcatenate(query,http://www.mqlmagazine.com/files/ro/programarea-in-mql/"CREATE TABLE IF NOT EXISTS http://www.mqlmagazine.com/files/ro/programarea-in-mql/",_Symbol,http://www.mqlmagazine.com/files/ro/programarea-in-mql/" (time VARCHAR(30) ,BID double,ASK double)http://www.mqlmagazine.com/files/ro/programarea-in-mql/");
   sql_query(query);
   return(0);
  }
//+------------------------------------------------------------------+
//| Expert deinitialization function                                 |
//+------------------------------------------------------------------+
void OnDeinit(const int reason)
  {
   mysql_close(mysql); //Close connection
   return;
  }
//+------------------------------------------------------------------+
//| Expert tick function                                             |
//+------------------------------------------------------------------+
void OnTick()
  {
   SymbolInfoTick(_Symbol,tick);
   StringConcatenate(query,http://www.mqlmagazine.com/files/ro/programarea-in-mql/"INSERT INTO http://www.mqlmagazine.com/files/ro/programarea-in-mql/",Symbol(),http://www.mqlmagazine.com/files/ro/programarea-in-mql/" (time,ask,bid) VALUES ('http://www.mqlmagazine.com/files/ro/programarea-in-mql/",tick.time,http://www.mqlmagazine.com/files/ro/programarea-in-mql/"',http://www.mqlmagazine.com/files/ro/programarea-in-mql/",tick.ask,http://www.mqlmagazine.com/files/ro/programarea-in-mql/",http://www.mqlmagazine.com/files/ro/programarea-in-mql/",tick.bid,http://www.mqlmagazine.com/files/ro/programarea-in-mql/")http://www.mqlmagazine.com/files/ro/programarea-in-mql/");
   sql_query(query);
   return;
  }
//+------------------------------------------------------------------+
void sql_query(string _query)
  {
    string ansiquery;
    int length=StringLen(_query);
    ansiquery=UNICODE2ANSI(_query);
    mysql_real_query(mysql,ansiquery,length);
    int mysqlerr=mysql_errno(mysql);
    if (mysqlerr>0)
      {
       Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"La interogarea: http://www.mqlmagazine.com/files/ro/programarea-in-mql/",_query);
       Print(http://www.mqlmagazine.com/files/ro/programarea-in-mql/"Eroare: http://www.mqlmagazine.com/files/ro/programarea-in-mql/",ANSI2UNICODE(mysql_error(mysql)) );       
      }      
    return;
  }

Intre liniile 9 si 17 declaram functiile pe care le importam din libmysql.dll . Fiind un exemplu am importat doar 6 functii. Pentru aprofundarea functiilor folositi documentatia mysql.

Editii