Working with Databases

New comment
 

Hello everyone,

i am working on an indicator which uses data from the mql5 intern database.

Currently, I access the database after each M1 candle and take exactly one value.  This results in the problem that the indicator has very long calculation times.

Therefore, I thought to asign  a whole column from my SQLite file into an array in mql5.


The only function that looked suitable would be this ( i tried to use it, but it doesn´t work):


Gets a field value as an array from the current record. 

bool  DatabaseColumnBlob(
   int    request,     // request handle received in DatabasePrepare
   int    column,      // field index in the request
   void&  data[]       // the reference to the variable for receiving the value
   );

Parameters

request

[in]  Request handle received in DatabasePrepare().

column

[in]  Field index in the request. Field numbering starts from zero and cannot exceed DatabaseColumnsCount() - 1.

data[]

[out]  Reference to the array for writing the field value.


If you have some idea with the work of databases, I would be happy for helpful tips!
 
Claudius Marius Walter:i am working on an indicator which uses data from the mql5 intern database.


Currently, I access the database after each M1 candle and take exactly one value.  This results in the problem that the indicator has very long calculation times.

Therefore, I thought to asign  a whole column from my SQLite file into an array in mql5.

If you have some idea with the work of databases, I would be happy for helpful tips!

We already know what is in the Documentation, so it serves no purpose to show us it again.

Instead, provide a sample of your code of how you are using it. Hopefully, a sample that can be compiled and executed so that we can see if we are able to replicate the problem and find a solution for you!

 
Sorry Fernando, i thought it would be enough. It´s a bit diffucult to upload all relevant datas.
I don´t know why, but i cannot upload the database..

Nevertheless here is the Indicator code:

#property indicator_buffers 1
#property indicator_plots   1
#property indicator_type1   DRAW_LINE
#property indicator_color1  clrRed
#property indicator_label1  "Number of trades"

double    tradesBuffer[];
double    quoteAssetVolume;
int       numberOfTrades;
double    takerBuyBaseAssetVolume;
double    takerBuyQuoteAssetVolume;
datetime  candleOne;
datetime  lastM1Bar;
datetime  newM1Bar;
int       db;
string    filename="AAVEUSDT.db"; 


int OnInit()
  {
   //--- indicator buffers mapping
   SetIndexBuffer(0,tradesBuffer,INDICATOR_DATA);
   
   return(INIT_SUCCEEDED);
  }

int OnCalculate(const int rates_total,
                const int prev_calculated,
                const datetime &time[],
                const double &open[],
                const double &high[],
                const double &low[],
                const double &close[],
                const long &tick_volume[],
                const long &volume[],
                const int &spread[])
  {



//int limit =rates_total ;
   //if(limit == 0) limit++;
    int i = rates_total - 30; // Calculate only the last 30 bars
    //120
   for(i ; i < rates_total; i++)
      {   
       //--- Open a database in Terminal/Common
       db=DatabaseOpen(filename, DATABASE_OPEN_READONLY | DATABASE_OPEN_COMMON);
       if(db==INVALID_HANDLE)
        {
         Print("DB: ", filename, " open failed with code Database open ", GetLastError());
        }
       
       //--- Ask for date and time for the 1.candle and convert it into a string value
       candleOne = time[i];
      // Print("Timestamp: ",time[i]);
       
       //--- create an SQL command
       string sqlCommand = " SELECT * FROM test3 WHERE OpenTime='"; 
       StringAdd(sqlCommand,TimeToString(candleOne));
       StringAdd(sqlCommand,":00'");
       
       //---create handle for DatabaseRead()
       int request=DatabasePrepare(db, sqlCommand);  
       if(request==INVALID_HANDLE)
        {
         Print("DB: ", filename, " request failed with code Database prepare ", GetLastError());
         DatabaseClose(db);    
        }
   
       //--- read the values of each field from the obtained entry
       for(int j=0; DatabaseRead(request); j++)
        {
         if(//DatabaseColumnDouble(request, 1, quoteAssetVolume)       && 
            DatabaseColumnInteger(request, 2, numberOfTrades)     
            //DatabaseBindArray(request,2,numberOfTrades)
            //DatabaseColumnDouble(request, 3, takerBuyBaseAssetVolume)&&
            //DatabaseColumnDouble(request, 4, takerBuyQuoteAssetVolume)
            )
             {
             tradesBuffer[i] = numberOfTrades;
             Print("numberOfTrades: ",numberOfTrades);
             }
         else
             {
             Print(j, ": DatabaseRead() failed with code database read ", GetLastError());
             DatabaseFinalize(request);
             DatabaseClose(db);
             }
         }
       //--- quit the request
       DatabaseFinalize(request);
   
       //--- close database
       DatabaseClose(db);
      }
   return(rates_total);
  }
