MySQL Read from Database

To add comments, please log in or register
Josh
130
Josh  

Hi,

i hope here is someone who wants to help me please. I want to read from a MySQL Database, and dont know how to do this. I already have my code ready to establish a connection to the database and too write into the database.


But how can i READ from the database?


I found this: https://forum.mql4.com/15984 MySQL Wrapper and as i see theire is a function to read from the database :

bool MySQL_FetchRow(string sQuery, string &sResultSet[]){
   
   int i;
   if (gbMySQLInitialized == FALSE) return(FALSE);   
   if (MYSQL_VERBOSE > 1) Print(sQuery);
   
   int liLength= StringLen(sQuery);    
   mysql_real_query(giMySQL, sQuery, liLength);   
   
   int liResult = mysql_store_result(giMySQL);
   ArrayResize(sResultSet, mysql_num_fields(liResult));
   MT4_mysql_fetch_row(liResult, sResultSet);  
  
   //for (i = 0; i < ArrayRange(sResultSet,0); i++){
   //     Print(i+"="+sResultSet[i]);
   //}
   mysql_free_result(liResult);
   return(MySQL_NoError());
}

bool MySQL_FetchArray(string sQuery, string &sResultSet[][]){
   
   int i,j;
   if (gbMySQLInitialized == FALSE) return(FALSE);   
   if (MYSQL_VERBOSE > 1) Print(sQuery);
   
   int liLength= StringLen(sQuery);    
   mysql_real_query(giMySQL, sQuery, liLength);   
   
   int liResult = mysql_store_result(giMySQL);
   string lsResult[0];
   int liNumRow   = mysql_num_rows(liResult);
   int liNumField = mysql_num_fields(liResult);
   
   ArrayResize(lsResult, liNumField);
   ArrayResize(sResultSet, liNumRow);
  // string sFieldSet[7];
  // ArrayResize(sFieldSet, mysql_num_fields(iResult));
  // MT4_mysql_fetch_fields_string(iResult, sFieldSet,FIELD_NAME);
   
   for (i = 0; i < liNumRow; i++){
      MT4_mysql_fetch_row(liResult, lsResult);  
      for (j = 0; j < liNumField; j++){
           sResultSet[i][j] = lsResult[j];
           //Print(i+","+j+":="+sResultSet[i][j]);
      }
   }
   mysql_free_result(liResult);
   return(MySQL_NoError());
}

But i dont get this code working on a script or EA. Please help me!!

Josh
130
Josh  
No idea??
Alexander
2441
Alexander  

Try EA

'TicksInMySQL'

It helps you

Josh
130
Josh  
Roger thanks for your reply, but the EA TicksinMySQL writes in to the data base, but i need to read from the data base. Thats my problem!
muyuan11
47
muyuan11  

Yeah,I got it work, I read data from mysql

