Which DB engine for MT5

[arcull]

Hi there.

to assist my automated trading via EA-s in MT5 I would need a database in which/from which I could read/write data using mql code. I've seen some posts explaining how to connect MT5 with MySql, have tried that, but it's a bit difficult, while writes to DB are straight forward, reads from the DB are more complicated. I'm not saying it's impossible, but would rather use some other "easier to implement" solution. So I wonder with which DB engine could I achieve easier integration. My requirements would be something like:

- freeware solution if possible

- working on MS windows

- is reasonably fast

- supports basic entity relation models, no science fiction required

As a last resort, I thought I'll do writes to plain ascii files, but would prefer a better solution. So any suggestions welcome, thanks in advance. 

[Anthony Garot]

arcull:

As a last resort, I thought I'll do writes to plain ascii files, but would prefer a better solution.

I dump to CSV. I have never had a need for an RDBMS.

CSV files are easy to open/manipulate in a spreadsheet; you can easily build plots using gnuplot; python has a CSV module; CSV is platform independent--in short, CSV is utterly versatile.

If/When my needs require a full DB solution, I would look to MySQL first.

[arcull]

Anthony Garot:

I dump to CSV. I have never had a need for an RDBMS.

CSV files are easy to open/manipulate in a spreadsheet; you can easily build plots using gnuplot; python has a CSV module; CSV is platform independent--in short, CSV is utterly versatile.

If/When my needs require a full DB solution, I would look to MySQL first.

Thanks Anthony, yes I could use CSV but I don't need to some kind of data export which would be used in other apps, I need need data storage for EA operational purposes, for doing the calculations needed to execute further trades according to strategy. I know I can store values in stack/heap memory for the time the EA runs, but I will loose all that if EA has to restart, MT5 crashes, computer reboots etc. So I would prefer to store the data to a DB and read them when need arises. However yes, csv is an option too.

[Enrique Dangeroux]

arcull:
Thanks Anthony, yes I could use CSV but I don't need to some kind of data export which would be used in other apps, I need need data storage for EA operational purposes, for doing the calculations needed to execute further trades according to strategy. I know I can store values in stack/heap memory for the time the EA runs, but I will loose all that if EA has to restart, MT5 crashes, computer reboots etc. So I would prefer to store the data to a DB and read them when need arises. However yes, csv is an option too.

Use struct. write, the struct when needed. When Crash/reboot etc, load the struct.

[JAMES-Thotsawat Pakine]

arcull:

Hi there.

to assist my automated trading via EA-s in MT5 I would need a database in which/from which I could read/write data using mql code. I've seen some posts explaining how to connect MT5 with MySql, have tried that, but it's a bit difficult, while writes to DB are straight forward, reads from the DB are more complicated. I'm not saying it's impossible, but would rather use some other "easier to implement" solution. So I wonder with which DB engine could I achieve easier integration. My requirements would be something like:

- freeware solution if possible

- working on MS windows

- is reasonably fast

- supports basic entity relation models, no science fiction required

As a last resort, I thought I'll do writes to plain ascii files, but would prefer a better solution. So any suggestions welcome, thanks in advance. 

[nicholish en]

