Execute SQL from Metatrader

jhernandez · #1 (**permalink**) 04-02-2007, 04:55 PM

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

MegaLeads · #2 (**permalink**) 04-04-2007, 09:40 PM

WOW - Thank you! Very Well Put!

TraderGeek · #3 (**permalink**) 04-23-2007, 09:48 PM

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 · #4 (**permalink**) 04-24-2007, 12:07 AM

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 · #5 (**permalink**) 04-24-2007, 09:42 AM

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

jhernandez · #6 (**permalink**) 04-25-2007, 05:48 PM

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

Execute SQL from Metatrader

TraderGeek · #7 (**permalink**) 04-25-2007, 06:20 PM

Excellent! Just what I was looking for.

jhernandez · #8 (**permalink**) 04-26-2007, 08:44 PM

Quote:

Originally Posted by 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 · #9 (**permalink**) 11-09-2007, 07:26 AM

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.....

tworkman · #10 (**permalink**) 11-13-2007, 05:40 AM

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?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mac? MetaTrader	dazminder	General Discussion	2	11-23-2006 06:54 PM
How do I make my EA execute?	Aaragorn	Questions	4	06-14-2006 04:37 AM
How can an EA Execute Script in the....	babarmughal	Expert Advisors - Metatrader 4	2	05-16-2006 10:15 PM

Register in Forex TSD!
Trading Systems Leaders in this forum (automated trading systems) are winning more than 3000 pips in a month (30000$ investing one lot every time). Click here to register and get more information