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

[MetaQuotes]

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

[Faisal Mahmood]

Questions on this solution

- Are there any issues with multiple EAs use the same sqlite database concurrently?

- If MT5 crashes, can some data be lost? How often does it write data to disk?

[Zbigniew Mirowski]

Hi,

Something doesn't seem right though.
I created a "signals.sqlite" database and got confirmation that it exists. I saved the data to the database and got confirmation that 63 records were saved. Unfortunately, I am unable to read anything from the database and when I check the database, I cannot find the "signals.sqlite" file. However, i find the directory  in XM MT5/Bases/signals and in this directory the file "signals.dat", which is unreadable for me and has a size of 3845 kb.
The question is where can I find the "signals.sqlite" file and if not, how can I edit it?

I use XM MT5 build 3521

[Fernando Carreiro]

Zbigniew Mirowski #: Something doesn't seem right though. I created a "signals.sqlite" database and got confirmation that it exists. I saved the data to the database and got confirmation that 63 records were saved. Unfortunately, I am unable to read anything from the database and when I check the database, I cannot find the "signals.sqlite" file. However, i find the directory  in XM MT5/Bases/signals and in this directory the file "signals.dat", which is unreadable for me and has a size of 3845 kb. The question is where can I find the "signals.sqlite" file and if not, how can I edit it? I use XM MT5 build 3521

The file is usually stored in "<data folder>\MQL5\Files" unless you used the DATABASE_OPEN_COMMON flag.

The "signals.dat" has nothing to with SQLite.

[Zbigniew Mirowski]

Fernando Carreiro #:

The file is usually stored in "<data folder>\MQL5\Files" unless you used the DATABASE_OPEN_COMMON flag.

The "signals.dat" has nothing to with SQLite.

Hi, Fernando, thanks for answer

I expected it, because it is in the description, but unfortunately the files directory is empty and I created the database with this command:

filename="signals.sqlite"

int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE |DATABASE_OPEN_COMMON);

e.g. with DATABASE_OPEN_COMMON flag

DATABASE_OPEN_COMMON

The file is in the common folder of all terminals

how to interpret ii?

[Fernando Carreiro]

@Zbigniew Mirowski #: I expected it, because it is in the description, but unfortunately the files directory is empty and I created the database with this command:

filename="signals.sqlite"

int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE |DATABASE_OPEN_COMMON);

e.g. with DATABASE_OPEN_COMMON flag

DATABASE_OPEN_COMMON

The file is in the common folder of all terminals

how to interpret ii?

Then you will find it in "%APPDATA%\MetaQuotes\Terminal\Common\Files".

[Zbigniew Mirowski]

Fernando Carreiro #:
Then you will find it in "%APPDATA%\MetaQuotes\Terminal\Common\Files".

Well, now I have a new problem. I came to the conclusion that maybe the error lies in the fact that I installed this MT5 on the C drive in Program files and maybe there are some access restrictions in the new Win11. So I installed a new instance on another disk, new demo account and tried to install this database there and... I got:
2022.11.30 10:37:26.036 CreateDB (EURUSD,H1) database error, table Signals already exists.
When creating a database, I immediately create tables. Create does not throw an error, because it is also opening the database, but creating a table throws an error.
I searched the whole computer and the "signals.sqlite" files were not found, but I found the "signals.dat" file again

Wow, I found the file "signals.sqlite"

C:\Users\name\AppData\Roaming\MetaQuotes\Terminal\Common\Files

there is only structure in the "signals.sqlite" file and I suspect there is data in the signals.dat file

C:\Users\name\AppData\Roaming\MetaQuotes\Terminal\Common\Files

[Fernando Carreiro]

Zbigniew Mirowski #: Wow, I found the file "signals.sqlite" C:\Users\name\AppData\Roaming\MetaQuotes\Terminal\Common\Files

But is that not exactly where I told you where you would find it?

Then you will find it in "%APPDATA%\MetaQuotes\Terminal\Common\Files".

[Zbigniew Mirowski]

Fernando Carreiro #:

But is that not exactly where I told you where you would find it?

Then you will find it in "%APPDATA%\MetaQuotes\Terminal\Common\Files".

You were right, but I didn't find him there yesterday, there is only structure in the "signals.sqlite" file and I suspect there is data in the signals.dat file

but it's in the documentation
parameters

filename

[in] File name relative to the "MQL5\Files" folder.

[Fernando Carreiro]

Zbigniew Mirowski #:
You were right, but I didn't find him there yesterday, there is only structure in the "signals.sqlite" file and I suspect there is data in the signals.dat file

No! I have already told you that the "signals.dat" is an internal MetaTrader data file and it has nothing to do with SQLite.

The "signals.dat" file is where MetaTrader stores the information about signals that can be retrieved with the Trade Signals functionality ...

Code Base

Listing all MT5 Signals' properties to a CSV file

Fernando Carreiro, 2022.04.21 15:14

This simple quick & dirty script code will output a CSV file of all the Signals' properties as reported by the MQL5 Trade Signals functionality.

[Fernando Carreiro]

Zbigniew Mirowski #: but it's in the documentation. parameters, filename, [in] File name relative to the "MQL5\Files" folder.

But you used the flag "DATABASE_OPEN_COMMON" which changes the location. So don't use that flag if you don't want it in the common folder.

DATABASE_OPEN_COMMON

The file is in the common folder of all terminals