//+------------------------------------------------------------------+ //| MySQL_logger_1.mq4 | //| Copyright © 2006, MetaQuotes Software Corp. | //| http://www.metaquotes.net | //+------------------------------------------------------------------+ #property copyright "Copyright © 2010, CAJAX!" #property link "http://cajax.net" //enter your connection details here string host = "localhost"; string user = "root"; string password = ""; string DB = "mt4"; int clientflag = 0; int port = 3306; string socket = ""; //mysql related stuff //requires libmysql. It can by found in any mysql related software, or from mysql distribution. //don't forget to find STABLE libmysql to not crash whole MT someday //DLL calls should be allowed to run #import "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); //minimal interval between writing balance details to database in seconds. 0 - write always extern int balance_write_min_interval=0; // if true, will write balance details only if changed some value extern bool balance_write_only_changes=true; // "0" and "true" combination stores changed as soon as possible without writing duplicates //when true all ticks are stored. Set true only for one advisor per symbol to prevent writing duplicates. extern bool write_ticks=true; //when true writes details of previous bar on given symbol and timeframe extern bool write_bars=true; #import int mysql; datetime Prev_Time=0;//time of previous bar time //+------------------------------------------------------------------+ //| expert initialization function | //+------------------------------------------------------------------+ int init(){ mysql = mysql_init(mysql); if(mysql != 0) Print("allocated"); //enter here details to connect to your database. 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), " "); //using Unicode.Though at present it doesn't matter string query="SET NAMES 'UTF-8'"; mysql_real_query(mysql, query, StringLen(query)); //automatically creating (if it doesn't exists) table for given currency . query=StringConcatenate("CREATE TABLE IF NOT EXISTS `",tablename_ticks(),"` (", "`ID` bigint(15) NOT NULL auto_increment, ", "`DATE_TIME` timestamp NULL default CURRENT_TIMESTAMP, ", "`ASK` double(15,5) NOT NULL, ", "`BID` double(15,5) NOT NULL, ", "PRIMARY KEY (`ID`)", ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;"); mysql_real_query(mysql, query, StringLen(query)); int myerr = mysql_errno(mysql); if(myerr > 0) Print("error=",myerr," Query: ",query); else Print (StringConcatenate("Writing ticks to ",tablename_ticks())); //automatically creating (if it doesn't exists) table for given currency and timeframe . query=StringConcatenate("CREATE TABLE IF NOT EXISTS `",tablename(),"` (", "`ID` bigint(15) NOT NULL auto_increment,", "`DATE_TIME` timestamp NULL default CURRENT_TIMESTAMP, ", "`OPEN` double(15,5) NOT NULL, ", "`MAX` double(15,5) NOT NULL, ", "`MIN` double(15,5) NOT NULL, ", "`CLOSE` double(15,5) NOT NULL, ", "`VOLUME` double(15,5) NOT NULL, ", "PRIMARY KEY (`ID`)", ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;"); mysql_real_query(mysql, query, StringLen(query)); myerr = mysql_errno(mysql); if(myerr > 0) Print("error=",myerr," Query: ",query); else Print (StringConcatenate("Writing bars to ",tablename())); //automatically creating database to store balance state query=StringConcatenate( "CREATE TABLE IF NOT EXISTS `balance` (", "`ID` bigint(20) unsigned NOT NULL auto_increment, ", "`DATE_TIME` datetime NOT NULL, ", "`BALANCE` float(15,5) NOT NULL, ", "`CREDIT` float(15,5) NOT NULL, ", "`MARGIN` float(15,5) NOT NULL, ", "`FREEMARGIN` float(15,5) NOT NULL, ", "`EQUITY` float(15,5) NOT NULL, ", "`PROFIT` float(15,5) NOT NULL, ", "`OPEN` int(11) NOT NULL, ", "PRIMARY KEY (`ID`)", ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;"); mysql_real_query(mysql, query, StringLen(query)); return(0); } //+------------------------------------------------------------------+ //| expert deinitialization function | //+------------------------------------------------------------------+ int deinit() { mysql_close(mysql); //---- return(0); } //+------------------------------------------------------------------+ //| expert start function | //+------------------------------------------------------------------+ int start(){ if(write_ticks==true)write_tick(); if(write_bars&&Prev_Time!=Time[0]) write_bar(); Prev_Time=Time[0]; writebalance(); } //+------------------------------------------------------------------+ //generating table name for given symbol and time period string tablename() { return (StringConcatenate(Symbol(),"_",Period())); } //generating table name for given symbol string tablename_ticks() { return (StringConcatenate(Symbol(),"_ticks")); } //writing balance details void writebalance() { //checking write interval int lastwrite=0; if(GlobalVariableCheck("cajax_mysqllog_bw")) lastwrite=GlobalVariableGet("cajax_mysqllog_bw"); if(CurTime()-balance_write_min_interval>=lastwrite){ //now check, if there is "always write" option cheked or some changes bool do_write=false; if(balance_write_only_changes==false)do_write=true; double balance=NormalizeDouble(AccountBalance(),Digits); double credit=NormalizeDouble(AccountCredit(),Digits); double margin=NormalizeDouble(AccountMargin(),Digits); double freemargin=NormalizeDouble(AccountFreeMargin(),Digits); double equity=NormalizeDouble(AccountEquity(),Digits); double profit=NormalizeDouble(AccountProfit(),Digits); //podschet otkrytyh lotov int j,open=0,orders=OrdersTotal(); for(int i=0;i 0) Print("error=",myerr); }else{ //didn't wrote. Nothing to do here } //storing current values to global variables GlobalVariableSet("balance_mysqllog_bh_balance",balance); GlobalVariableSet("balance_mysqllog_bh_credit",credit); GlobalVariableSet("balance_mysqllog_bh_margin",margin); GlobalVariableSet("balance_mysqllog_bh_freemargin",freemargin); GlobalVariableSet("balance_mysqllog_bh_equity",equity); GlobalVariableSet("balance_mysqllog_bh_profit",profit); GlobalVariableSet("balance_mysqllog_bh_open",open); GlobalVariableSet("balance_write_min_interval",CurTime()); } } //writing open,min,max,close, volume of previous bar on opening current void write_bar() { string query = ""; int length = 0; query = StringConcatenate("INSERT INTO `",tablename(),"`(`ID`,`date_time`,`open`,`max`,`min`,`close`,`volume`) VALUES('','", TimeToStr(CurTime(), TIME_DATE|TIME_SECONDS), "',", NormalizeDouble(Open[1], Digits), ",", NormalizeDouble(Low[1], Digits), ",", NormalizeDouble(High[1], Digits), ",", NormalizeDouble(Close[1], Digits), ",", NormalizeDouble(Volume[1], Digits),");"); length = StringLen(query); mysql_real_query(mysql, query, length); int myerr = mysql_errno(mysql); if(myerr > 0) Print("error=",myerr," Query: ",query); } //function to store all ticks. should be used only for one advisor per currency pair to prevent duplicates in DB void write_tick() { string query = ""; int length = 0; query = StringConcatenate("INSERT INTO `",tablename_ticks(),"`(`ID`,`date_time`,`ask`,`bid`) VALUES('','", TimeToStr(CurTime(), TIME_DATE|TIME_SECONDS), "',", NormalizeDouble(Ask, Digits), ",", NormalizeDouble(Bid, Digits),");"); length = StringLen(query); mysql_real_query(mysql, query, length); int myerr = mysql_errno(mysql); if(myerr > 0) Print("error=",myerr," Query: ",query); }