How to Create Connection Using ODBC with X++ in Ax 2012

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.

Queries enable you to ask questions about the data in the Microsoft Dynamics AX database and retrieve information. There are two ways to create queries:

• Use the Queries node in the Application Object Tree (AOT) to create static queries using a graphical interface. For more information about creating queries in the AOT, 
• see : How to create query by using the aot. 
• Use the query system classes to create dynamic queries in code. For more information about creating queries in code, see how to: Create Queries by Using X++.

Static queries are pre-defined queries that can be accessed from the Application Object Tree (AOT). In Microsoft Dynamics AX, static queries are located in the Queries node and contain the following primary sub node: 

• Methods
• Data Sources
• Dependent Objects
• Composite Query

Each Data Sources node has the following sub node:

• Fields
• Ranges
• Data Sources
• Group By
• Order By
• Relations (only present in a child data source) 

Create Job and Create ODBC Connection through X++. For more Info ODBC Connection

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

    myLoginProperty = new LoginProperty();
    myLoginProperty.setServer(SysSQLSystemInfo::construct().getLoginServer());
    myLoginProperty.setDatabase(SysSQLSystemInfo::construct().getloginDatabase());
   
    myLoginProperty.setServer("ExternalServerName");
    myLoginProperty.setDatabase("DatabaseName");
   
    // myLoginProperty.setUsername (“UserID”);
    // myLoginProperty.setPassword (“Password”);
    //_dictTable = new DictTable(tableNum());
    //if(_dictTable != null)
    //{
        odbcConnection = new OdbcConnection(myLoginProperty);
        if (odbcConnection)
        {
            Query=strfmt( "Select * from tablename");
            //Assert permission for executing the sql string.
            sqlpermission = new SqlStatementExecutePermission(Query);
            // perm1=new SqlStatementExecutePermission(sql1);
            sqlpermission.assert();
             // Create new Statement instance
            statement =odbcConnection.createStatement();
            resultSet =statement.executeQuery(Query);
            while(resultSet.next())
            {
                info(strFmt("%1,%2,",resultSet.getString(1),resultSet.getString(2)));
            }
            //statement.close();
            // limit the scope of the assert call
            //CodeAccessPermission::revertAssert();
        }
        else
        {
            error("Failed to log on to the database through ODBC.");
        }
    //}
}

Author : Umesh Patel

Related Posts