How to create DSN and connect to an External Database from X++

Go to Administrative Tools  >>  Data Sources (ODBC)

In the tab User DSN >> Add >> Choose SQL Server Native Client

Config DSN to your database and server

Sample Database

X++ Code

static void DSNConnectivityTest(Args _args)

{

    LoginProperty                   loginProperty;

    OdbcConnection                  odbcConnection;

    Statement                       statement;

    ResultSet                       resultSet;

    str                             sql, criteria;

    SqlStatementExecutePermission   perm;

    // Set the information on the ODBC.

    loginProperty = new LoginProperty();

    loginProperty.setDSN("TestDatabase");

    loginProperty.setDatabase("TestDB");

    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)

    {

        sql = "SELECT * FROM Persons;";

        perm = new SqlStatementExecutePermission(sql);

        perm.assert();

        //Prepare the sql statement.

        statement = odbcConnection.createStatement();

        resultSet = statement.executeQuery(sql);

        while (resultSet.next())

        {

            //Always get fields in numerical order, such as 1 then 2 the 3

            info(strFmt('Person Id: %1 -> Person Name : %2', strLRTrim(resultSet.getString(1)), resultSet.getString(2)));

        }

        //Close the connection.

        resultSet.close();

        statement.close();

    }

    else

    {

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

    }

}