Retrieve External Data using ODBC connection in Dynamics AX

When I’m flying in Dynamics AX world, I found most of the AX users are struggling to handle data from out source (External source such as MS-Access Database, Oracle Database, etc....).

In below I’m going to explain, how we can connect to the External Database using ODBC(Open Database Connectivity) Connection & retrieve data from that DB.

First we have to create a ODBC Datasource which has connected our Database. In my Example i have used DS as my Datasource name.

server static void GetShippingTrans(CustTable custTable)
{
     // Variable Declaration
LoginProperty lP;
OdbcConnection con;
Statement st;
ResultSet rS;
Str sqlS;
str databaseName = "Test"; 
MyShippingTrans trans;
SqlStatementExecutePermission sqlPermission;
;
// Initialize new Login Property
lP= new LoginProperty();

// Set DSN to the Login Property
lP.setDSN(DS);

//Set the correct DataBase to retrieve Data (May have more than one Database in that DSN)
lP.setDatabase(databaseName);

try
{
 //Create ODBC Connection by using Login Property

con= new OdbcConnection(lP);
st= con.createStatement();

//SQL Statement to select the Records from the Table

sqlS = StrFmt("SELECT %1, %2, %3 FROM Customer WHERE " +
"Customer.id = '%4',"Name", "DOB", "Height", custTable.CustId );

//Create a permission to execute SQL Statement

sqlPermission = new SQLStatementExecutePermission(sqlS);
sqlPermission.assert();

//Execute SQL Statement & retrieve the result set

            rS = st.executeQuery(sqlS);

while (rs.next())
{

            //Manipulate retrieved data.......

                     info(strFmt("Cust Name :%1    DOB : %2    Hieght : %3 "

                     ,rs.getString(1), rs.getDate(2), rs.getReal(3)));

}
CodeAccessPermission::revertAssert();

}
catch (Exception::Error)
{

            error(strFmt("Error accessing Customer database"));

}
}


I hope, now everyone has an idea about retrieving data from external data base & manipulating those fetched data.