Discussion of article "How to Access the MySQL Database from MQL5 (MQL4)" - page 7

 

Ok, I see you have written an expert advisor for this purposes, and it is written without recommendations I've posted in article.

So, let's move step by step: 1.

1. The calling of " DB = cMySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); " should be made inside OnInit() standard function instead of OnTick(). 2.

2. You are uses cMySqlConnect - it's imported function form DLL, you have to use MySqlConnect function instead of cMySqlConnect function !

3. You have to call MySqlDisconnect function inside OnDeinit() stundard function.

4. You have to check database connection identifier inside OnTick() standard function to be sure that connect was successful.

Finally it will looks like.

... your code
int DB = -1; // database identifier//--
... your code
int OnInit()
{
   DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); 

   if (DB != -1)
    {
    // connection succeeded!
    // here you have to define table creation logic
   }
   return(INIT_SUCCEEDED);
}

void OnDeinit(const int reason)
 {
 MySqlDisconnect(DB);
 }

void OnTick()
{
 // first command.
 if (DB==-1) return; // do not any action when no connection

 // here your code without cMySqlConnect call
}
You have to rebuild your code based on the requirements of project, as I see the currently there is no clear logic, everything messed up.
 
elugovoy:

Ok, I see you have written an expert advisor for this purposes, and it is written without recommendations I've posted in article.

So, let's move step by step: 1.

1. The calling of " DB = cMySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); " should be made inside OnInit() standard function instead of OnTick(). 2.

2. You are uses cMySqlConnect - it's imported function form DLL, you have to use MySqlConnect function instead of cMySqlConnect function !

3. You have to call MySqlDisconnect function inside OnDeinit() stundard function.

4. You have to check database connection identifier inside OnTick() standard function to be sure that connect was successful.

Finally it will looks like.

Thanks, I'll try again and give the result back.
 
elugovoy:

Ok, I see you have written an expert advisor for this purposes, and it is written without recommendations I've posted in article.

So, let's move step by step: 1.

1. The calling of " DB = cMySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); " should be made inside OnInit() standard function instead of OnTick(). 2.

2. You are uses cMySqlConnect - it's imported function form DLL, you have to use MySqlConnect function instead of cMySqlConnect function !

3. You have to call MySqlDisconnect function inside OnDeinit() stundard function.

4. You have to check database connection identifier inside OnTick() standard function to be sure that connect was successful.

Finally it will looks like.

I changed step by step, but the problem is still exist when use the expert in one mt4 for four or more symbols. it print " Access violation read to 0x0000000B in '... .MQLMySQL.dll'
"

the code is followed

<--


#include <MQLMySQL.mqh

int MySqlErrorNumber; // recent MySQL error number

string MySqlErrorDescription; // error description


//+------------------------------------------------------------------+

//| Expert initialisation function & nbsp; |

//+------------------------------------------------------------------+

int OnInit()

OnInit(); int OnInit()

EventSetTimer(timeSeconds);

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

if( DB == -1 )

{

Print("Database is not connected ..."); if( DB == -1 ) { print("Database is not connected ... "); }

}

return(INIT_SUCCEEDED); }

}

//+------------------------------------------------------------------+

//| Expert deinitialisation function |

//+------------------------------------------------------------------+

void OnDeinit(const int reason)

{

MySqlDisconnect(DB);

EventKillTimer();

ObjectsDeleteAll();

}


void OnTimer()

{

//Alert(TimeCurrent());

OnTick();

}


//+------------------------------------------------------------------+

//| Expert tick function & nbsp; |

//+------------------------------------------------------------------+

void OnTick()

{

if( DB == -1 )

{

Alert("Database is not connected ... "); return; { Alert("Database is not connected ...")

return; }

}

if( IsExpertEnabled() && IsConnected() && AccountNumber() > 0 && DB ! = -1 )

{

int account = AccountNumber();

string symbol = Symbol(); if( cmd !

if( cmd ! = "" && cmd ! = NULL )

{

symbol = cmd; }

}

symbolOrder = symbol + "_table";

double spread = (Ask - Bid); } symbolOrder = symbol + "_table"; }