Here is a example of the database:
In my case i want to select the whole NumberOfTrades column and asign it to an array. Is this possible if i use the DatabaseColumnBlob() function or do i need to tackle it different?

 
Claudius Marius Walter: Sorry Fernando, i thought it would be enough. It´s a bit diffucult to upload all relevant datas. I don´t know why, but i cannot upload the database.. Nevertheless here is the Indicator code:

If you are having trouble uploading a file, just ZIP it.

In your code, there is no mention of the said function you mentioned, namely "DatabaseColumnBlob". So where exactly are you having problems?

 

Also, why on earth are you opening and scanning the database and closing it on every tick?

That is going to make it SLOOOOOOOW! Open it only once and reuse the handle!

Also, does this have anything to do with the following thread?

Forum on trading, automated trading systems and testing trading strategies

How can virtual trades be kept after MT5 restart

Volker Albrandt, 2021.05.14 10:54

I want to use an EA for MT5 that uses virtual trades as an indicator before entering real trades.
I have talked to a Coder and he told me that it is no problem to code this.
But it is not possible to keep this virtual trades, after MT5 has been restarted.

Does anyone know an option, where the virtual trades can be kept, after a MT5 restart?

 
Fernando Carreiro:

If you are having trouble uploading a file, just ZIP it.

In your code, there is no mention of the said function you mentioned, namely "DatabaseColumnBlob". So where exactly are you having problems?

Thansk for your answer, i actually ziped it, but it doesnt´work.

Here is a link from my own cloud. https://mega.nz/folder/wwBhhCLb#T90vwwQGKRCrG1csJThgoA

The file is called "Files.rar" The AAVEUSDT.db must be safed into the common folder.
CustomSymbols.json must be uploaded as a custom symbol. But I think it´s not necessary, because the indicator works (more or less) also on a normal chart like EURUSD



i want to implement the DatabaseColumnBlob() in the second for-iteration in line 80. See the the modified code below:

#property indicator_buffers 2
#property indicator_plots   1
#property indicator_type1   DRAW_LINE
#property indicator_color1  clrRed
#property indicator_label1  "Number of trades"

double    tradesBuffer[];
double    numberOfTradesArray[];
double    quoteAssetVolume;
int       numberOfTrades;
double    takerBuyBaseAssetVolume;
double    takerBuyQuoteAssetVolume;
datetime  candleOne;
datetime  lastM1Bar;
datetime  newM1Bar;
int       db;
string    filename="AAVEUSDT.db"; 


int OnInit()
  {
   //--- indicator buffers mapping
   SetIndexBuffer(0,tradesBuffer,INDICATOR_DATA);
   SetIndexBuffer(1,numberOfTradesArray,INDICATOR_DATA);
  
   return(INIT_SUCCEEDED);
  }

