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

New comment
 

elugovoy:

      You explained very  clearly.Because，I use a lot of extraneous data for my index ，so the connections become very large ，once more than 32 ，MT4 will point out “error” and show nothing in the index windows.

thank you for your kind help and your effort of developing these all！


Hello,

Yes the max number of connections is 32 

Are you trying to use your MQL program based on MQLMySQL.dll on more than 32 charts concurrently?

Or you just forgot to close the connection in your program? 

To change max connection from 32 to another you have to rebuild MQLMySQL.DLL. The sources are attached to the article, you have to:

1. Unpack sources from "MQLMySQL DLL Project MSVS-2010.zip"

2. Load the project into MS Visual Studio 2010 or higher

3. Open file "MQLMySQL.h"

4. Find string:

and replace value 32 with 64 or whatever you want

5. Rebuild the project

6. Replace an old DLL in your Metatrader data folder with new compiled.

Regards,

Eugene 

 

First all, thanks for the lib, is by far the better solution out there and the most documented.

I was wondering if there is any way to get the affected rows after an update statement, in order to execute an insert if updated records is 0 (the update succeed, just doesn't update any records because the  "where" filter conditions).

I don't want to use a "insert on duplicate key update" because 99.9% of the time the update statement will effectively update 1 record (already exists), and therefore there's no need to execute the insert. I guess there's a performance impact trying to insert always with no need. 

Thanks in advance! 

 
jmhoms:

First all, thanks for the lib, is by far the better solution out there and the most documented.

I was wondering if there is any way to get the affected rows after an update statement, in order to execute an insert if updated records is 0 (the update succeed, just doesn't update any records because the  "where" filter conditions).

I don't want to use a "insert on duplicate key update" because 99.9% of the time the update statement will effectively update 1 record (already exists), and therefore there's no need to execute the insert. I guess there's a performance impact trying to insert always with no need. 

Thanks in advance! 

Hello dear friend, 

If you have MSVC 2010 you can download the sources of MQLMySQL.DLL and add the function you need; the MySQL C++ API has such function (http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html)

Or I can do it for you, but right now I have no time for this, can do on weekend (7-8 March, 2015), is it allright with you?

Regards,

Eugene 

 
elugovoy:

Hello dear friend, 

If you have MSVC 2010 you can download the sources of MQLMySQL.DLL and add the function you need; the MySQL C++ API has such function (http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html)

Or I can do it for you, but right now I have no time for this, can do on weekend (7-8 March, 2015), is it allright with you?

Regards,

Eugene 

Of course it works for me !!! 

 Thank you very much.

Josep M. 

 

Hi Eugeniy, 

did you have the chance to have a look at the "affected rows" feature ?

Greetings,

Josep M. 

 
jmhoms:

Hi Eugeniy, 

did you have the chance to have a look at the "affected rows" feature ?

Greetings,

Josep M. 

 Hello Josep,

I've replied in private message.

Anyway I'm posting here the v2.2. Tested on MT4. If you have troubles with MT5 (x32/x64) just let me know.

Happy working! 

Files:
MQL4_x32.zip  1073 kb
MQL5_x32.zip  1073 kb
MQL5_x64.zip  1076 kb
MQLMySQL_v2_2.zip  5072 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?

1234567891011...13
New comment
Reason: