getting table buffer from AX destination DB using ODBC connection using X++

hi all,

As one of my requirement i have to compare the data of 2 same AX tables(say cust table) that are on different instances (called as source and destination). I have got the table buffer of the custtable from source using the follwoing query:

while select * from sqlDictionary where sqlDictionary.FieldId>0 && sqlDictionary.tabId == tablenum(CustTable)

Now for getting the AX table buffer from the destination i will create an ODBC connnection from Source using follwoing code:

loginproperty = new loginproperty();
loginProperty.setDSN(“DSN name of target”);
loginProperty.setDatabase(“AOS servername of target”);
odbcConnection = new ODBCConnection(loginProperty);
if (odbcConnection)
{
sql = odbcConnection.createStatement();
result = sql.executeQuery(strfmt("select * from CustTable)); // result id of type ResultSet
while (result.next())
{
}
Now, the issue i am facing is that how shall i get the Table Buffer of CustTable from target as resultset.getstring(1) etc returns the respective (1,2 3 etc) values of the CUstTable fields.

See, the actual requrement is that i have to show (in a report) the difference of the records between an AX table from Source and target (data has been migrated from Source to target via BI/SSIS packages). I have to make this code generic so that this could be executed for any table. For that i am passing the values of the table name and the primary key value of this table form a form to the class (in which i am writing all this code).

I wonder if i was able to explain my requirement. Please provide inputson getting the table buffer at destination so that we can go ahaead and do record comparision.

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));

}

}

}

}

hi Alex,

Thanks for the post. yeah i do agree that it got to be done in SQL but we are doing this in AX as per project requirements.

yeah, the code that you have shared is too complex. i wonder if i would have been able to think in that direction at all. :slight_smile:

so, can we use ur code in job for execution or it has to be in full fledged class only (where i am actually writing my code).

Anyhow, i will do the same (both job and class) :slight_smile:

Regards,

Barkha

I wrote it as a job where you can put your SQL information in, and it should just run. It most likely will have an error or two after execution, but it should produce some data that will help you down the right path.

I would put it in a class and re-write it for production use. This is just a demo job.