How to: Connect to an External Database from X++ Code

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This topic describes how to connect directly to an external database from Microsoft Dynamics AX X++ code. This is achieved by using the Open Database Connection (ODBC) protocol through the OdbcConnection class. This is useful when an external system that is implemented without Microsoft Dynamics AX has important information stored in its database.

Before you can use ODBC, you must create a Data Source Name (DSN) in the Windows operating system. A DSN acts as a thin client to the database and includes all the authentication information such as username and password.

Warning

If you use ODBC to directly access data that is stored in an installation of Microsoft Dynamics AX, you must ensure that your code contains the proper restrictions to partition and company. For more information, see How to: Include a Filter for Partition in Direct Transact-SQL.

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++, 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.

See also

Queries in the AOT for Data Access

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.