How to: Connect to an External Database from X++ Code [AX 2012] [AX 2009]

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 Application Object Server (AOS) computer.

Note
Ongoing maintenance is simpler if the DSN is created on the AOS tier.

X++ Code Example with ODBC

---

The following X++ code example uses ODBC to connect to an external database. The code example assumes that you have already created the DSN in Windows.

X++

// 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.

If you want to do Insert/Update operation through this code then you must use following code.

int      _returnId;

sql = " Your insert / Update Query"

 //Prepare the sql statement.
statement = odbcConnection.createStatement();
_returnId = statement.executeUpdate(sql);

reference :1. Microsoft Documentation MSDN 2. ArtofCreation 3. GalaxyofDynamics