You’re better off doing this 100% in SQL. If you want to do something like this in AX, you’re going to have many issues. Here is some sample code I just wrote. It doesn’t handle arrays (like dimension[1]), and there are a bunch of other issues such as, what if the first instance has more fields. You can use “typeOf()” and all sorts of stuff. This is a very difficult task to accomplish. Here’s my code to get you 80% started:
static void JobCompareTableToSQL(Args _args)
{
SysDictTable dictTable = new SysDictTable(tablenum(CustTable));
SysDictField dictField;
fieldId fieldId, extFieldId;
int i, j;
CustTable custTable;
str sqlStatement;
SqlStatementExecutePermission sqlStatementExecutePermission;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str retVal;
;
loginProperty = new LoginProperty();
loginProperty.setServer(‘YourSQLServer’);
loginProperty.setDatabase(‘YourSQLDB’);
odbcConnection = new OdbcConnection(loginProperty);
statement = odbcConnection.createStatement();
sqlStatement = ‘SELECT top 2 * FROM CUSTTABLE’;
new SqlStatementExecutePermission(sqlStatement).assert();
resultSet = statement.executeQuery(sqlStatement);
while (resultSet.next())
{
select firstonly custTable;
for (i=1; i<=dictTable.fieldCnt(); i++)
{
fieldId = dictTable.fieldCnt2Id(i);
dictField = new SysDictField(custTable.TableId, fieldId);
if (!dictField.isSystem() && dictField.arraySize() == 1)
{
retVal = resultSet.getString(i);
if (strfmt("%1", custTable.(fieldId)) == strfmt("%1", retVal))
info(strfmt(“Yes[%4]: %1: %2 - %3”, fieldid2name(custTable.TableId, fieldId), custTable.(fieldId), retVal, fieldId));
else
info(strfmt(“No[%4]: %1: %2 - %3”, fieldid2name(custTable.TableId, fieldId), custTable.(fieldId), retVal, fieldId));
}
}
}
}