We start the code by creating DictTable and DictField objects for handling the vendor table and its fields later in the query.

A new SqlSystem object also has to be created. It will be used to convert Dynamics AX types to SQL types.

Next, we set up a SQL statement with %1, %2, and other placeholders for table or field names or values to be inserted later.

The main query creation happens next when the query placeholders are replaced with the right values. Here we use previously created DictTable and DictField type objects by calling their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we pass the name exactly how it is used in the database. For example, the Dimension field in VendTable actually consists of a set of three fields, which are named in the database DIMENSION, DIMENSION2_, and DIMENSION3_ respectively and using DbBackend::Sql would return us exactly that.

We also use the sqlLiteral() of the previously created sqlSystem object to properly format SQL values to make sure that they do not have any unsafe characters.

Once the SQL statement is ready, we initialize a direct connection to the database and run the statement. The result is a resultSet object, and we get the results by using the while statement and calling its next() until the end.

Note that we create an sqlPermission object of type SqlStatementExecutePermission here and call its assert() before executing the statement. This is required in order to comply with Dynamics AX SQL execution security requirements.

Another thing to mention is that when building direct SQL queries, special attention has to be paid to license, configuration, and security keys. Some tables or fields might be disabled in the application and may not be present in the database.

The code in this recipe can be also used to connect to the external ODBC databases. We only need to replace the UserConnection class with OdbcConnection and use text names instead of DictTable and DictField.

The Standard Dynamics AX application provides an alternate way of building direct SQL statements by using a set of SQLBuilder classes. By using those classes, we can create SQL statements as objects as oppose to text. We will demonstrate next how to use a set of SQLBuilder classes. We will create the same SQL statement as before.

First in AOT, we create another class called VendTableSqlBuilder with the following code::

class VendTableSqlBuilder
 {
 }
 server static void main(Args _args)
 {
     UserConnection                  userConnection;
     Statement                       statement;
     str                             sqlStatement;
     SqlStatementExecutePermission   sqlPermission;
     ResultSet                       resultSet;
     SQLBuilderSelectExpression      selectExpr;
     SQLBuilderTableEntry            vendTable;
     SQLBuilderFieldEntry            accountNum;
     SQLBuilderFieldEntry            name;
     SQLBuilderFieldEntry            dataAreaId;
     SQLBuilderFieldEntry            blocked;
     ;
 
     selectExpr = SQLBuilderSelectExpression::construct();
 
     vendTable = selectExpr.addTableId(
         tablenum(VendTable));
 
     accountNum = vendTable.addFieldId(
         fieldnum(VendTable,AccountNum));
 
     name = vendTable.addFieldId(
         fieldnum(VendTable,Name));
 
     dataAreaId = vendTable.addFieldId(
         fieldnum(VendTable,DataAreaId));
 
     blocked = vendTable.addFieldId(
         fieldnum(VendTable,Blocked));
 
     vendTable.addRange(dataAreaId, curext());
     vendTable.addRange(blocked, CustVendorBlocked::No);
 
     selectExpr.addSelectFieldEntry(
         SQLBuilderSelectFieldEntry::newExpression(
             accountNum,
             'AccountNum'));
 
     selectExpr.addSelectFieldEntry(
         SQLBuilderSelectFieldEntry::newExpression(
             name,
             'Name'));
 
     sqlStatement   = selectExpr.getExpression(null);
 
     userConnection = new UserConnection();
     statement      = userConnection.createStatement();
 
     sqlPermission = new SqlStatementExecutePermission(
         sqlStatement);
 
     sqlPermission.assert();
 
     resultSet = statement.executeQuery(sqlStatement);
 
     CodeAccessPermission::revertAssert();
 
     while (resultSet.next())
     {
         info(strfmt(
             "%1 - %2",
             resultSet.getString(1),
             resultSet.getString(2)));
     }
 }

In this method, we first create a new selectExpr object, which is based on the SQLBuilderSelectExpression class. It represents the object of the SQL statement.

Next, we add the VendTable table to it by calling its member method addTableId(). The method returns a reference to the vendTable object of type SQLBuilderTableEntry, which corresponds to a table node in a SQL query.

Then, we create four field objects of type SQLBuilderFieldEntry to be used later and two ranges to show only this company account and only active vendor accounts.

We use addSelectFieldEntry() to add two fields to be selected. Here we use the previously created field objects.

The SQL statement is generated once getExpression() is called and the rest of the code is the same as in the previous example.

Running the class would give us results exactly like the ones we got before.