Using SQLBuilderView class to expose view creation definition in D365FO

Chaitanya Golla

17,225

Like(1)

Report

Hi,

In this post will provide the code that exposes the view definition through SQLBuilderView class in D365FO.

Created a table by name SampleTable and loaded some data into it.
Created a view by name SampleView based on table SampleTable using SQLBuilderView class.
On executing the class CG_ViewSelectUsingSQLBuilder it provides the view creation definition of SampleView.
Note: CG_ViewSelectUsingSQLBuilder is a runnable class and macro #SchemaViews represents 'INFORMATION<underscore>SCHEMA.Views'

class CG_ViewSelectUsingSQLBuilder
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    { 
        
        #Define.TableName('TABLE_NAME')
        #Define.Definition('VIEW_DEFINITION')

        SQLBuilderSelectExpression      selectExpr = SQLBuilderSelectExpression::construct();
        SQLBuilderTableEntry            viewTableEntry;
        SQLBuilderFieldEntry            viewField1Entry, viewField2Entry;
        SQLStatementExecutePermission   permission;
        
        UserConnection                  userConnection;
        Statement                       statement;
        ResultSet                       resultSet;
        str                             sql;
        str                             viewId = "SampleView";

        viewTableEntry = selectExpr.addTable(#SchemaViews);
        viewField1Entry = viewTableEntry.addField(#TableName);
        viewField2Entry = viewTableEntry.addField(#Definition);

        selectExpr.addField(viewField1Entry, viewId);
        selectExpr.addField(viewField2Entry, 'Definition');

viewTableEntry.addRange(viewField1Entry, queryValue(viewId), SQLBuilderBinaryOperatorType::Equal);

        sql = selectExpr.getExpression(null);
 
        userConnection = new UserConnection();
        statement      = userConnection.createStatement();

        new SqlStatementExecutePermission(sql).assert();
       
        resultSet = statement.executeQuery(sql);
        CodeAccessPermission::revertAssert();

        while (resultSet.next())
        {
            info(strFmt("View %1 has defintion %2", resultSet.getString(1), resultSet.getString(2)));
        }
    }

}

Output:InfoMessage

Regards,

Chaitanya Golla