If you're not relying on your db to manage a lot of concurrent queries then I'd probably go with sqlite3, otherwise MySQL or better yet PostgreSQL (if you're feeling brave and want to write your own bindings).

https://github.com/dingmaotu/mql-sqlite3

[Alain Verleyen]

MySQL or ...

[arcull]

Thanks for all input. I'll give sqlite a try.

@Enrique Dangeroux I don't see how structs could be useful here. How could a struct store data (like closed positions for instance) and reload it on app crash. I could use struct for some kind of ini settings, but that is not what I need. Anyway, if you can elaborate on this it would be great. I prefer a small sample code if possible. Thanks again.

[nicholish en]

arcull:

I don't see how structs could be useful here. How could a struct store data (like closed positions for instance) and reload it on app crash. I could use struct for some kind of ini settings, but that is not what I need. Anyway, if you can elaborate on this it would be great. I prefer a small sample code if possible. Thanks again.

Here is some sample code (runnable script) that does exactly what you need without the use of DB. 

#property strict
#include <Arrays\List.mqh>
#include <stdlib.mqh>
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
class ClosedPosition : public CObject
  {
public:
   struct P
     {
      int               ticket;
      datetime          closed_time,open_time;
     }
   details;
   ClosedPosition(){}
   ClosedPosition(int ticket,datetime open_time,datetime closed_time)
     {
      this.details.ticket=ticket;
      this.details.open_time=open_time;
      this.details.closed_time=closed_time;
     }
   virtual bool Save(const int file_handle) override
     {
      if(file_handle==INVALID_HANDLE)
         return false;
      FileWriteStruct(file_handle,details);
      return true;
     }
   virtual bool Load(const int file_handle) override
     {
      if(file_handle==INVALID_HANDLE)
         return false;
      FileReadStruct(file_handle,details);
      return true;
     }
  };
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
class PositionList : public CList
  {
public: virtual CObject *CreateElement(void) override { return new ClosedPosition(); }
  };
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
class Expert : public CObject
  {
public:
   string symbol;
   struct EA_details
     {
      ENUM_TIMEFRAMES   timeframe;
      int               magic;
     }
   details;

   struct EA_settings
     {
      int               setting1,setting2,setting3;
     }
   settings;

   PositionList      positions;

   bool deinit()
     {
      string filename=StringFormat("%s_%s_%d.bin",symbol,EnumToString(details.timeframe),details.magic);
      int f=FileOpen(filename,FILE_WRITE|FILE_BIN);
      if(f==INVALID_HANDLE)
         return false;
      if(!positions.Save(f))
         return false;
      int len = StringLen(symbol);
      FileWriteInteger(f,len);
      FileWriteString(f,symbol,len);
      FileWriteStruct(f,details);
      FileWriteStruct(f,settings);
   
      
      FileClose(f);
      return true;
     }

   bool load(string _symbol,ENUM_TIMEFRAMES _timeframe,int _magic)
     {
      string filename=StringFormat("%s_%s_%d.bin",_symbol,EnumToString(_timeframe),_magic);
      int f=FileOpen(filename,FILE_READ|FILE_BIN);
      if(f==INVALID_HANDLE)
      {
         Print(ErrorDescription(_LastError));
         return false;
      }
      if(!positions.Load(f))
         return false;
      int len = FileReadInteger(f);
      symbol = FileReadString(f,len);
      FileReadStruct(f,this.details);
      FileReadStruct(f,this.settings);
      FileClose(f);
      return true;
     }
  };
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+

#define MAGIC 12341234
void OnStart()
  {
   Expert *e = new Expert;
   e.symbol = _Symbol;
   e.details.timeframe = (ENUM_TIMEFRAMES)_Period;
   e.details.magic = MAGIC;
   e.settings.setting1 = 1;
   e.settings.setting2 = 2;
   e.settings.setting3 = 3;
   
   for(int i=0;i<3;i++)
   {
      int ticket = 123456;
      datetime open = D'2018.05.01';
      datetime closed = D'2018.05.015';
      ClosedPosition *p = new ClosedPosition(ticket+i, open+i, closed+i);
      e.positions.Add(p);
   }
   
   // Simulating a call to deinit. There is no object in memory
   e.deinit();
   delete e;
   
   e = new Expert;
   if(e.load(_Symbol,(ENUM_TIMEFRAMES)_Period,MAGIC))
   {
      printf("%s, %s, %d", e.symbol, EnumToString(e.details.timeframe), e.details.magic);
      printf("Settings: %d, %d, %d", e.settings.setting1, e.settings.setting2, e.settings.setting3);
      for(ClosedPosition *p=e.positions.GetFirstNode(); CheckPointer(p); p = p.Next())
         printf("%d, %s = %s", p.details.ticket, 
            TimeToString(p.details.open_time), 
            TimeToString(p.details.closed_time)
         );
   }
   delete e;
   
   
   
  }

[arcull]

Emma Schwatson:

Here is some sample code (runnable script) that does exactly what you need without the use of DB. 

Thanks for code snippet. This approach looks useful, will give it a try.