In this blog post, I will show you how to configure DSN to connect an external DB into AX 2012 using x++.
To configure DSN, you need to follow these steps:
1. Go to Administrative Tools > Data Sources (ODBC) on your Windows system.
2. Create a new DSN on the tier where your x++ code will run. This can be either on the client or on the AOS computer.
3. Choose the type of database you want to connect to, such as SQL Server, Oracle, MySQL, etc.
4. Enter the name of the DSN, the name of the database, and the authentication information (such as username and password) for accessing the database.
5. Test the connection and save the DSN.
Once you have created the DSN, you can use it in your x++ code to connect to the external database and execute queries or stored procedures. Here is an example of how to do that I am using SQL Server as an example:
static void ExternalDBConnection(Args _args)
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql;
str strConnectionString;
SqlStatementExecutePermission perm;
str dsn = "DSNName";
str dsnUser = "DBUserName";
str dsnUSerPwd = "DBPassword";
str myDatabase = "DatabaseName";
Counter counter = 0;
strConnectionString = strfmt("DSN=%1;UID=%2;PWD=%3",dsn, dsnUser,dsnUSerPwd);
loginProperty = new LoginProperty();
loginProperty.setDatabase(myDatabase);
loginProperty.setOther(strConnectionString);
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = "SELECT * FROM customers;";
//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())
{
counter++;
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
Info(strFmt("%1 - %2 - %3", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)));
}
Info(strFmt("%1", counter));
//Close the connection.
resultSet.close();
statement.close();
}
else
{
error("Failed to log on to the database through ODBC.");
}
}