Connection from AX to an External Database

There are a few options. We can create an ODBC connection on a local machine or just to connect directly without creating ODBC record.

For exmaple, we want to check whether some records exist in an external table. We should create a OdbcConnection with appropriate LoginProperty and permit to execute a SQL statement by means of SqlStatementExecutePermission class. 

server boolean checkExternalDB()
    {
    //connection parameters
        #define.ExternalTableName("CustTable")
        #define.ExternalFieldName("AccountNum")
        #define.ExternalSQLServerName("SRVAXSQL2005")
        #define.ExternalSQLDBName("DAXdb401_Standard_DEV")
        LoginProperty LP = new LoginProperty();
        OdbcConnection myConnection;
        SqlStatementExecutePermission permission;
        Statement myStatement;
        str sqlStmt = "";
        ResultSet myResult;
        boolean ret = true;
        ;

        LP.setServer(#ExternalSQLServerName);
        LP.setDatabase(#ExternalSQLDBName);
        try
        {
            myConnection = new OdbcConnection(LP);
        }
        catch
        {
            info("Check connection parameters. "+funcName());
            ret = checkFailed(strfmt("External DB Connection error in: %1"), #ExternalSQLDBName);
        }

        myStatement = myConnection.createStatement();
    //anything you want to get from the external table
        sqlStmt = "SELECT count (RecId) FROM "+#ExternalTableName+ " where "+#ExternalFieldName + " = '" + this.AccountNum+"'";

        permission = new SqlStatementExecutePermission(sqlStmt);
        permission.assert();

        myResult = myStatement.executeQuery(sqlStmt);
        while (MyResult.next())
        {
            if (MyResult.getInt(1) > 0)
            {
                //yes, records exist in the external table
                ret = checkFailed(strfmt("@LBA53"+"\n"+funcName(), strfmt("[%1].[%2].[%3]", #ExternalSQLServerName, #ExternalSQLDBName, #ExternalTableName)));
                break;
            }
        }

        CodeAccessPermission::revertAssert();

        return ret;
    }