the origin doc from TSD, I reference it and made sample cade and sample database completely.

  1. run create.sql to make datebase "mt4" and sample table "trades", insert some data into table
  2. open "mqlread.mq4", edit it by your database
  • edit #import "C:\MYSQL5\bin\libmysql.dll", the abosult path is ok
  • in function connect(), edit user,password,DB( DB = "mt4" ok) by your mysql db set
  • then compile it as script (put ex4 to script directory),then close mt4,open mt4 again,to make new script avalible.
  • run script by drop it to chart
  • the data will be show on left up connor of chart

    try it,it will be work . and modify code as your need .

    I got a lot help from this forum, and i hope this tutuial can help u too .


    it work fine :)

    MQLREAD.MQ4

    //+------------------------------------------------------------------+ 
    //|                                                  mySQLTester.mq4 | 
    //|                                            Copyright ?2006, GP2X | 
    //|                                                                  | 
    //+------------------------------------------------------------------+ 
    
    
    
    #property copyright "Copyright ?2006, GP2X" 
    #property link      "" 
    #define DELIM ";" 
    
    
    #import "C:\MYSQL5\bin\libmysql.dll"
    int mysql_init(int db); 
    int mysql_errno(int TMYSQL); 
    int mysql_real_connect( int TMYSQL,string host,string user,string password, string DB,int port,int socket,int clientflag); 
    int mysql_real_query(int TMSQL,string query,int length); 
    void mysql_close(int TMSQL); 
    
    int mysql_store_result(int TMSQL); 
    string mysql_fetch_row(int result); 
    int mysql_num_rows(int result); 
    void mysql_free_result(int result); 
    
    
    int mysql; 
    
    int mTicket, mType; 
    string mSymbol; 
    double mLots, mOpen, mClose, mStopLoss, mTakeProfit; 
    
    //+------------------------------------------------------------------+ 
    //| expert initialization function                                   | 
    //+------------------------------------------------------------------+ 
    int init() 
      { 
       string row; 
       connect(); 
       string query="Select concat(';',concat_ws(';', ticket, symbol, type, lots, open, close, stoploss, takeprofit)) from trades"; 
       
       Print("query = " + query);
       int length=StringLen(query); 
       mysql_real_query(mysql,query,length); 
       int result = mysql_store_result(mysql); 
       int numOfRows = mysql_num_rows(result); 
       for (int i=0;i<numOfRows;i++) { 
          row = mysql_fetch_row(result); 
          decodeTrade(row); 
          Comment("Ticket=", mTicket, ",Symbol=", mSymbol, ",Type=", mType, ",Lots=", mLots, ",Open=", mOpen, ",Close=", mClose, ",SL=", mStopLoss, ",TakeProfit=", mTakeProfit); 
       } 
       mysql_free_result(result); 
        
       return(0); 
      } 
    //+------------------------------------------------------------------+ 
    //| expert deinitialization function                                 | 
    //+------------------------------------------------------------------+ 
    int deinit() 
      { 
       mysql_close(mysql); 
       return(0); 
      } 
    //+------------------------------------------------------------------+ 
    //| expert start function                                            | 
    //+------------------------------------------------------------------+ 
    int start() 
      { 
    //---- 
        
    //---- 
       return(0); 
      } 
    //+------------------------------------------------------------------+ 
    
    void connect() { 
    
       mysql = mysql_init(mysql); 
       if (mysql!=0) Print("allocated"); 
       string host="localhost"; 
       string user="root"; 
       string password="000001"; 
       string DB="mt4"; 
       int clientflag=0; 
       int port=3306; 
       string socket=""; 
       int res=mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag); 
       int err=GetLastError(); 
       if (res==mysql) Print("connected"); 
       else Print("error=",mysql," ",mysql_errno(mysql)," "); 
    
    } 
    
    void decodeTrade(string trade) { 
       int begin = StringFind(trade, DELIM)+1; 
       int end = StringFind(trade, DELIM, begin); 
       mTicket = StrToInteger(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mSymbol = StringSubstr(trade, begin, end-begin); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mType = StrToInteger(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mLots = StrToDouble(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mOpen = StrToDouble(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mClose = StrToDouble(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringFind(trade, DELIM, begin); 
       mStopLoss = StrToDouble(StringSubstr(trade, begin, end-begin)); 
       begin = end+1; 
       end = StringLen(trade); 
       mTakeProfit = StrToDouble(StringSubstr(trade, begin, end-begin)); 
    }  
    

    CREAT.SQL

    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    create database if not exists `mt4`;
    
    USE `mt4`;
    
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    /*Table structure for table `trades` */
    
    DROP TABLE IF EXISTS `trades`;
    
    CREATE TABLE `trades` (
      `ID` int(11) NOT NULL auto_increment,
      `ticket` int(11) default '0',
      `symbol` varchar(50) default 'EURUSD',
      `type` int(11) default '1',
      `lots` decimal(10,5) default '0.10000',
      `open` decimal(10,5) default '0.00000',
      `close` decimal(10,5) default '0.00000',
      `stoploss` decimal(10,5) default '0.00000',
      `takeprofit` decimal(10,5) default '0.00000',
      PRIMARY KEY  (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
    
    /*Data for the table `trades` */
    
    insert  into `trades`(`ID`,`ticket`,`symbol`,`type`,`lots`,`open`,`close`,`stoploss`,`takeprofit`) values (1,0,'\'EURUSD\'',1,'0.10000','1.00000','0.00000','0.00000','0.00000');
    insert  into `trades`(`ID`,`ticket`,`symbol`,`type`,`lots`,`open`,`close`,`stoploss`,`takeprofit`) values (3,1,'\'EURUSD\'',1,'1.00000','2.02000','0.00000','0.00000','0.00000');
    insert  into `trades`(`ID`,`ticket`,`symbol`,`type`,`lots`,`open`,`close`,`stoploss`,`takeprofit`) values (4,3,'\'EURUSD\'',-1,'0.10000','21121.00000','245.00000','1.25000','0.00000');
    
    Files:
    riaanhoogenboezem
    5
    riaanhoogenboezem  
    Thanks so much
    To add comments, please log in or register