admission = spread*MathPow(10, Digits).

//int DB = cMySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);

Query = "SELECT * FROM " + symbol + " where AccountNumber = " + (string)AccountNumber();

//Print(Query);

int Cursor1 = MySqlCursorOpen(DB, Query);

if (Cursor1 >= 0)

{

int Rows1 = MySqlCursorRows(Cursor1); int Cursor1 = MySqlCursorOpen(DB, Query); if (Cursor1 >= 0)

int dataRows1 = Rows1;

//Alert(dataRows);

if( dataRows1 > 0 )

{

Query = "update " + symbol + " set Bid = " + (string)Bid + ", Ask = " + ( string)Ask

+ ", Spread = " + DoubleToStr(spread, Digits)

+ ", Time = '" + TimeToString(TimeLocal(), TIME_DATE|TIME_SECONDS ) + "' where AccountNumber = "

+ (string)account; // + "' and Symbol = '" + symbol + "'".

MySqlExecute(DB, Query);

MySqlCursorClose(Cursor1);

}

else if( dataRows1 == 0 )

{

Query = "CREATE TABLE IF NOT EXISTS " + symbol + " (id int NOT NULL AUTO_ INCREMENT PRIMARY KEY, AccountNumber int, "

+ "Symbol char(20), Bid double, Ask double, Spread double,"

+ "Memo char(50), "

+ "Time datetime) ENGINE=MEMORY DEFAULT CHARSET=utf8 ";

MySqlExecute(DB, Query);

Query = "INSERT INTO " + symbol + "(AccountNumber, Symbol, Bid, Ask, Spread. Memo, Time) VALUES ("

+ (string)account + ", '" + symbol + "', "+(string)Bid+","+ (string)Ask + ", "

+ DoubleToStr(spread, Digits)

+ ", '" + (string)AccountCompany()

+ "', \'"+TimeToString(TimeLocal(), TIME_DATE|TIME_SECONDS)+"\')"";

if(MySqlExecute(DB, Query) ! = true )

{

//Query = "DROP TABLE IF EXISTS `data_table`";

//MySqlExecute(DB, Query);

Query = "CREATE TABLE IF NOT EXISTS " + symbol + "(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, AccountNumber int, "

+ "Symbol char(20), Bid double, Ask double, Spread double,"

+ "Memo char(50), "

+ "Time datetime) ENGINE=MEMORY DEFAULT CHARSET= utf8 ";

MySqlExecute(DB, Query);

}

}

MySqlCursorClose(Cursor1); // NEVER FORGET TO CLOSE CURSOR !!!!

}

}

}

-->

 
elugovoy:

Ok, I see you have written an expert advisor for this purposes, and it is written without recommendations I've posted in article.

So, let's move step by step: 1.

1. The calling of " DB = cMySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); " should be made inside OnInit() standard function instead of OnTick(). 2.

2. You are uses cMySqlConnect - it's imported function form DLL, you have to use MySqlConnect function instead of cMySqlConnect function !

3. You have to call MySqlDisconnect function inside OnDeinit() stundard function.

4. You have to check database connection identifier inside OnTick() standard function to be sure that connect was successful.

Finally it will looks like.

The problem is it can't use in one mt4 for more than two symbols, if use in only one symbol, it's normal. When use in four or more, it's run fine, but after a few minutes, it prints the problem "OnTick()". minutes, it prints the problem "Access violation read to 0x0000000B in '...MQLMySQL.db'". .MQLMySQL.dll'"

The exambles is all scripts, it's run only one times, You may try one experts call the dll in four or more symbols in one or two mt4, You may find the problem.

Would be the dll needs a memeoy release or garbe collection?

 
MFC dumps leaks prematurely whenit exits, instead of waiting for the
CRT to dump leaks followi ng static data destruction, and this causes &#959; spurious leak reports for objects which allocated memory before MFC was initialised and which thus are destroyed after MFC nbsp;
spurious leak reports for objects which allocated memory before MFC
was initialised and which thus are destroyed after MFC exits. This is
commonly observed when using the MFC DLL in a program that also uses
the C++ runtime DLL.
 

