DynamicsAX - Get SQL Statement by tablename

2
votes

I'd like to create a class in AX2012 which I can call from Ax2012 Business Connector that allows me to generate the SQL statement for a specified table.

The method I implemented at the class is:

client server public static str getTableStatement(str tableName)
{    
     OMInternalOrganization org;
     select generateOnly org;
     info(org.getSQLStatement());
     return org.getSQLStatement();
}

My question is: how can I now use the parameter tableName instead of the static assignment of OMInternalOrganization.

It would be also fine to find another way of getting the SQL statement.

I'm a .Net Developer and have nearly NO experience with X++ development.

Thank you for your help!

2
c#sqlaxaptadynamics-ax-2012ax
"It would be also fine to find another way of getting the SQL statement" - what is the current logic of your getSQLStatement method? - 10p
@10p - getSQLStatement is base 2012. I was unaware of it as well. - Alex Kwitny
my current logic was taking the tablename from the ax metadataservice and building the sql statement based on this. I had problems with inheritance here so i found that it is possible from AX to get the statement, but i didn't know how. - kamahl

2 Answers

3
votes

You can use SysDictTable::makeRecord. 

client server public static str getTableStatement(str tableName)
{
    Common buffer;
    ;

    buffer = SysDictTable::newName(tableName).makeRecord();
    select generateOnly buffer;
    info(buffer.getSQLStatement());
    return buffer.getSQLStatement();
}
0
votes

If you don't need the list of the table fields, you can use the following code.

client server public static str getTableStatement(str _tableName)
{
    SysDictTable dt = SysDictTable::newName(_tableName);
    str ret;    

    if (dt)
    {
        ret = strFmt('SELECT * FROM %1', dt.name(DbBackend::Sql));
    }

    return ret;
}

P.S. You can modify the code if you're only interested in the name of the table in SQL Server (not in AX).