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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s