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.
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!
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?
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?
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:
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?
Never heard about this thread..
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
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.
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?
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?
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
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.
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!