Click here to Skip to main content
Click here to Skip to main content

Integrating SQL Server 2008 with Metatrader 5

By , 4 Mar 2010
Rate this:
Please Sign up or sign in to vote.

Introduction

There is only one peer reviewed article concerning SQL integration with Metatrader by Yuriv Zaytsev written almost 8 years back and which was written originally in Russian and automatically translated to English using a translator. The example is very hard to execute, and requires creating a specific database, with specific login credentials and roles without which a successful result cannot be achieved. The article itself can be found here. Zaytsev used the following:

  • Microsoft SQL SERVER 2000 Developer - BASE
  • VISUAL C++ 6.0 SP5 - to create DLL "YZMSSQLExpertSample.dll
  • MDAC 7

Furthermore his example is specific to a database, which you have to create, and connection strings that you have to hardcode. His article was also written when Meta trader 5 was not around, and I had considerable compatibility problems with upgrading from MQL4 to MQL5 using his code.

The article's code samples do not easily upgrade to Visual Studio .NET versions 2003 or 2008, so this is an attempt to remedy all of the above providing a more seamless implementation background.

The DLL has also been modified to accept a connection string as an argument thereby removing any need to constantly rebuild the DLL for connection string changes, and can also accept an entire SQL statement as an input using its ExecuteScalar method.

Background

This article is very relevant to people attempting to integrate the newer Metatrader 5 terminal with SQL Server 2008 to receive and store in a database, commodities, and currencies real time streaming data, to build associating trading systems.

Using the Code

The code has three parts:

Part 1: DLL Generation using the DLL_Creator.zip VS 2008 DLL Project. (No changes are necessary here). However you must ensure that your MSADO15.dll is in the location "C:\Program Files\Common Files\System\ADO\msado15.dll". If it's not, you could either manually place it there or rebuild the DLL project with the correct path.

Part 2: Importing the generated DLL into the Libraries folder. On my computer, the location for this is C:\Users\Administrator\AppData\Roaming\MetaQuotes\Terminal\
5D2A9C702A29311ED87B6AD8A346121B\MQL5\Libraries\
.

But to be sure, on MT5 you can just click on File->Open Data Folder and then Click on Libraries and place the DLL file inside.

Note: Make sure you check the "Allow DLL Imports" Box as shown below:
dll.JPG

Part 3: Create a new MQL5 script with only the following code that references the DLL.

#import "ExpertSample.dll"
   string ExecuteScalar(string strSQL, string strCNN);   
#import  

int OnStart()
{     
   string temp = SymbolInfoDouble("EURUSD",SYMBOL_BID);
   ExecuteScalar("INSERT INTO [indicator_data]([indicator_id] ,[value]) 
	VALUES (2, "+temp +");", "Provider=SQLOLEDB.1;User ID=sa;
	Password=********;Persist Security Info=True;
	Initial Catalog=Finance;Data Source=KMS\\SQL2008" );   
   return(0);
} 

Points of Interest

Well I spent a good couple of days trying to understand why passing strings from MQL5 to C++ only passed the first character. I published this as a bug in mql5's ability to pass variables via dll. I was told that MQL5 handles strings as Wide Characters or w_char. I was receiving them as char* instead of w_char * and that made all the difference. The code is reproduced below:

void p(char *s)
{
    MessageBox(NULL, s,(LPCSTR)L"no error",MB_ICONWARNING | 
	MB_CANCELTRYCONTINUE | MB_DEFBUTTON2 );
}

MT4_EXPFUNC char * __stdcall ExecuteScalar(wchar_t *wstr, wchar_t *wcnn)
  {              
    char* sql = new char[wcslen(wstr) + 1];
    wcstombs( sql, wstr, wcslen(wstr) );
    sql[wcslen(wstr)] = '\0';
    //p(sql);
    p(sql);
    char *cnn = new char[wcslen(wcnn) + 1];
    wcstombs(cnn, wcnn, wcslen(wcnn) );
    cnn[wcslen(wcnn)] = '\0';
    //p(cnn);

    HRESULT hr = S_OK;
    char tmpChar[255];
    try {
        // Define string variables.
        
        //_bstr_t strCnn("Provider=SQLOLEDB.1;User ID=sa;
        //Password=Admin1234;Persist Security Info=True;
        //Initial Catalog=Finance;Data Source=KMS\\SQL2008");
        _bstr_t strCnn(cnn);

        ::CoInitialize(NULL);
        _RecordsetPtr pRstAuthors = NULL;
        // Call Create instance to instantiate the Record set
        hr = pRstAuthors.CreateInstance(__uuidof(Recordset));
        if(FAILED(hr))
        {
            ::CoUninitialize();
            p("ERROR: Failed creating record set instance");
            return "ERROR: Failed creating record set instance";
        }

        //Open the Record set for getting records from Author table
        try {
            pRstAuthors->Open(sql,strCnn, adOpenStatic,     adLockReadOnly,adCmdText);
            //int msgboxID2 = MessageBox(NULL,(LPCSTR)L"no errors",
            //(LPCSTR)L"no error",MB_ICONWARNING | 
            //MB_CANCELTRYCONTINUE | MB_DEFBUTTON2 );
        } catch (_com_error & ce1) {
            
            ::CoUninitialize();
            p("Unable to Open SQL Server");
            //int msgboxID = MessageBox(NULL,(LPCSTR)
            //"Resource not available\nDo you want to try again?",
            //(LPCSTR)L"SQL ERror",MB_ICONWARNING | MB_CANCELTRYCONTINUE | 
            //MB_DEFBUTTON2 );
            return "ERROR: Unable to open SQL Server";
        }
        p("SQL Opened");

        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();
        p("ERROR: Failed to get data.");
        return "ERROR: Failed to get data.";
    }

    return tmpChar;
  }  

Conclusion

To get this guy running, all you need is a database to test on and a MQL 5 platform available at MetaTrader5.

You will also need VS2008 the express versions of which are free, in case you want to change the DLL file.

This is based on the smash and grab concept, and implementing this should be easy. Good luck and feel free to post comments and I will do my best to reply.

History

  • 4th March, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Kanhar Munshi
Software Developer (Senior) New York City Department of Health and Environment
United States United States
Kanhar Munshi currently works as a consultant for the New York City Department of Health and Environmental Surveillance building, deploying and maintaining ASP.NET web applications, while also serving in the role of an assistant database administrator.
 
He is currently involved in the building of a Large Scale implementation of a Normalized Data Store, Data Warehouse for the Department of Health.
 
His interests, include C#, running, table tennis, and trekking.
 
Kanhar Munshi  Kanhar Munshi  Kanhar Munshi 
Kanhar Munshi Kanhar Munshi  
Kanhar Munshi Kanhar Munshi 
kanhar munshi

Comments and Discussions

 
GeneralMy vote of 3 PinmemberDmitri Nesteruk6-Feb-11 2:09 
General64 bit version PinmemberJohn Mcgiles20-Nov-10 17:57 
GeneralRe: 64 bit version Pinmemberforik2111-Feb-11 3:32 
GeneralError Message PinmemberAamir Ghanghro22-Jun-10 20:31 
GeneralRe: Error Message PinmemberKanhar Munshi29-Jul-10 10:59 
QuestionWhere to put DLL File ? PinmemberAamir Ghanghro22-Jun-10 20:29 
GeneralMy vote of 1 PinmemberJim Crafton8-Mar-10 6:18 
GeneralRe: My vote of 1 PinmemberKanhar Munshi29-Jul-10 10:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 4 Mar 2010
Article Copyright 2010 by Kanhar Munshi
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid