Discussion of article "SQLite: Native handling of SQL databases in MQL5"

 

New article SQLite: Native handling of SQL databases in MQL5 has been published:

The development of trading strategies is associated with handling large amounts of data. Now, you are able to work with databases using SQL queries based on SQLite directly in MQL5. An important feature of this engine is that the entire database is placed in a single file located on a user's PC.

Debugging SQL queries in MetaEditor

All functions for working with the database return the error code in case of an unsuccessful code. Working with them should not cause any issues if you follow four simple rules:

  1. all query handles should be destroyed after use by DatabaseFinalize();
  2. the database should be closed with DatabaseClose() before completion;
  3. query execution results should be checked;
  4. in case of an error, a query is destroyed first, while the database is closed afterwards.

The most difficult thing is to understand what the error is if the query has not been created. MetaEditor allows opening *.sqlite files and work with them using SQL queries. Let's see how this is done using the company.sqlite file as an example:

1. Open the company.sqlite file in the common terminal folder.

2. After opening the database, we can see the COMPANY table in the Navigator. Double-click on it.

3. The "SELECT * FROM COMPANY" query is automatically created in the status bar.

4. The query is executed automatically. It can also be executed by pressing F9 or clicking Execute.

5. See the query execution result.

6. If something is wrong, the errors are displayed in the editor’s Journal.


SQL queries allow obtaining statistics on table fields, for example, the sum and the average. Let's make the queries and check if they work.

Author: MetaQuotes

MetaQuotes
  • www.mql5.com
Trader's profile
 

Who has figured it out, please show the implementation of this task.

  1. There are two Terminals.
  2. It is necessary to transfer real-time quotes of one symbol from Terminal1 to the corresponding custom symbol of Terminal2.
 
Thanks for the article and good examples of useful applications.
 
fxsaber:

Who has figured it out, please show the implementation of such a task.

  1. There are two Terminals.
  2. It is necessary to transfer real-time quotes of one symbol from Terminal1 to the corresponding custom symbol of Terminal2.

Within the framework of this task, do I understand correctly that in both cases (when reading the base on Terminal2 and writing to the base in Terminal1) it must be blocked through the transaction mechanism?


What is the cheapest way to determine that the database has been updated?

 
fxsaber:

Within the framework of this task, do I understand correctly that in both cases (when reading the base on Terminal2 and writing to the base in Terminal1) it should be blocked through the transaction mechanism?

No, this is a different locking. If the base is already opened in another MQL5 programme or MetaEditor, you will get the 5124 error when trying to modify it.

 
Rashid Umarov:

No, this is a different lock. If the base is already opened in another MQL5 program or MetaEditor, you will get the 5124 error when trying to modify it.

It turns out that data exchange has fewer possibilities than through files?

 
fxsaber:

Does it appear that sharing data has fewer possibilities than through files?

depends on the database implementation itself

files are always inconvenient - sequential access and the same problems when working with a single file

 
fxsaber:

Does it appear that sharing data has fewer possibilities than through files?

SQLite is not a server-side database, but an embedded database. Therefore, it cannot provide joint and simultaneous use from different processes.

Its purpose is for internal use in MQL5 and sharing (not simultaneous use)/transferring with other systems.

The advantage of the inbuilt in-house database is that it is available in any agents and allows you to conveniently work with data remotely and in the strategy tester.


Within the framework of different MQL5 Expert Advisors running in one terminal, you can share the same database.

 
Renat Fatkhullin:

SQLite is not a server-side database, but an embedded database. Therefore, it cannot provide sharing and concurrent use from different processes.

Within different experts running in the same terminal, you can share the same database.

I understand now, thank you.

 

Is it possible to use what is described for µl5 in µl4?

Are the functions and library operations compatible? Or is it only for µl5?

 
Chiripaha:

Is what is described for µl5 possible to use in µl4?

Are the functions and library operations compatible? Or is it only for µl5?

Native SQLite support is only in MetaTrader 5 & MQL5.

This is not a library, but a standard MQL5 functionality with deep integration into the whole system, including strategy testing agents.