DYNAMICS AX X++ CODE TO CONNECT TO SQL DATABASE DSN ODBC CONNECTION LOGIN

How  to connect to an external DB from Dynamics AX using X++

1. Create a DSN

To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC).

Create the DSN on the tier where the X++ code will call the DSN from

2. X++ code

static void TestOdbcJob()

{

    LoginProperty login;

    OdbcConnection con;

    Statement stmt;

    ResultSet rs;

    str strQuery, criteria;

    SqlStatementExecutePermission perm;

    ;

    // Set the information on the ODBC.
    login = new LoginProperty();

    login.setDSN("dsnName");

    login.setDatabase("databaseName");

    //Create a connection to external database.
    con = new OdbcConnection(login);

    if (con)

    {

        strQuery = strfmt("SELECT * from tableName WHERE XXX = ‘%1′ ORDER BY  FIELD1, FIELD2", criteria);

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(strQuery);

        perm.assert();

        //Prepare the sql statement.
        stmt = con.createStatement();

        rs = stmt.executeQuery(strQuery);

       

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (rs.next())

        {

            //It is not possible to get field 2 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print rs.getString(1);

            print rs.getString(2);

        }

        rs.close();

        stmt.close();

    }

    else

    {

        error("Failed to log on to the database through ODBC");

    }

}