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

[Alexandr Gavrilin]

DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);

won't it create a big load when calling for each tick?????

Wouldn't it be more logical to connect to the database in init and then use the identifier for access ?

[Eugeniy Lugovoy]

DKeN:

won't it create a big load when calling for each tick?????

Wouldn't it be more logical to connect to the database in init and then use the identifier for access ?

Greetings. For Expert Advisors and Indicators the connection to the database is made in the initialisation function. There are no recommendations in the article on how to establish a connection with the database on each tick. Only examples of scripts are given, and scripts, you understand, do not have initialisation.

[Denis Kirichenko]

Eugene, thank you for the article. Interesting material on DBMS.

I have such a problem.

I have MetaTrader5, x64, 975 build.

When running the script "MySQL-001", I get an error:

RL      1       12:41:22.443    MySQL-001       'C:\Program Files\MetaTrader5\MQL5\libraries\MQLMySQL.dll' is not 64-bit version
PG      1       12:41:22.474    MySQL-001 (AUDCAD.e,M1) Cannot load 'C:\Program Files\MetaTrader5\MQL5\libraries\MQLMySQL.dll'
DK      2       12:41:23.677    MySQL-001 (AUDCAD.e,M1) Cannot call 'cMySqlVersion', '..\libraries\MQLMySQL.dll' is not loaded
ID      2       12:41:23.677    MySQL-001 (AUDCAD.e,M1) unresolved import function call

The reason is, as I understand, the system is trying to load a 64-bit library...

[Eugeniy Lugovoy]

denkir:

Eugene, thank you for the article. Interesting material on DBMS.

I have such a problem.

I have MetaTrader5, x64, 975 build.

When running the script "MySQL-001", I get an error:

The reason is, as I understand, the system is trying to load a 64-bit library...

Yes, the project is compiled for x32 as I work with 32-bit terminals mostly. I understand the problem, I will compile and test it under MT5 x64 in the next few days.

Thanks for the comment.

[Eugeniy Lugovoy]

Library version for Metatrader 5 - x64

- Project MQLMySQL.DLL is recompiled for x64 (sources were not corrected).

- Standard LibMySQL.DLL is taken from the latest stable MySQL distribution v5.6.21 x64

Actually, for those who tried to run the project under MT5 x64, you can replace only the contents of the MQL5\Libraries directory. The source code of MQL programs does not need to be corrected or recompiled.

Thanks again to Denis (denkir) for his remark, I forgot about x64 users.

[Andriy Voitenko]

I read it and wept. I have nothing against the author. I hope he treats criticism adequately. Anyway:

1. Why write a wrapper in the form of another DLL, if everything works fine without it? The MQL language is not at that stage of development to write such crutches that often turn into rakes.

2. Why as a result of all this work is not written a class that has everything necessary, and which eliminates the shortcomings of predecessors, which the author refers to?

3. I would like to especially note this:

string SQL;
SQL = "INSERT INTO EURUSD(Ask,Bid) VALUES (1.3601,1.3632);";
SQL = SQL + "INSERT INTO EURUSD(Ask,Bid) VALUES (1.3621,1.3643);";
SQL = SQL + "INSERT INTO EURUSD(Ask,Bid) VALUES (1.3605,1.3629);";

Actually, everything is much simpler:

string SQL="INSERT INTO EURUSD(Ask,Bid) VALUES (1.3601,1.3632),(1.3621,1.3643),(1.3605,1.3629);";

In general, the article is written in the style of "Look what I can do!" instead of "Look and learn how it should be done".

[Eugeniy Lugovoy]

avoitenko:

I read it and wept. I have nothing against the author. I hope he treats criticism adequately. Anyway:

1. Why write a wrapper in the form of another DLL, if everything works fine without it? The MQL language is not at that stage of development to write such crutches that often turn into rakes.

2. Why as a result of all this work is not written a class that has everything necessary, and which eliminates the shortcomings of predecessors, which the author refers to?

3. I would like to especially note this:

Actually, everything is much simpler:

In general, the article is written in the style of "Look what I can do!" instead of "Look and learn how it should be done".

I'll answer in order.

I must say that the project was started quite a long time ago, when the MQL language was not at the current stage of development and MQL4 had no classes, no structures.

And the software solution had to work for both MQL4 and MQL5 with minimal costs for the transition from MQL4 to MQL5 (if necessary). The project was being finalised until recently.

That is why the project was not implemented as a class. Do you want a class? No problem - write it! Nobody forbids you.

Now concerning 3 INSERTs instead of one. I wrote it this way (and it is copyright) for the following reasons:

- it does not violate the generally accepted standard SQL'92, SQL'2000 (your variant - peculiarities of syntax exactly MySQL bulk insert);

- it will be easier for a novice programmer in this area to read and understand;

- demonstration of execution of exactly 3 INSERT instructions, not one.

Multi statements can include any DML/DDL/DCL operations, your example is limited to INSERT operation on one table.

P.S. The article was written in a "Take and use" style.

[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 

[Eugeniy Lugovoy]

For x64 platforms, use the following libraries (with discussion)