Hello once again.

Based on the code you have provided I guess you are new to MQL.

I'm sorry, I have no time to teach you, but I have time for testing the software I've developed.

I'm sorry, I have no time to teach you, but I have time for testing the software I've developed. So, here are logs attached.

Have build testing EA for you, based on your logic: each table defined for each currency pair and can keep the online market data for different accounts.

You can inspect the logs, no one error "Access violation..." was raised. You can inspect the logs, no one error "Access violation..." was raised.

The problem is not in MQLMySQL library.

Testing EA.

//+------------------------------------------------------------------+
//|& nbsp; &nbsp ; DFTest.mq4 |
//|& nbsp; Copyright 2014, MetaQuotes Software Corp. |
//|& nbsp; &nbsp ; http://www.mql5.com |
//+------------------------------------------------------------------+
#property copyright "Copyright 2014, MetaQuotes Software Corp."
#property link      "http://www.mql5.com"
#property version   "1.00"
#property strict
#include <MQLMySQL.mqh>

input string Host = "localhost";
input string User = "root";
input string Password = "ioctrl";
input string Database = "mysql";
input int Port = 3306;
input string Socket = "";
input int ClientFlag = 0;

input int Timer = 1; // Timer (seconds) not used now

int DB;

int OnInit()
{
 SQLTrace = true; // to see all the queries would be sent to the database
       EventSetTimer(Timer);
       DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); 
       if( DB == -1 )
       {
            Print("Database is not connected! Error: ", MySqlErrorDescription);
            return (INIT_FAILED);
       }
       
 // create table if not exists
 string cmd;
 cmd = "CREATE TABLE IF NOT EXISTS `" + Symbol() + "` (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, AccountNumber int, "
       + "Symbol char(20), Bid double, Ask double, Spread double, " 
       + "Memo char(50), " 
       + "Time datetime) ENGINE=MEMORY DEFAULT CHARSET=utf8";
 if (!MySqlExecute(DB, cmd))
    {
     Print ("Table creation error: ",MySqlErrorDescription);
     return (INIT_FAILED);
    }
                      

       return(INIT_SUCCEEDED);
}

void OnDeinit(const int reason)
{
      MySqlDisconnect(DB); 
      EventKillTimer();
}


void OnTimer()
{
 // you should never call OnTick from OnTimer and vice versa.
 // the target of these 2 functions is different
}

void OnTick()
{
 int account;
 string symbol;
 double spread;
 string Query, cmd;
 int Cursor;
 int Rows;
 
 if (DB == -1)
    {
    Comment("Database is not connected ... ");
    return;
    }

 if ( IsExpertEnabled() && IsConnected() && AccountNumber() > 0 )
    {
     account = AccountNumber();
     symbol = "`"+Symbol()+"`"; // if you have to use it as table name you have to use quotes as a good practice for MySQL database
     
 
 spread = (Ask - Bid);
 
 // possible it could be better to use something like this: admission = MarketInfo(symbol, MODE_SPREAD);
 Query = "SELECT * FROM " + symbol + " where AccountNumber = " + (string)account;
 Cursor = MySqlCursorOpen(DB, Query);
 if (Cursor >= 0)
    {
      Rows = MySqlCursorRows(Cursor);
      if ( Rows > 0 )
         {
          cmd = "update " + symbol + " set Bid = "+(string)Bid + ", Ask = " + (string)Ask
                       + ", Spread = " + DoubleToStr(spread, Digits)  
                       + ", Time = '" + TimeToString(TimeLocal(), TIME_DATE|TIME_SECONDS) +"' where AccountNumber = " + (string)account;
         }
      else
         {
          cmd = "INSERT INTO " + symbol + "(AccountNumber, Symbol, Bid, Ask, Spread, Memo, Time) VALUES (" 
                + (string)account + ", \'" + symbol + "\', "+DoubleToStr(Bid,Digits)+","+ DoubleToStr(Ask,Digits) + ","  + DoubleToStr(spread, Digits) 
                + ", \'" + AccountCompany() +"\', \'"+TimeToString(TimeLocal(), TIME_DATE|TIME_SECONDS)+"\')";
         }
      if (!MySqlExecute(DB, cmd))
         {
          Print("Updating error: ",MySqlErrorDescription);
          Print (cmd);
         }

      MySqlCursorClose(Cursor);
     }
  }

} 

 

