Discussion of article "How to Access the MySQL Database from MQL5 (MQL4)"

[MetaQuotes]

New article How to Access the MySQL Database from MQL5 (MQL4) has been published:

The article describes the development of an interface between MQL and the MySQL database. It discusses existing practical solutions and offers a more convenient way to implement a library for working with databases. The article contains a detailed description of the functions, the interface structure, examples and some of specific features of working with MySQL. As for the software solutions, the article attachments include the files of dynamic libraries, documentation and script examples for the MQL4 and MQL5 languages.

The problem of interaction of MQL with databases is not new, however it's still relevant. Use of databases can greatly enhance the possibilities of MetaTrader: storage and analysis of the price history, copying trades from one trading platform to another, providing quotes/trades in real time, heavy analytical computations on the server side and/or using a schedule, monitoring and remote control of accounts using web technologies.

Anyway, there were many attempts to benefit from the combination of MQL and MySQL, some solutions are available in the CodeBase.

For example "MySQL wrapper - library for MetaTrader 4" is the project, from which many programmers start their own developments with further additions. In my opinion, one of the disadvantages of this solution is allocation of special arrays for reading data from the database.

Another project "MySQL logger 1 - EA for MetaTrader 4" is highly specialized, it uses no wrapper to access the standard library libmysql.dll. Therefore it doesn't work in MetaTrader4 Build 600+, since the char character types have been replaced by wchar_t, and the use of the int type instead of the TMYSQL structure pointer causes memory leaks in the project (the allocated memory cannot be controlled/freed).

Another interesting project is "EAX_Mysql - MySQL library - library for MetaTrader 5". It's quite a good implementation. The list of disadvantages stated by the author imposes some restrictions on its use.

Anyone who ever needs to uses databases in their MQL projects has two options: either to develop their own solution and know every single part of it, or use/adapt any third-party solution, learn how to use it and detect all its defects that may hinder their project.

I faced such a necessity and the two options while developing a rather complex trading robot. Having searched through existing projects and studied a very large number of solutions, I realized that non of the found implementations could help bring my trading robot to the "professional level".

Moreover, there were also absurd solutions, for example: DML/DDL operations (insert/update/delete data, create/drop objects in database) were performed using the standard libmysql.dll, and data selection (SELECT) was actually implemented as a HTTP request (using inet.dll) to a PHP script located on the web server on the MySQL server side. The SQL queries were written in the PHP script.

In other words, to run the project, one needed to keep the following components available, configured and running: MySQL server, Apache/IIS web server, PHP/ASP scripts on the server side... A combination of quite a large number of technologies. Of course, in some circumstances this may be acceptable, but when the only task is to select data from the database - this is nonsense. In addition, supporting such a cumbersome solution is time-consuming.

Most of the solutions had no problems inserting data, creating objects and the like. The problem was data selection, as the data should be returned to the calling environment.

I thought using arrays for this purpose was impractical and inconvenient, simply because in the course of development/debugging/support of the main project, select queries to the database can be changed, while you should also control correct memory allocation for the arrays... Well, this can and must be avoided.

The hereinafter discussed MQL <-> MySql interfaced is based on a typical approach used in Oracle PL/SQL, MS SQL T-SQL, AdoDB - use of cursors. This interface was developed targeting the ease of programming and maintenance, plus a minimum of components. It is implemented as a DLL wrapper to the standard library libmysql.dll and a set of interface functions as an .mqh file.

1. MQL <-> MySQL Interface

The interaction between the MetaTrader terminal (through MQL programs) can be implemented with the help of the below components:

Author: Eugeniy Lugovoy

[Sergey Lukin]

Thanks! I will examine your solution in depth, it may be more stable than relying on MQL only.

Just FYI there is a MQL4-only bridge I wrote a while ago for MT4 build 600+: https://www.mql5.com/en/code/11114

[Eugeniy Lugovoy]

lukins:

Thanks! I will examine your solution in depth, it may be more stable than relying on MQL only.

Just FYI there is a MQL4-only bridge I wrote a while ago for MT4 build 600+: https://www.mql5.com/en/code/11114

Thanks for your time reading this artice. I've also checked your (https://www.mql5.com/en/code/11114), I've found such solution a long time ago (before MT4 build 600) and I liked the idea to make MQL and MySQL friendly. But just one thing I don't want to use is arrays to retreive the data from database. That's why I've built this solution for using with MT4 and MT5.

Also, I forgot to add x64-based project to the article, so you can download it right here, in discussion. Nothing was changed in sources, just DLL has been recompiled for x64.

Good luck,

Eugene 

[sokramm]

Thanks for this new approach! I have tried different solutions but this one looks the best so far. I made a minor addition to your library where I return the number of fields for a SELECT query, using mysql_num_fields, so that I know how many fields I need to process and return in the MQL side. Thanks so much again for putting this together, really helped!!

MQL Code:

int fields = MySqlNumFields(cursor);
for (int j=0; j<fields;j++)
{
    data[j] = MySqlGetFieldAsString(cursor, j);
}

