How To: connect to an external DB from X++

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

We had a MAS200 System before we migrate to AX and lots a transactional data still exists in the old database. in this case, we simply create a DSN and use it on AX X++ code.

A DSN acts as a thin client ot the DB and includes all the authentication information such as username and password.

 

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");

    }

}