create external ODBC connection through x++ in Dyanmics ax

Description:-

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.

Create job and pass query.

static void ODBCConnection(Args _args)
{
    LoginProperty   myLoginProperty;
    DictTable  _dictTable;
    ODBCConnection  odbcConnection;
    Resultset       resultSet;
    Statement       statement;
    SqlStatementExecutePermission   sqlpermission;
    str Query;

    myLoginProperty = new LoginProperty();

    myLoginProperty.setServer("ExternalServerName");
    myLoginProperty.setDatabase("ExternalDatabaseName");
        odbcConnection = new OdbcConnection(myLoginProperty);
        if (odbcConnection)
        {
            Query=strfmt( "Select Firstname,LastName from Employees");
            //Assert permission for executing the sql string.
            sqlpermission = new SqlStatementExecutePermission(Query);
            sqlpermission.assert();
             // Create new Statement instance
            statement =odbcConnection.createStatement();
            resultSet =statement.executeQuery(Query);
            while(resultSet.next())
            {
                info(strFmt("FirstName : %1,LastName : %2",resultSet.getString(1),resultSet.getString(2)));
            }
        }
        else
        {
            error("Failed to log on to the database through ODBC.");
        }
}

Output:-

Example 2:-

static void ODBCRead(Args _args)
{
    LoginProperty LP = new LoginProperty();
    OdbcConnection myConnection;
    Statement myStatement;
    ResultSet myResult;
    ;
    LP.setServer("ExternalServerName");
    LP.setDatabase("ExternalDatabaseNmae");
    myConnection = new OdbcConnection(LP);
    myStatement = myConnection.createStatement();
    myResult = myStatement.executeQuery("SELECT * FROM TableName");
    while (myResult.next())
    {
        info(strfmt("%1 %2", myResult.getString(1), myResult.getString(2)));
    }
}

Author : Umesh Patel

Related Posts