AX – D365FO – Executing a direct SQL statement

This recipe will demonstrate how to execute direct SQL statements. 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…

Open AOT, and create a new class called 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;
     DictField                       fldAccountNum;
     DictField                       fldName;
     DictField                       fldDataAreaId;
     DictField                       fldBlocked;
     ;
 
     tblVendTable = new DictTable(tablenum(VendTable));
 
     fldAccountNum = new DictField(
         tablenum(VendTable),
         fieldnum(VendTable,AccountNum));
 
     fldName = new DictField(
         tablenum(VendTable),
         fieldnum(VendTable,Name));
 
     fldDataAreaId = new DictField(
         tablenum(VendTable),
         fieldnum(VendTable,DataAreaId));
 
     fldBlocked = new DictField(
         tablenum(VendTable),
         fieldnum(VendTable,Blocked));
 
     sqlSystem = new SqlSystem();
 
     sqlStatement = 'SELECT %2, %3 FROM %1 ' +
         'WHERE %4 = %6 AND %5 = %7';
 
     sqlStatement = strfmt(
         sqlStatement,
         tblVendTable.name(DbBackend::Sql),
         fldAccountNum.name(DbBackend::Sql),
         fldName.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)));
     }
 }

Run the class, and notice the list of vendors retrieved directly from the database:

While running one Job , where I was trying to execute one SQL statement in AX, I got the below stack trace :

Request for the permission of type ‘SqlStatementExecutePermission’ failed.
(S)\Classes\SqlStatementExecutePermission\demand
(S)\Classes\Statement\executeQuery
(C)\Jobs\test – line 21

So what is the solution for this , Very simple , we need to run this job on Server , So now how to do it :here is solution , create a menu item of type action, give the object type as Job ,and object name , and set the property Run on as” Server “.

Other direct sql statements examples :

“Select” statement

public static server void main(Args _args)
{
     Connection      connection;
     Statement       statement;
     str             query;
     Resultset       resultSet;
     ;

     // create connection object
     connection = new Connection();

     // create statement
     statement = connection.createStatement();

     // Set the SQL statement
     query = 'select name from CustTable';

     // assert SQL statement execute permission
     new SqlStatementExecutePermission(query).assert();

     // when the query returns result,
     // loop all results for processing
     //BP Deviation documented
     resultSet = statement.executeQuery(query);

     while(resultSet.next())
     {
         // do something with the result
         info(resultSet.getString(1));
     }

     // limit the scope of the assert call
     CodeAccessPermission::revertAssert();
}

“Insert” statement

public static server void main(Args _args)
{
     Connection      connection;
     Statement       statement;
     str             query;
     ;

     // create connection object
     connection = new Connection();

     // create statement
     statement = connection.createStatement();

     // Set the SQL statement
     query = "insert into CustTable (AccountNum, Name, RecId) values ('demo', 'demo', 2)";

     // assert SQL statement execute permission
     new SqlStatementExecutePermission(query).assert();

     //BP Deviation documented
     statement.executeUpdate(query);

     // limit the scope of the assert call
     CodeAccessPermission::revertAssert();
}