int OnCalculate(const int rates_total,
                const int prev_calculated,
                const datetime &time[],
                const double &open[],
                const double &high[],
                const double &low[],
                const double &close[],
                const long &tick_volume[],
                const long &volume[],
                const int &spread[])
  {



//int limit =rates_total ;
   //if(limit == 0) limit++;
    int i = rates_total - 30; // Calculate only the last 30 bars
    //120
   for(i ; i < rates_total; i++)
      {   
       //--- Open a database in Terminal/Common
       db=DatabaseOpen(filename, DATABASE_OPEN_READONLY | DATABASE_OPEN_COMMON);
       if(db==INVALID_HANDLE)
        {
         Print("DB: ", filename, " open failed with code Database open ", GetLastError());
        }
       
       //--- Ask for date and time for the 1.candle and convert it into a string value
       candleOne = time[i];
      // Print("Timestamp: ",time[i]);
       
       //--- create an SQL command
       string sqlCommand = " SELECT * FROM test3 WHERE OpenTime='"; 
       StringAdd(sqlCommand,TimeToString(candleOne));
       StringAdd(sqlCommand,":00'");
       
       //---create handle for DatabaseRead()
       int request=DatabasePrepare(db, sqlCommand);  
       if(request==INVALID_HANDLE)
        {
         Print("DB: ", filename, " request failed with code Database prepare ", GetLastError());
         DatabaseClose(db);    
        }
   
       //--- read the values of each field from the obtained entry
       for(int j=0; DatabaseRead(request); j++)
        {
         if(//DatabaseColumnDouble(request, 1, quoteAssetVolume)       && 
            DatabaseColumnInteger(request, 2, numberOfTrades) &&    
            DatabaseColumnBlob(request,2, numberOfTradesArray)
            //DatabaseBindArray(request,2,numberOfTrades)
            //DatabaseColumnDouble(request, 3, takerBuyBaseAssetVolume)&&
            //DatabaseColumnDouble(request, 4, takerBuyQuoteAssetVolume)
            )
             {
             tradesBuffer[i] = numberOfTrades;
             Print("numberOfTrades: ",numberOfTrades);
             }
         else
             {
             Print(j, ": DatabaseRead() failed with code database read ", GetLastError());
             DatabaseFinalize(request);
             DatabaseClose(db);
             }
         }
       //--- quit the request
       DatabaseFinalize(request);
   
       //--- close database
       DatabaseClose(db);
      }
   return(rates_total);
  

The problem with this function is, that the numberOfTradesArray doesnt´t get filled with the correct values:


321.01 MB folder on MEGA
321.01 MB folder on MEGA
  • mega.nz
2 files
 
Fernando Carreiro:

Also, why on earth are you opening and scanning the database and closing it on every tick?

That is going to make it SLOOOOOOOW! Open it only once and reuse the handle!

Also, does this have anything to do with the following thread?


Oh thanks for your tip, i will fix it.

Never heard about this thread..
 
Claudius Marius Walter:

Thansk for your answer, i actually ziped it, but it doesnt´work.

Here is a link from my own cloud. https://mega.nz/folder/wwBhhCLb#T90vwwQGKRCrG1csJThgoA

The file is called "Files.rar" The AAVEUSDT.db must be safed into the common folder.
CustomSymbols.json must be uploaded as a custom symbol. But I think it´s not necessary, because the indicator works (more or less) also on a normal chart like EURUSD



i want to implement the DatabaseColumnBlob() in the second for-iteration in line 80. See the the modified code below:

The problem with this function is, that the numberOfTradesArray doesnt´t get filled with the correct values:


I don't want to be offensive, but could you please be more careful, read the documentation and instructions and stop wasting people time ?

1. This link https://mega.nz/folder/wwBhhCLb#T90vwwQGKRCrG1csJThgoA doesn't work. 

2. It's good to check the documentation but it's better to read and understand it.

DatabaseColumnBlob() will read a row into an array, not suitable if you want to read a column as stated in your OP.

3. When you attach a file there is message to read

4. Fernando already talked to you about opening the DB on every tick. You even open it in a loop ! That's insane.

 for(i ; i < rates_total; i++)
      {   
       //--- Open a database in Terminal/Common
       db=DatabaseOpen(filename, DATABASE_OPEN_READONLY | DATABASE_OPEN_COMMON);
       if(db==INVALID_HANDLE)
        {
         Print("DB: ", filename, " open failed with code Database open ", GetLastError());
        }
   ...
 
Excuse me, Alain.

I didn´t understand the documentation correctly... But your comment helps alot.

Nevertheless. Do you have any idea how to extract a whole column from the database and asign it to an array?



Here is the right .zip file
Files:
Files.zip  7847 kb
 
Claudius Marius Walter:
Excuse me, Alain.

I didn´t understand the documentation correctly... But your comment helps alot.

Nevertheless. Do you have any idea how to extract a whole column from the database and asign it to an array?



Here is the right .zip file

Maybe reading the documentation in your native language would help ? Not sure if you know there is a German section on this forum.

There is no such possibilities with SQL to my knowledge. You need to loop through all records and put the needed data yourself into an array or a structure.

I don't know what you are trying to do but are you sure you need a database for that ? For sure, your way to use it is not good, when working with files (which databases are, unless you use a memory DB) you need to be careful about the performance.

 
The german documentation is horrible.. Sometimes I am very confused. Nevertheless, I will be better (thanks to you!) and can help at least in the German forum at some topics.


Claudius Marius Walter:
Sorry Fernando, i thought it would be enough. It´s a bit diffucult to upload all relevant datas.
I don´t know why, but i cannot upload the database..

Nevertheless here is the Indicator code:

Here is a example of the database:
In my case i want to select the whole NumberOfTrades column and asign it to an array. Is this possible if i use the DatabaseColumnBlob() function or do i need to tackle it different?

At the bottom of this post a table is attached. This table includes additional course data, such as the "number of trades". From these M1 data I would like to make an indicator which shows me for example the number of trades of every bar.

The additional course data is from a custom symbol. I have about 200 Custom symbols. Each custom symbol has about 1 000 000 lines. Therefore, I thought that a database would be the smartest solution. Do you agree with that?
12
New comment
Reason: