Execute SQL from Metatrader

To add comments, please log in or register
jhernandez
66
jhernandez  

All,

I've seen a couple of threads about using databases in Metatrader, but I havent seen anything specific about connecting to a Microsoft SQL Server. So, I thought I'd put together a quick tutorial of how to do this in case anybody wants to do something similar. This process should work for any data source that can be accessed via ADO/ODBC/OLE DB (SQL, Access, Excel, etc...)

The following code snippets are a little technical, and will require some basic coding knowledge.

For this task, I started with the ExpertSample sample C++ project in your Metatrader folder "experts\samples\DLLSample". I didn't want to start from scratch figuring out the right type of COM library to build, so I simply used this sample project.

Once you've opened up the project (you'll need Visual Studio), you're ready to use SQL in 4 easy steps!

1) Add a reference to the appopriate ADO dlls at the top of your ExpertSample.cpp page, like so:

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \

no_namespace rename("EOF", "EndOfFile")

[/PHP]

2) Then you're ready to add the ExecuteScalar method to your code. This function will only return one value from SQL. (Metatrader can only receive basic data types).

MT4_EXPFUNC char * __stdcall ExecuteScalar(char *strSQL)

{

HRESULT hr = S_OK;

char tmpChar[255];

try {

// Define string variables.

_bstr_t strCnn(MY_CONNECTION_STRING); //http://www.connectionstrings.com for more info

::CoInitialize(NULL);

_RecordsetPtr pRstAuthors = NULL;

// Call Create instance to instantiate the Record set

hr = pRstAuthors.CreateInstance(__uuidof(Recordset));

if(FAILED(hr))

{

::CoUninitialize();

return "ERROR: Failed creating record set instance";

}

//Open the Record set for getting records from Author table

try {

pRstAuthors->Open(strSQL,strCnn, adOpenStatic, adLockReadOnly,adCmdText);

} catch (_com_error & ce1) {

::CoUninitialize();

return "ERROR: Unable to open SQL Server";

}

try {

pRstAuthors->MoveFirst();

} catch(_com_error) {

::CoUninitialize();

return ""; //empty data

}

//Loop through the Record set

if (!pRstAuthors->EndOfFile)

{

_variant_t tmpvariant;

//Get the first column value

tmpvariant = pRstAuthors->GetFields()->GetItem((long)0)->GetValue();

strcpy(tmpChar,(_bstr_t)tmpvariant);

}

if (pRstAuthors->State == adStateOpen)

pRstAuthors->Close();

pRstAuthors = NULL;

::CoUninitialize();

}

catch(_com_error & ce)

{

//_bstr_t strError = ce.ErrorMessage;

::CoUninitialize();

return "ERROR: Failed to get data.";

}

return tmpChar;

}

[/PHP]

3) Make sure to add you method name in the ExpertSample.def so that you can call it via Metatrader.

[PHP]

EXPORTS GetIntValue

GetDoubleValue

GetStringValue

GetArrayItemValue

SetArrayItemValue

GetRatesItemValue

SortStringArray

ProcessStringArray

ExecuteScalar

4) Once you add your compiled DLL to the "experts\libraries" folder, you can call your DLL method by adding the following to the top of your MQ4 file:

[PHP]

#import "ExpertSample.dll"

string ExecuteScalar(string strSQL);

#import

That's it! You're ready to start making database calls straight from Metatrader. I'm using this right now to play around with some SELECT/INSERT statements into my SQL Server, trying to play around with some advanced ordering logic.

My next goal: using Sockets from Metatrader to communicate to an external application, preferrably written in C#.

Juan

MegaLeads
81
MegaLeads  

WOW - Thank you! Very Well Put!

TraderGeek
45
TraderGeek  

MQL and Databases

Does anybody know of a way to connect to a database from MT4? I imagine it would need to be an external DLL as it doesn't look like there is a data access laryer built in to MQL but it shouldn't be that hard to put together. But before I do it, I wondered it here is one out there already somewhere. What I had in mind was a Generic ODBC or OLEDB connection type of thing so it could be used with multiple DBMS's.

Coder
14
Coder  

MQL & Databases

Hello TraderGeek,

Yes, you will need an external DLL to do this work for you.

If you have no experience with C++, have a look at Powerbasic.

Powerbasic can create DLL files that can be used in MT4 and

it's syntax is easy to understand.

Coder

kkb
6
kkb  

do a search on mysql all the information is there. There is no need to create a DLL as they already exist.

jhernandez
66
jhernandez  

Here's a post about connecting to a MS SQL Server DB, and other DBs in that ADO family:

https://www.mql5.com/en/forum/176646

TraderGeek
45
TraderGeek  

Excellent! Just what I was looking for.

jhernandez
66
jhernandez  
TraderGeek:
Excellent! Just what I was looking for.

Great, glad that helped.

Juan

PS. Hey, they merged the two threads! Now it looks like I referenced the very same thread I was posting in! Madness!

wan234
4
wan234  

How if its just use msado15.dll directly on MQL dude?...

So no more the extended dll in C, maybe it can give more speed for MQL' execution than using 2 dll files... can you give me the sample of database function on msado15.dll?...just to read the record and execute SQL Command.....

As the logic, MQL can use libmysql.dll directly.....

Tom Workman
30
Tom Workman  

Any more help with this?

I'm trying to connect to a sql express 2005 database. I'm not a c++ programmer. Does anyone know where I can find a working dll that will do this?

Anton Hughes
62
Anton Hughes  

connecting to sql server

Hi, I am trying to follow this tutorial on how to connect to sql server.

In the following directory - C:\Program Files\Common Files\System\ADO\ - I have serveral .dll files.

I have the following:

msado15.dll

msadomd.dll

msador15.dll

msadox.dll

There are few others, but these all have 'ado' in the title.

Can someone tell me which one I should use?

Also, can someone give an example of added the connection string.

And also, this example is extremely basic - in fact it seems to be something like a Northwinds example. Does anyone have examples of getting ticks from MT and storing them in MS Sql Server?

Also, has anyone - or is anyone - successfully using MS SQL server with Metatrader? If so, can you please review the above tutorial and maybe provide some additions, or improvements.

many thanks?

12
To add comments, please log in or register