Access external database using ODBC

Find below some code-examples, how to Access external databases from Dynamics AX.

The examples are based on the following entry in MSDN:
How to: Connect to an External Database from X++ Code [AX 2012]
 

Read access (SELECT)

// 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("ExternalDB_32bit");
    loginProperty.setDatabase("ExternalDatabaseName");

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

    if (odbcConnection)
    {
        sql = "SELECT * FROM items;";

        //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 strFmt("%1 - %2", strRTrim(resultSet.getString(1)), strRTrim(resultSet.getString(2)));
        }

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

Write access (INSERT)

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("ExternalDB_32bit");
    loginProperty.setDatabase("ExternalDatabaseName");

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

    if (odbcConnection)
    {
        sql = "INSERT INTO items VALUES ('7000', 'Item 7000');";

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

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

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

Write access (UPDATE)

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("ExternalDB_32bit");
    loginProperty.setDatabase("ExternalDatabaseName");

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

    if (odbcConnection)
    {
        sql = "UPDATE items SET itemname = 'Name of ' + itemId;";

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

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

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

When performing these code on the client, a 32-bit DSN is required. Running the code on the server - for example via the SysOperation framework - and it is installed on a server using a 64 bit operating system a 64-bit DSN must be configured on the AOS-Server.

Following error Messages could indicate that your are using the wrong DSN:

ODBC-Vorgang ist fehlgeschlagen. Anmeldung bei der Datenbank nicht möglich.
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

It is possible to change the access like in following code, to ensure that we are always using the corresponding DSN:

// Set the information on the ODBC.
loginProperty = new LoginProperty();
if(Global::isRunningOnServer())
{
    loginProperty.setDSN("ExternalDB_64bit");
}
else
{
    loginProperty.setDSN("ExternalDB_32bit");
}
loginProperty.setDatabase("ExternalDatabaseName");

Note:

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%WindowsSysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%WindowsSystem32 Folder.