MySQL Read from Database

[Deleted] 2008.11.06 19:56

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!!

[Deleted] 2008.11.07 11:46 #1

No idea??

Alexander 2008.11.07 17:05 #2

Try EA

'TicksInMySQL'

It helps you

[Deleted] 2008.11.07 21:52 #3

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!

[Deleted] 2008.11.23 20:02 #4

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.

run create.sql to make datebase "mt4" and sample table "trades", insert some data into table
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:

mysqlread.zip 2 kb

Principles of database operations SQLite database Generating included code -

Christiaan Hoogenboezem 2019.03.15 19:02 #5

Thanks so much

New comment