ConnectingToExternalDatabase.xpp

//Author:Mafigu Huggins
//Tel:    +263 782 326 160
//Email:  mafiguhuggins@gmail.com

//If we need to interface any external database with Dynamics AX, we can achieve this task by using ADO and its AX available classes:
//CCADOConnection, CCADORecordSet, CCADOFields, CCADOField and CCADOCommand. Here an example:
class ConnectToExternalDatabase
{

    static void ADOTestJob(Args _args)
    {
        CCADOConnection ccConnection;
        CCADOCommand ccCommand;
        CCADORecordSet ccRecordset;
        CCADOFields ccFields;
        str st;
        str data1;
        int data2;
        ;

        ccConnection = new CCADOConnection();

        // Setting the connection string
        ccConnection.connectionString(StrFmt('Provider=SQLOLEDB.1;Persist Security Info=False;User ID=%3;Password=%4;Initial Catalog=%2;Data Source=%1'
        , 'servername' // Server's IP or name
        , 'database' // Database or catalog
        , 'user' // Username
        , 'pwd' // Password
        ));

        // Open the connection
        ccConnection.open();

        // Preparing the query
        st = "SELECT * FROM mytable";

        // Recordset object creation
        ccRecordset = new CCADORecordSet();

        // Executing the query
        ccRecordset.open( st, ccConnection );

        // Reading data
        while (!ccRecordset.EOF())
        {
            ccFields = ccRecordset.fields();

            // We can access fields either by name or by Index
            data1 = ccFields.itemName("FIELD1").value();
            data2 = ccFields.itemIdx(1).value();

            info(strfmt("Data %1, %2", data1, data2));

            // Read next record
            ccRecordset.moveNext();
        }

        // Closing the connection
        ccRecordset.close();
        ccConnection.close();
        }

        //If we need to execute something (an UPDATE, DELETE, etc.), we can use the CCADOCommand:


        void ExecuteSQLExt(str sql)
        {
            // Creating the ADO Command object
            ccCommand = new CCADOCommand();

            // Associate it with an existing opened connection
            ccCommand.activeConnection(ccConnection);

            // Executing the command
            ccCommand.commandText(SQL);

            ccCommand.execute();
        }
}