AX2012 / D365FnO – HOW TO CONFIGURE DSN TO CONNECT EXTERNAL DATABASE INTO AX 2012 USING X++

D365fno-PostImage

In this blog post, I will show you how to configure DSN to connect an external DB into AX 2012 using x++.

To configure DSN, you need to follow these steps:
 
1. Go to Administrative Tools > Data Sources (ODBC) on your Windows system.
2. Create a new DSN on the tier where your x++ code will run. This can be either on the client or on the AOS computer.
3. Choose the type of database you want to connect to, such as SQL Server, Oracle, MySQL, etc.
4. Enter the name of the DSN, the name of the database, and the authentication information (such as username and password) for accessing the database.
5. Test the connection and save the DSN.
 
Once you have created the DSN, you can use it in your x++ code to connect to the external database and execute queries or stored procedures. Here is an example of how to do that I am using SQL Server as an example:
static void ExternalDBConnection(Args _args)
{
    LoginProperty                   loginProperty;
    OdbcConnection                  odbcConnection;
    Statement                       statement;
    ResultSet                       resultSet;
    str                             sql;
    str                             strConnectionString;
    SqlStatementExecutePermission   perm;
    str                             dsn         = "DSNName";
    str                             dsnUser     = "DBUserName";
    str                             dsnUSerPwd  = "DBPassword";
    str                             myDatabase  = "DatabaseName";
    Counter                         counter = 0;

    strConnectionString = strfmt("DSN=%1;UID=%2;PWD=%3",dsn, dsnUser,dsnUSerPwd);
    loginProperty       = new LoginProperty();
    
    loginProperty.setDatabase(myDatabase);
    loginProperty.setOther(strConnectionString);
    
    odbcConnection      = new OdbcConnection(loginProperty);
        
    if (odbcConnection)
    {
        sql = "SELECT * FROM customers;";

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

            //Prepare the sql statement.
            statement = odbcConnection.createStatement();
            resultSet = statement.executeQuery(sql);

            //Cause the sql statement to run,
            //then loop through each row in the result.
            while (resultSet.next())
            {
                counter++;
                //Always get fields in numerical order, such as 1 then 2 the 3 etc.
                Info(strFmt("%1 - %2 - %3", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)));
                
            }
            Info(strFmt("%1", counter));
                
            //Close the connection.
            resultSet.close();
            statement.close();
        }
        else
        {
            error("Failed to log on to the database through ODBC.");
        }
}
This site uses cookies to offer you a better browsing experience. By browsing this website, you agree to our use of cookies.