Executing a direct SQL statement

Dynamics AX allows developers to build X++ SQL statements that are flexible enough to fit into any custom business process. However, in some cases, the usage of X++ SQL is either not effective or not possible at all.

One of the cases is when we run data upgrade tasks during an application version upgrade. The standard application contains a set of data upgrade tasks to be completed during the version upgrade. If the application is highly customized, then most likely the standard tasks have to be modified to reflect data dictionary customizations, or even a new set of tasks have to be created to make sure data is handled correctly during the upgrade.

Normally at this stage, SQL statements are so complex that they can only be created using database-specific SQL and executed directly in the database. Additionally, running direct SQL statements dramatically increases data upgrade performance because most of the code is executed on the database server where all data resides. This is very important while working with large volumes of data.

Another case when we would need to use direct SQL statements is when we want to connect to an external database using the ODBC connection. In this case, X++ SQL is not supported at all.

This recipe will demonstrate how to execute SQL statements directly. We will connect to the current Dynamics AX database directly using an additional connection and will retrieve the list of vendor accounts.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. In the AOT, create a new class named VendTableSql with the following code:

   class VendTableSql
   {
   }
   server static void main(Args _args)
   {
   UserConnection userConnection;
   Statement statement;
   str sqlStatement;
   SqlSystem sqlSystem;
   SqlStatementExecutePermission sqlPermission;
   ResultSet resultSet;
   DictTable tblVendTable;
   DictTable tblDirPartyTable;
   DictField fldParty;
   DictField fldAccountNum;
   DictField fldDataAreaId;
   DictField fldBlocked;
   DictField fldRecId;
   DictField fldName;
   tblVendTable = new DictTable(tableNum(VendTable));
   tblDirPartyTable = new DictTable(tableNum(DirPartyTable));
   fldParty = new DictField(
   tableNum(VendTable),
   fieldNum(VendTable,Party));
   fldAccountNum = new DictField(
   tableNum(VendTable),
   fieldNum(VendTable,AccountNum));
   fldDataAreaId = new DictField(
   tableNum(VendTable),
   fieldNum(VendTable,DataAreaId));
   fldBlocked = new DictField(
   tableNum(VendTable),
   fieldNum(VendTable,Blocked));
   fldRecId = new DictField(
   tableNum(DirPartyTable),
   fieldNum(DirPartyTable,RecId));
   fldName = new DictField(
   tableNum(DirPartyTable),
   fieldNum(DirPartyTable,Name));
   sqlSystem = new SqlSystem();
   sqlStatement = 'SELECT %3, %4 FROM %1 ' +
   'JOIN %2 ON %1.%5 = %2.%6 ' +
   'WHERE %7 = %9 AND %8 = %10';
   sqlStatement = strFmt(
   sqlStatement,
   tblVendTable.name(DbBackend::Sql),
   tblDirPartyTable.name(DbBackend::Sql),
   fldAccountNum.name(DbBackend::Sql),
   fldName.name(DbBackend::Sql),
   fldParty.name(DbBackend::Sql),
   fldRecId.name(DbBackend::Sql),
   fldDataAreaId.name(DbBackend::Sql),
   fldBlocked.name(DbBackend::Sql),
   sqlSystem.sqlLiteral(curext(), true),
   sqlSystem.sqlLiteral(CustVendorBlocked::No, true));
   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)));
   }
   }
   

2. 2. Run the class to obtain the list of vendors retrieved directly from the database:

   

How it works...

We start the code by creating DictTable and DictField objects for handling the vendor table and its fields used later in the query. DirPartyTable table is used to get additional vendor information.

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 an SQL statement with a number of placeholders for table or field names and field values to be inserted later.

The main query creation happens next when the query placeholders are replaced with the right values. Here we use the 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—some of the SQL field names are not necessary the same as field names within the application.

We also use the sqlLiteral() method of the previously created sqlSystem object to properly format SQL values to make sure 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 results are returned into the resultSet object, and we get them by using the while statement and calling the next() method until the end of the resultSet object.

Note that we create an sqlPermission object of type SqlStatementExecutePermission here and call its assert() method before executing the statement. This is required in order to comply with Dynamics AX trustworthy computing 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 contain no data 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 the OdbcConnection class and use text names instead of the DictTable and DictField objects.

There's more...

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 opposed to text. Next, we will demonstrate how to use the SQLBuilder classes. We will create the same SQL statement as before.

First in AOT, we create another class named 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;
SQLBuilderTableEntry dirPartyTable;
SQLBuilderFieldEntry accountNum;
SQLBuilderFieldEntry dataAreaId;
SQLBuilderFieldEntry blocked;
SQLBuilderFieldEntry name;
selectExpr = SQLBuilderSelectExpression::construct();
selectExpr.parmUseJoin(true);
vendTable = selectExpr.addTableId(
tablenum(VendTable));
dirPartyTable = vendTable.addJoinTableId(
tablenum(DirPartyTable));
accountNum = vendTable.addFieldId(
fieldnum(VendTable,AccountNum));
name = dirPartyTable.addFieldId(
fieldnum(DirPartyTable,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 an SQL query. We also add DirPartyTable as a joined table.

Then, we create a number of 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 the getExpression() method is called, and the rest of the code is the same as in the previous example.

Running the class would give us results, which are exactly similar to the ones we got before.