Accessing External Database from X++

1.       Create an ODBC connection through Administrative tools >> Data Sources (ODBC)
>> Create a new ‘SQL Server’ connection
>> Test the connection
2.       Use the following code to access the external database:
server static void GetShippingTrans(Args    _args)
{
    LoginProperty                   loginProperty;
    OdbcConnection                  connection;
    Statement                       statement;
    ResultSet                       results;
    Str                             sqlStatement;
    str                             dataSourceName = "TestExternalDB_XXX";
    str                             databaseName = "payroll_XXX_dev";
    SqlStatementExecutePermission   sqlPermission;
    ;
   
    loginProperty = new LoginProperty();
    loginProperty.setDSN(dataSourceName);
    LoginProperty.setDatabase(databaseName);

    try
    {

        connection = new OdbcConnection(loginProperty);
        statement = connection.createStatement();
        sqlStatement = StrFmt("SELECT * FROM EmployeeCTCDetails");
        sqlPermission = new SQLStatementExecutePermission(sqlStatement);
        sqlPermission.assert();
        results = statement.executeQuery(sQLStatement);
        while (results.next())
        {
            setprefix(strfmt("Accessing the external database"));
            print results.getInt(1),'-',results.getString(2),'-',results.getString(3),'-',results.getReal(4);
        }
        CodeAccessPermission::revertAssert();
    }
    catch (Exception::Error)
    {
        error(strFmt("Error accessing external database."));
    }
    pause;
}