Skip to main content

Notifications

Executing direct simple SQL statement in D365FO

Hi,

In this post we will see the demo of executing simple SQL statement directly from D365FO.

  •  Created new tables by name SampleTableSampleTrans and loaded some data into it.
  •  Constructed SQL statement operates on table SampleTable and display its data.
  •  Note: CG_ExecuteSimpleSQLStmt is a runnable class
class CG_ExecuteSimpleSQLStmt
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {  
        UserConnection                  userConnection;
        Statement                       statement;
        str                             sql;
        SQLSystem                       system;
        SQLStatementExecutePermission   permission;
        ResultSet                       resultSet;
       
        DictTable                       sampleTable = new DictTable(tableNum(SampleTable));
        DictField                       Id = new DictField(tableNum(SampleTable), fieldNum(SampleTable, Id));
        DictField                       tableDueDate = new DictField(tableNum(SampleTable), fieldNum(SampleTable, dueDate));
       
        system = new SqlSystem();
 
        sql = 'SELECT %1, %2 FROM %3';
 
        sql = strFmt(sql,
                    tableDueDate.name(DbBackend::Sql),  // 1
                    Id.name(DbBackend::Sql),            // 2
                    sampleTable.name(DbBackend::Sql));  // 3
 
        userConnection = new UserConnection();
        statement      = userConnection.createStatement();
 
        new SqlStatementExecutePermission(sql).assert();
       
        resultSet = statement.executeQuery(sql);
        CodeAccessPermission::revertAssert();
 
        while (resultSet.next())
        {
            info(strFmt("Id %1 and due date %2", resultSet.getString(1), resultSet.getString(2)));
        }
    }
 
}

Output

Regards,

Chaitanya Golla

Comments

*This post is locked for comments