DynamicsAX - Get SQL Statement by tablename

Question

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!

"It would be also fine to find another way of getting the SQL statement" - what is the current logic of your `getSQLStatement` method? — 10p, Aug 05 '14 at 13:47
@10p - getSQLStatement is base 2012. I was unaware of it as well. — Alex Kwitny, Aug 05 '14 at 15:39
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, Aug 06 '14 at 08:06

score 3 · Accepted Answer · answered Aug 05 '14 at 11:08

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

score 0 · Answer 2 · answered Aug 05 '14 at 16:18

0

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

answered Aug 05 '14 at 16:18

10p

5,488
22
30

thanks for the information about dbBackend::SQL I´m not sure if this one would work with table inheritance. – kamahl Aug 06 '14 at 08:06
For derived tables it would always return the base table name, because only 1 table is used in SQL Server database for table inheritance hierarchy. As I mentioned, it could be useful if you don't need the field names to be returned (i.e. you already know the fields you need to use or can get this info elsewhere). – 10p Aug 06 '14 at 10:06
Ah okay. Just for completeness, how could i get all the SQL-Field names for a single table? – kamahl Aug 07 '14 at 11:36
1

In AX - by using `DictTable` and `DictField` classes, and `DictField.name(DbBackend::Sql)`. In SQL Server - see http://stackoverflow.com/questions/600446/ – 10p Aug 07 '14 at 19:28

DynamicsAX - Get SQL Statement by tablename

2 Answers2