MQL5 : Connecting to MySQL

March 31, 2010
By Bogdan Baltatu, MQLmagazine editor

[Versiunea romaneasca] [MQLmagazine.com in romana] [English edition]

This article is dedicated to sending data from MQL5 to a MySQL database. The article is at the same time an example for the functions presented in the article DLL Hell, MQL5 Edition : UNICODE vs ANSI.
The main advantages of collecting data in MySQL databases would be : data interrogration, lower space needed for important quantities of data. For instance the trades stored in a database occupy probably less than an equivalent HTML file. However, the main disadvantage is that such export is being done to analyse data on another platform or on manually written platforms, that need development time and are error prone – either to conception errors or to programming errors.

First you need to have installed the MySQL server and a connector. In order for the code to work you have to allow dll import from the platform and put the libmysql.dll library in the ‘libraries’ folder. We had a problem with a 64 bit edition of a libmysql.dll . Dependancies issues, probably even import (normally a 32-bit application can’t import 64-bit dlls). To get rid of the problems brought by a 64-bit dll we recommend to use one for 32-bit based systems.

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 "Bogdan Baltatu"
#property link      "http://mqlmagazine.com"
#property version   "1.00"
 
#include <stringlib.mqh>
 
#import "libmysql.dll"
   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   ="localhost"; //MySql Host:
string MySqlUser   ="user";     //MySQL User:
string MySqlPass   ="passpass";      //MySQL Password:
string MySqlDB     ="forex";     //MySQL Table:
int    MySqlPort   =3306;        //MySQL Port:
string MySqlSocket ="";          //MySQL Socket:
input int    MySqlFlag   =0;           //MySQL Flag:
int mysql   =0;
string query="";
MqlTick tick;
 
 
 
//+------------------------------------------------------------------+
//| Expert initialization function                                   |
//+------------------------------------------------------------------+
 
int OnInit()
  {
   string host,user,pass,DB;
   if(MQL5InfoInteger(MQL5_DLLS_ALLOWED)==0)
      {
        Alert("DLL calling not allowed. Allow and try again!");
      }
   mysql=mysql_init(0);
   Print("obiectul mysql=",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("connection result=",res);
   if (res==mysql)
      Print("Successfully connected to the MySQL server!");
   else
     { 
      string s;
      s=mysql_error(mysql);     
      Print("Eroare de conectare = ",ANSI2UNICODE(s));
      return(0);
     } 
   //Create table
   StringConcatenate(query,"CREATE TABLE IF NOT EXISTS ",_Symbol," (time VARCHAR(30) ,BID double,ASK double)");
   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,"INSERT INTO ",Symbol()," (time,ask,bid) VALUES ('",tick.time,"',",tick.ask,",",tick.bid,")");
   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("Query: ",_query);
       Print("Returned error: ",ANSI2UNICODE(mysql_error(mysql)) );       
      }      
    return;
  }

Between lines 9 and 17 we declare the functions that we import from libmysql.dll . Being an example we imported just 6 functions. For a deeper study of these functions use the mysql documentation.

This entry was posted on March 31, 2010 at 7:33 pm and is filed under MQL programming. You can follow any responses to this entry through the RSS 2.0 feed.

6 Responses to “ MQL5 : Connecting to MySQL ”

  1. Aaron Peterson on November 3, 2010 at 6:12 pm

    I am trying to follow this, and implement a MYSQL connection in my EA. However I am currently failing on “mysql=mysql_init(0);”

    I am using the 32-bit version 6.0.2.

    Any Ideas?

  2. Bogdan Caramalac, MQLmagazine sr.editor on November 3, 2010 at 6:54 pm

    If your MetaTrader is 64 bit, you can’t do it. There’s no way to call 32 bit dlls from 64 bit apps, same way you can’t call a 16 bit dll from a 32 bit app.

  3. Oliver Koenig on March 11, 2011 at 2:39 pm

    Hi Bogdan,

    do you have some code sample for querying data from mysql?

    Thanks & best regards,
    Oliver

  4. reptile on March 29, 2011 at 10:20 pm

    Hi

    What about reading from the mysql in mt5/mql5? In mt4 write (insert) works fine, but there are problems with the reading of the library (selecet) in addition the question is complicated by the encoded string query = “Select concat (‘;’, concat_ws (‘;’, id unix_timestamp (gmt ) .. etc + table

    ???????? ????! ???? :)

  5. CB154 on January 27, 2012 at 1:46 pm

    Hi,

    Am new to this but have this sample code working well on my MT5. Is it possible to alter this to store Open High Low close data of a 1 hour or 2 hour time period ?

  6. Bogdan Caramalac, MQLmagazine sr.editor on January 27, 2012 at 4:37 pm

    Well, why not, you have to run it on a H1 chart, and alter the code so that is run each time there is a new bar. This is done easily with a datetime variable, let’s call it LastTime, which will be first assigned with 0 bar datetime inside init. Then, inside OnTick, put an if line to check if the datetime of the current bar is diffrent than the LastTime. When they are different, you have a new bar, so inside the if block run whatever you have to run there and also assign LastTime to the current bar datetime.

Leave a Reply