I have a requirement to connect External DB from X++ in D365. In the previous version, AX 2012, I was using the below code. Now when I use the same code, the following error throw:
Note:
I have created a system DSN in the AOS server and using that DSN I am trying to connect.
This is a DEMO environment where I am doing some development. The environment was downloaded from LCS.
static server public str EmployeeIntegrationSQLConnect(String50 _DSNName, String50 _DB, str sql)
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
ENSEmployeeIntegrationDetailsTmp ENSEmployeeIntegrationDetailsTmp;
Statement statement;
ResultSet resultSet;
str criteria,myConnectionString;
SqlStatementExecutePermission perm;
string255 result;
str 100 _date;
int rowcnt;
// Set the information on the ODBC.
loginProperty = new LoginProperty();
loginProperty.setDSN("DB");
loginProperty.setDatabase("HR");
//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(sql);
perm.assert();
//Prepare the sql statement.
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
delete_from ENSEmployeeIntegrationDetailsTmp;
//Cause the sql statement to run,
//then loop through each row in the result.
rowcnt = 0;
while (resultSet.next())
{
ttsBegin;
ENSEmployeeIntegrationDetailsTmp.Campus = resultSet.getString(1);
ENSEmployeeIntegrationDetailsTmp.PersonnleNUmber = resultSet.getString(2);
ENSEmployeeIntegrationDetailsTmp.FirstName = resultSet.getString(3);
ENSEmployeeIntegrationDetailsTmp.MiddleName = resultSet.getString(4);
ENSEmployeeIntegrationDetailsTmp.LastName = resultSet.getString(5);
ENSEmployeeIntegrationDetailsTmp.Email = resultSet.getString(6);
_date= resultSet.getString(7);
ENSEmployeeIntegrationDetailsTmp.JoinDate = str2Date( _date,123);
ENSEmployeeIntegrationDetailsTmp.Category = resultSet.getString(8);
ENSEmployeeIntegrationDetailsTmp.Employee = ENSEmployeeIntegrationDetailsTmp.PersonnleNUmber;
ENSEmployeeIntegrationDetailsTmp.School = resultSet.getString(9);
ENSEmployeeIntegrationDetailsTmp.Grades = resultSet.getString(10);
ENSEmployeeIntegrationDetailsTmp.LastworkingDate = str2Date(resultSet.getString(11),321);
ENSEmployeeIntegrationDetailsTmp.insert();
ttscommit;
rowcnt ++;
}
info(strFmt("%1 rows inserted",rowcnt));
//Close the connection.
resultSet.close();
statement.close();
return result;
}
else
{
error("Failed to log on to the database through ODBC.");
}
return "";
}