[Eugeniy Lugovoy]

sokramm:

Thanks for this new approach! I have tried different solutions but this one looks the best so far. I made a minor addition to your library where I return the number of fields for a SELECT query, using mysql_num_fields, so that I know how many fields I need to process and return in the MQL side. Thanks so much again for putting this together, really helped!!

Thanks for the kind words. It's basic solution and it keeps general functionality I'm using in real.

I've used next rules during development of this solution: robustness, easy to study, simple using and minimal support.

Sure, you may change this project and add any addition functions regarding your project needs.

Good luck,

Eugene 

[Eugeniy Lugovoy]

Hello friends,

Well, if you like this solution and trying it for real projects (not just for fun) I have an update.

I'm starting to write new article about working with different databases, such as MS SQL Server, MS Access, Oracle, IBM DB/2, PostgreSQL.

The decision would be robust and easy to use like this one, but I have no time to complete it.

So, if you want to support this project can you send a few $$ over paypal for me: [e-mail]

The amount of sum doesn't matter, I just would like to know is such decision really needs to be completed. 

Thank you all,

Eugene 

[illman]

hello，Eugeniy Lugovoy~

I want to know why ‘ MySqlGetFieldAsDouble(Cursor, 3);’ can‘t write its value to data[i]？

  Print("data[i]",data[3]);——always displa 0.0.    what's wrong ？ thank you sir！

     for (i=0; i<Rows; i++)
         if (MySqlCursorFetchRow(Cursor))
        
         {

           double data[] ;

          ArrayResize(data,Rows); 

          data[i]= MySqlGetFieldAsDouble(Cursor, 3);

          Print("data[i]",data[3]);
        }

[illman]

elugovoy:

Hello friends,

Well, if you like this solution and trying it for real projects (not just for fun) I have an update.

I'm starting to write new article about working with different databases, such as MS SQL Server, MS Access, Oracle, IBM DB/2, PostgreSQL.

The decision would be robust and easy to use like this one, but I have no time to complete it.

So, if you want to support this project can you send a few $$ over paypal for me: [e-mail]

The amount of sum doesn't matter, I just would like to know is such decision really needs to be completed. 

Thank you all,

Eugene 

in sometimes,array is necessary——for example ，we need to match the data to a specify date（of the tick）——it may be relate to algorithm that is convenient to make it in array than MYSQL。can you give me a help sir？thanks a lot~！

[Eugeniy Lugovoy]

illman:

hello，Eugeniy Lugovoy~

I want to know why ‘ MySqlGetFieldAsDouble(Cursor, 3);’ can‘t write its value to data[i]？

Print("data[i]",data[3]);——always displa 0.0.    what's wrong ？ thank you sir！

for (i=0; i<Rows; i++)

        if (MySqlCursorFetchRow(Cursor))
        
         {

           double data[] ;

          ArrayResize(data,Rows); 

          data[i]= MySqlGetFieldAsDouble(Cursor, 3);

          Print("data[i]",data[3]);
        }

You are trying to define array every time in cycle and print empty array item.

Try this:

// I guess the Rows gets number of rows already before this workaround.
double data[];
ArrayResize(data, Rows);  

for (i=0; i<Rows; i++)
    {
     if (MySqlCursorFetchRow(Cursor))
        {
          data[i]= MySqlGetFieldAsDouble(Cursor, 3);
        }
    }

// here you'll get filled array "data"
Print("data[",3,"]",data[3]);

So, you have to define array once before fetching cycle, then resize, and then write routine for getting data.

By the way, the number 3 in  MySqlGetFieldAsDouble(Cursor, 3); means 4th column in SELECT list, because the numeration starts with 0, i.e. SELECT Open, High, Low, Close FROM ... means 0 - Open, 1 - High, 2 - Low and 3 - Close .

Hope this wil help. 

Regards,

Eugene 

[illman]

elugovoy:

You are trying to define array every time in cycle and print empty array item.

Try this:

So, you have to define array once before fetching cycle, then resize, and then write routine for getting data.

By the way, the number 3 in  MySqlGetFieldAsDouble(Cursor, 3); means 4th column in SELECT list, because the numeration starts with 0, i.e. SELECT Open, High, Low, Close FROM ... means 0 - Open, 1 - High, 2 - Low and 3 - Close .

Hope this wil help. 

Regards,

Eugene 

Thanks for helping sir ，I have solved my problems about the array。You did a good job on mysql-mql indeed~！

[[Deleted]]

Hello elugovoy

 Awesome article and great libraries.

 I'm trying to use your examples under MT5 but I'm getting this error: 

2014.12.15 15:44:16.387 MySQL-001 'C:\Users\....\AppData\Roaming\MetaQuotes\Terminal\D0E8209F77C8CF37AD8BF550E51FF075\MQL5\libraries\MQLMySQL.dll' is not 64-bit version

Are you  running MT5 under 32-bit env?

 Could you pleas help me with it? 

 Thanks very much!

 Carmine Marrone.