Connect to an external DB from AX

Connect to an external DB from AX 

This is achieved by using the ODBC protocal through the OdbcConnection class.


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. This will be either on the client computer or on the AOS computer.
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);
        }

        //Close the connection.
        rs.close();
        stmt.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC");
    }
}