Can MQL use Database connection?

freak
248
freak  

Is it possible to do that?

Mykola Bilak
585
Mykola Bilak  

in MQL - not, in MQ4 - yes, with the help of DLL

freak
248
freak  

Do you know more about it? Can you point me out what/where to look for more?

I'm PHP/MySQL programmist. Don't know anything about DLL programming...

I would appreciate any info.

freak
248
freak  

All right! I've just found everything I need here https://www.mql5.com/en/forum/173113

Cheers!!

Mykola Bilak
585
Mykola Bilak  

you don't need to program a DLL, you can use existing.

for example MySQL:

#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);

#import

int mysql;

int init(){

//----

mysql=mysql_init(mysql);

if (mysql!=0) Print("allocated");

string host="localhost";

string user="user";

string password="pwd";

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)," ");

return(0);

}

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

//| expert deinitialization function |

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

int deinit()

{

//----

mysql_close(mysql);

//----

return(0);

}

int start()

{

string query="";

int length=0;

query=StringConcatenate("insert into ticks(margin,freemargin,date,ask,bid,symbol,equity) values(",AccountMargin(),",",AccountFreeMargin(),",\"",TimeToStr(CurTime(),TIME_DATE|TIME_SECONDS),"\",",NormalizeDouble(Ask,4),",",NormalizeDouble(Bid,4),",\"",Symbol(),"\",",AccountEquity(),");");

length=StringLen(query);

mysql_real_query(mysql,query,length);

int myerr=mysql_errno(mysql);

if (myerr>0)Print("error=",myerr);

}

hope this helps.

read also MySQL documentation for DLL usage.

freak
248
freak  

Yes! Thats what I was looking for. libmysql.dll - where can I get it from? Is it avy special version?

NOTE: I've just downloaded MySQL 5.0 pack from mysql.com and grab it from INSTALATION_DIR/bin/libmysql.dll

It works as a dream!

Thank you again!!!

Mykola Bilak
585
Mykola Bilak  

for libmysql.dll look in "lib" subfolder of your mysql installation.

there is one problem though as metatrader can get only scalar datatypes from DLLs.

DDL and DML SQL statements work just fine, but if you need to read something from database with SELECT, it is not so trivial as result is in memory as some structure, which can not be read in MQ4.

to override this I had to output query result to a file and then read it in MQ4:

int GetClientTrades() {

FileDelete(queryFile);

string query="SELECT * INTO OUTFILE \'"+queryPFile+"\' FIELDS TERMINATED BY \';\' LINES TERMINATED BY \'\n\'FROM clients_trades;";

Print(query);

int length=StringLen(query);

mysql_real_query(mysql,query,length);

int myerr=mysql_errno(mysql);

if (myerr>0) Print("error=",myerr);

int handle,res,size,i;

ArrayResize(clientTrades,0);

handle=FileOpen(queryFile,FILE_CSV|FILE_READ);

if(handle<1) {

Print("File " + queryFile + " not found, the last error is ", GetLastError());

return(0);

}

while (!FileIsEnding(handle)) {

size++;

ArrayResize(clientTrades,size);

clientTrades[0]=FileReadNumber(handle);

clientTrades[1]=FileReadNumber(handle);

clientTrades[2]=FileReadNumber(handle);

clientTrades[3]=FileReadNumber(handle);

clientTrades[4]=FileReadNumber(handle);

clientTrades[5]=FileReadNumber(handle);

clientTrades[6]=FileReadNumber(handle);

clientTrades[7]=FileReadNumber(handle);

clientTrades[8]=FileReadNumber(handle);

clientTrades[9]=FileReadNumber(handle);

}

ArrayResize(clientTrades,size-1);

size--;

//if (clientTrades[0]==0.0) ArrayResize(clientTrades,size-1);

FileClose(handle);

return(1);

}

freak
248
freak  

Great trick You are THE BIG MAN!!!

I'll be posting any feedback using it here. So stay tuned

Cheers!

jbwyme
7
jbwyme  

sorry to bring up an older thread but I have successfully connected and inserted data into a mysql database via the EA but I can't seem to figure out your code to SELECT data. I copied the function but it came up with a lot of errors.

jbwyme
7
jbwyme  

okay Ive gotten most of it to work but it can't find the output file since the code deletes it. How does the file get created after it is deleted?

jbwyme
7
jbwyme  

okay for some reason I cannot get the OUTFILE to work... I have the proper permissions set on the MySQL Server but when the code runs for the first time it runs successfully (no errors) and the second time it says that there is already a file by that name. I searched my entire drive for the file name (output.txt) and it didn't find one instance of it. Any ideas?