How to connect to an external DB from Dynamics AX using X++
1. 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
2. X++ code
static void TestOdbcJob()
{
LoginProperty login;
OdbcConnection con;
Statement stmt;
ResultSet rs;
str strQuery, criteria;
SqlStatementExecutePermission perm;
;
// Set the information on the ODBC.
login = new LoginProperty();
login = new LoginProperty();
login.setDSN("dsnName");
login.setDatabase("databaseName");
//Create a connection to external database.
con = new OdbcConnection(login);
con = new OdbcConnection(login);
if (con)
{
strQuery = strfmt("SELECT * from tableName WHERE XXX = ‘%1′ ORDER BY FIELD1, FIELD2", criteria);
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(strQuery);
perm = new SqlStatementExecutePermission(strQuery);
perm.assert();
//Prepare the sql statement.
stmt = con.createStatement();
stmt = con.createStatement();
rs = stmt.executeQuery(strQuery);
//Cause the sql statement to run,
//then loop through each row in the result.
while (rs.next())
//then loop through each row in the result.
while (rs.next())
{
//It is not possible to get field 2 and then 1.
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
print rs.getString(1);
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
print rs.getString(1);
print rs.getString(2);
}
rs.close();
stmt.close();
}
else
{
error("Failed to log on to the database through ODBC");
}
}