This is right logic of using the library.

Due to optimisation you can even do not use SELECT statement, just execute UPDATE statement , than check if (MySqlRowsAffected()==0) means no one row was You have ~100% of updates, so apply the INSERT statement.

You have ~100% of updates, so this workaround can increase the performance and reduce network traffik.

And at the end, full source codes of project (include DLL development) has been attached to the article, you may change it on your own way.

If you fill that the problem in MqlMySQL.DLL anyway, you can debug it and fix for yourself.

Best wishes, Eugene

Eugene

Files:
logs.zip  302 kb
 

Great work, Thank you very much Eugeniy .

Your codes help me so much, i can save so much time. I tried to do INSERT, SELECT, UPDATE and DELETE query successfully. If i am not wrong, the "Cursor" is needed only for SELECT query only ??

= pedma

//+------------------------------------------------------------------+
//|                                                    MySQL-006.mq4 |
//|                                   Copyright 2014, Eugene Lugovoy |
//|                                        http://www.fxcodexlab.com |
//| Select, Insert, Update & Delete Query                            |
//| modified by : pedma                                              |
//+------------------------------------------------------------------+

#property copyright "Copyright 2014, Eugene Lugovoy."
#property link      "http://www.fxcodexlab.com"
#property version   "1.00"
#property strict

#include <MQLMySQL.mqh>

