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:
PHP Code:
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
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).
PHP Code:
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;
}
3) Make sure to add you method name in the ExpertSample.def so that you can call it via Metatrader.
PHP Code:
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 Code:
#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