Select forupdate table statement in Dynamics Ax dynamically

Finance and Operations/AX Technical Forum
1

Hi dyanmics ax forum,

I need help on select forupdate table statement dynamically / multiple tables.

The scenario is, i need to update value for inventDimId fields in all tables which contain inventDimId fields.

But i have problems in “select forupdate * from (TableName)”, i can’t find any solution to put a variable that can change its table name each loop.

Below is my code:

server static void main(Args args)

{

Connection con = new Connection();

Statement stmt = con.createStatement();

ResultSet r;

str sql;

SqlStatementExecutePermission perm;

int i;

DataArea dataAreaIdTbl;

InventDimId inventDimId;

;

sql = strfmt(“select TABLE_NAME, column_name from INFORMATION_SCHEMA.COLUMNS where column_name = ‘inventDimId’”);

perm = new SqlStatementExecutePermission(sql);

perm.assert();

try

{

r = stmt.executeQuery(sql);

while select Id from dataAreaIdTbl where dataAreaIdTbl.Id == ‘345’

{

changecompany(dataAreaIdTbl.Id)

{

while (r.next())

{

//Need help on this section code

}

}

info(strfmt(’%1’,r.getString(1)));

}

}

catch (exception::Error)

{

print “An error occured in the query.”;

}

CodeAccessPermission::revertAssert();

}

2

I would do it either purely in X++ or purely in SQL - combining both can lead to troubles caused by differences between table names in AOT and in SQL database. (That can be handled too, but it’s an unnecessary complication).

The X++ way would look like this:

SysDictTable dt = new SysDictTable(tableName2id('InventTrans'));
Common t = dt.makeRecord();

while select forUpdate t
    where t.(dt.fieldName2Id('InventDimId')) == 'something'
{ … }

x

3

A solution could be to use X++ and select in a loop like so:

MyTable myvar;

while select myVar

{

if (condition here)

{

myVar.selectForUpdate(true);

myVar.inventDimID = newValue;

myVar.update();

}

//else it loops through to the next record

}

4

Also you’ll want to be sure to add transaction scope in your efforts above.

5

You see how to access InventDimId field in my code above, you’ll use the same approach for setting the value. Calling update() is the same usual.

Do you need any other help?