string INI;
//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart() {
 string Host, User, Password, Database, Socket; // database credentials
 int Port,ClientFlag;
 int DB; // database identifier
 
 Print (MySqlVersion());

 INI = "C:\\This\\Must\\be\\Real\\Path\\To\\MyConnection.ini";
 
 // reading database credentials from INI file
 Host = ReadIni(INI, "MYSQL", "Host");
 User = ReadIni(INI, "MYSQL", "User");
 Password = ReadIni(INI, "MYSQL", "Password");
 Database = ReadIni(INI, "MYSQL", "Database");
 Port     = StrToInteger(ReadIni(INI, "MYSQL", "Port"));
 Socket   = ReadIni(INI, "MYSQL", "Socket");
 /// ClientFlag = StrToInteger(ReadIni(INI, "MYSQL", "ClientFlag"));  
 ClientFlag = CLIENT_MULTI_STATEMENTS;

 Print ("Host: ",Host, ", User: ", User, ", Database: ",Database);
 
 // open database connection
 Print ("Connecting...");
 
 DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);
 
 if (DB == -1) { Print ("Connection failed! Error: "+MySqlErrorDescription); return; } else { Print ("Connected! DBID#",DB);}
 
 // executing SELECT statement
 string Query0,Query1,Query2,Query3,Query4,Query5,Query6,Query7;
 int    i,Cursor1,Cursor3,Cursor5,Cursor7,Rows,total;
 
 int      vId;
 string   vCode;
 datetime vStartTime;
 
 Query0 = "DROP TABLE IF EXISTS `test_table`";
 MySqlExecute(DB, Query0);
 
 Query0 = "CREATE TABLE `test_table` (id int, code varchar(50), start_date datetime)";
 if (MySqlExecute(DB, Query0))  {
     Print ("Table `test_table` created.");
 }
 else  {
     Print ("Table `test_table` cannot be created. Error: ", MySqlErrorDescription);
 }
 
 //--- inserting data
 Query0 = "INSERT INTO `test_table` (id, code, start_date) VALUES ("+(string)AccountNumber()+",\'ACCOUNT\',\'"+TimeToStr(TimeLocal(), TIME_DATE|TIME_SECONDS)+"\')";
  if (MySqlExecute(DB, Query0))   {
      Print ("Succeeded: ", Query0);
  }
  else  {
      Print ("Error: ", MySqlErrorDescription);
      Print ("Query: ", Query0);
  }
  
  // multi-insert
  Query0 =          "INSERT INTO `test_table` (id, code, start_date) VALUES (1,\'EURUSD\',\'2014.01.01 00:00:01\');";
  Query0 = Query0 + "INSERT INTO `test_table` (id, code, start_date) VALUES (2,\'EURJPY\',\'2014.01.02 00:02:00\');";
  Query0 = Query0 + "INSERT INTO `test_table` (id, code, start_date) VALUES (3,\'USDJPY\',\'2014.01.03 03:00:00\');";
  if (MySqlExecute(DB, Query0))   {
      Print ("Succeeded! 3 rows has been inserted by one query.");
  }
  else  {
      Print ("Error of multiple statements: ", MySqlErrorDescription);
  }

 //--- select query, need Cursor ----
 Query1 = "SELECT id, code, start_date FROM `test_table`";
 Print ("SQL> ", Query1);
 Cursor1 = MySqlCursorOpen(DB, Query1);
 
 if (Cursor1 >= 0)  {
     Rows = MySqlCursorRows(Cursor1);
     Print (Rows, " row(s) selected.");
     for (i=0; i<Rows; i++) {
         if (MySqlCursorFetchRow(Cursor1))   {
             vId = MySqlGetFieldAsInt(Cursor1, 0); // id
             vCode = MySqlGetFieldAsString(Cursor1, 1); // code
             vStartTime = MySqlGetFieldAsDatetime(Cursor1, 2); // start_time
             Print ("ROW[",i,"]: id = ", vId, ", code = ", vCode, ", start_time = ", TimeToStr(vStartTime, TIME_DATE|TIME_SECONDS));
         }
         total += vId;
     }
     //--- insert data based on query
     Query2 = "INSERT INTO `test_table` (id, code, start_date) VALUES ("+(string)total+",\'ABCDEF\',\'"+TimeToStr(TimeLocal(), TIME_DATE|TIME_SECONDS)+"\');  ";
     if (MySqlExecute(DB, Query2))  {
         Print ("Succeeded! 1 new row has been inserted.");
     }
     else  {
         Print ("Error inserting data based on SELECT query : ", MySqlErrorDescription);
     }
     //---
     MySqlCursorClose(Cursor1); // NEVER FORGET TO CLOSE CURSOR !!!
 }
 else  {
     Print ("Cursor1 opening failed. Error: ", MySqlErrorDescription);
 }
     
 //--- new Cursor for select  ---   
 Query3 = "SELECT id, code, start_date FROM `test_table`";
 Print ("SQL> ", Query3);
 Cursor3 = MySqlCursorOpen(DB, Query3);
 if (Cursor3 >= 0)  {
     Rows = MySqlCursorRows(Cursor3);
     Print (Rows, " row(s) selected.");
     for (i=0; i<Rows; i++) {
         if (MySqlCursorFetchRow(Cursor3))   {
             vId = MySqlGetFieldAsInt(Cursor3, 0); // id
             vCode = MySqlGetFieldAsString(Cursor3, 1); // code
             vStartTime = MySqlGetFieldAsDatetime(Cursor3, 2); // start_time
             Print ("ROW[",i,"]: id = ", vId, ", code = ", vCode, ", start_time = ", TimeToStr(vStartTime, TIME_DATE|TIME_SECONDS));
         }
     }
     //--- update data ----
     Query4 = "UPDATE `test_table` SET id=8,code='PQRXYZ',start_date=\'"+TimeToStr(TimeLocal(), TIME_DATE|TIME_SECONDS)+"\' WHERE code='ABCDEF';  ";
     if (MySqlExecute(DB, Query4))  {
         Print ("Succeeded! last row has been updated (code ABCDEF => PQRXYZ & new id=8.");
     }
     else  {
         Print ("Error updating data of last row : ", MySqlErrorDescription);
     }
     //---
     MySqlCursorClose(Cursor3); // NEVER FORGET TO CLOSE CURSOR !!!
 }
 else  {
     Print ("Cursor3 opening failed. Error: ", MySqlErrorDescription);
 }
 
 //--- show result after updated , new cursor is needed ----   
 Query5 = "SELECT id, code, start_date FROM `test_table`";
 Print ("SQL> ", Query5);
 Cursor5 = MySqlCursorOpen(DB, Query5);
 if (Cursor5 >= 0)  {
     Rows = MySqlCursorRows(Cursor5);
     Print (Rows, " row(s) selected.");
     for (i=0; i<Rows; i++) {
         if (MySqlCursorFetchRow(Cursor5))   {
             vId = MySqlGetFieldAsInt(Cursor5, 0); // id
             vCode = MySqlGetFieldAsString(Cursor5, 1); // code
             vStartTime = MySqlGetFieldAsDatetime(Cursor5, 2); // start_time
             Print ("ROW[",i,"]: id = ", vId, ", code = ", vCode, ", start_time = ", TimeToStr(vStartTime, TIME_DATE|TIME_SECONDS));
         }
     }
     //--- delete query ---
     Query6 = "DELETE FROM `test_table` WHERE id=3;  ";
     if (MySqlExecute(DB, Query6))  {
         Print ("Succeeded! 1 row (id=3) has been deleted.");
     }
     else  {
         Print ("Error deleting 1 row : ", MySqlErrorDescription);
     }
     //---
     MySqlCursorClose(Cursor5); // NEVER FORGET TO CLOSE CURSOR !!!
 }
 else  {
     Print ("Cursor3 opening failed. Error: ", MySqlErrorDescription);
 }
 
 //--- show result after deleted, another cursor is needed ---     
 Query7 = "SELECT id, code, start_date FROM `test_table`";
 Print ("SQL> ", Query7);
 Cursor7 = MySqlCursorOpen(DB, Query7);
 if (Cursor7 >= 0)  {
     Rows = MySqlCursorRows(Cursor7);
     Print (Rows, " row(s) selected.");
     for (i=0; i<Rows; i++) {
         if (MySqlCursorFetchRow(Cursor7))   {
             vId = MySqlGetFieldAsInt(Cursor7, 0); // id
             vCode = MySqlGetFieldAsString(Cursor7, 1); // code
             vStartTime = MySqlGetFieldAsDatetime(Cursor7, 2); // start_time
             Print ("ROW[",i,"]: id = ", vId, ", code = ", vCode, ", start_time = ", TimeToStr(vStartTime, TIME_DATE|TIME_SECONDS));
         }
     }
     //---
     MySqlCursorClose(Cursor7); // NEVER FORGET TO CLOSE CURSOR !!!
 }
 else  {
     Print ("Cursor3 opening failed. Error: ", MySqlErrorDescription);
 }
 //----- at the end ----   
 MySqlDisconnect(DB);
 Print ("Disconnected. Script done!");
}
//+------------------------------------------------------------------+




 
pedma:

