How to: Connect to an External Database from X++ Code (ODBC)

// X++, Main method in a class.

static public void Main(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("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //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())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

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

32 Bit and 64 Bit Windows Operating System

The preceding code example can run on either the client tier or the server tier. The following table shows how the operating system architecture affects the choice of tier.

	32 bit Windows	64 bit Windows
Client tier (MorphX)	Runnable.	Not runnable.
Server tier (AOS)	Runnable.	Runnable. Consider adding the server keyword to the declaration of the Main method. https://msdn.microsoft.com/EN-US/library/ee677510.aspx related : https://msdn.microsoft.com/EN-US/library/jj129502.aspx