Great work, Thank you very much Eugeniy .

Your codes help me so much, i can save so much time. I tried to do INSERT, SELECT, UPDATE and DELETE query successfully. If i am not wrong, the "Cursor" is needed only for SELECT query only ??

= pedma




Exactly man!

The cursors used only for data selection because we have to receive data from db into MQL variable, not just to send the sql command to database.

I'm glad that my solution helps you.

Good luck,

Eugene 

 

Hi Eugene,

 

I was just wondering if you still do paid development projects as I am looking to create a remote trade copier which is integrated into an EA that I will be selling?

I looked on Upwork and you don't appear to have completed any freelancer projects recently.

 

I had a go myself using the library you created, but it didn't turn out too well. Though I'm sure it would be easy for someone with your programming ability!

 

Thanks in advance,

James 

 
James Beach:

Hi Eugene,

 

I was just wondering if you still do paid development projects as I am looking to create a remote trade copier which is integrated into an EA that I will be selling?

I looked on Upwork and you don't appear to have completed any freelancer projects recently.

 

I had a go myself using the library you created, but it didn't turn out too well. Though I'm sure it would be easy for someone with your programming ability!

 

Thanks in advance,

James 

Hello James, 

I'm working at Upwork only on one big project currently. So, have no much free time nowadays. What's the problem you have with libs? How can I help you?