How to Run Query Through Classes on d365fo x++

Run Query Through Class

 1. Add Base Enum

    





2. Add Extended Data Types





3. Add These Two Classes

  • SqlBrowser
  • SQLHelper
class SqlBrowser
{

    public static void Main(Args _args)
    {

        if(isSystemAdministrator())
        {
            Dialog Dialog = new Dialog("SQL");
            DialogField querySelection = Dialog.addField(enumStr(QuerySelectionType),"SQL query Type");
            DialogField queryInput = Dialog.addField(extendedTypeStr(QueryEdt),"SQL query");
            //Note extendedTypeStr used is string EDT with memo string size i.e no restriction on how bug the query is
        
            Dialog.run();
        
            if(Dialog.closedOk() && queryInput.value() &&  querySelection.value() == QuerySelectionType::Select)
            {
                str query = queryInput.value();
                container con  = SQLHelper::getExpectedResultFromQuery(query);
                container con1 = conpeek(con,1);
                info(con2Str(con1));
                File::SendStringAsFileToUser(con2Str(con1),'sqlresult.txt');//exporting result of select statement to text file
            }
            else if(Dialog.closedOk() && queryInput.value() &&  querySelection.value() != QuerySelectionType::Select)
            {
                str query = queryInput.value();
                SQLHelper::Manipulating_data(query);
                info("Row(s) Affected");
            }
        }

    }

}
-----------------------------------------------------------------------------------------------------
class SQLHelper
{

    /// <summary>
    /// Returns the rows obtained after executing a SQL statement on the DB, after formatting them w.r.t the data type. Converts the rows from ResultSet into Container.
    /// </summary>
    /// <param name="queryStmt">
    /// The SQL statement executed on the backend DB.
    /// </param>
    /// <returns>
    /// Container of rows returned after executing the result.
    /// </returns>
    /// <remarks>
    /// </remarks>
    public static Container getExpectedResultFromQuery(str queryStmt)
    {
        int i;
        int colType;
        container expectedSubResult, expectedResult;
        int counter;
        ResultSet resultSet;
        ;

        try
        {
            // Executes the Query statement on the back end database.
            resultSet = SQLHelper::resultSetExecuteQuery(queryStmt);

            while(resultSet.next())
            {
                expectedSubResult = connull();

                for ( i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
                {
                    colType = resultSet.getMetaData().getColumnType(i);
                    switch (colType)
                    {
                        case 0: // String
                        case 8: // Memo
                            expectedSubResult += resultSet.getString(i);
                            break;
                        case 1: // Interger
                            expectedSubResult += resultSet.getInt(i);
                            break;
                        case 2: // Real
                            expectedSubResult += resultSet.getReal(i);
                            break;
                        case 3: // Date
                            expectedSubResult += resultSet.getDate(i);
                            break;
                        case 4: // Enum
                            expectedSubResult += resultSet.getBoolean(i);
                            break;
                        case 6: // UtcDateTime
                            expectedSubResult += dateTime2str(resultSet.getDateTime(i));
                            break;
                        case 45: // Guid
                            expectedSubResult += guid2str(resultSet.getGuid(i));
                            break;
                        case 49: // Int64
                            expectedSubResult += resultSet.getInt64(i);
                            break;
                        default:
                            break;
                    }
                    // End of Switch
                }
                // End of for

                expectedResult += [expectedSubResult];
            }
            //info(con2Str(expectedSubResult));//can be commented- just used to info expectedSubResul
            // End of while
        }
        catch
        {
            Error('error');
        }

        return expectedResult;
    }

    /// <summary>
    /// Returns the set of rows after executing the SQL statement on the backend database using the ResultSet class.
    /// </summary>
    /// <param name="strQuery">
    /// The SQL statement executed on the backend DB.
    /// </param>
    /// <returns>
    /// ResultSet containg the rows obtained after executing the Query
    /// </returns>
    /// <remarks>
    /// </remarks>
    private server static ResultSet resultSetExecuteQuery(str strQuery)
    {
        Connection  connection = new Connection();
        Statement   statement = connection.createStatement();
        SqlStatementExecutePermission permission;
        ResultSet resultSet;
           

        // Get the formatted Query statement.
        strQuery = strfmt(strQuery,SRSStatementQuery::getDbSchemaPrefix());
            
        permission = new SqlStatementExecutePermission(strQuery);
        permission.assert();

        resultSet = statement.executeQuery(strQuery);

        return resultSet;
    }

    public static void Manipulating_data(str queryStmt)
    {
        str sql;
        Connection conn;
        SqlStatementExecutePermission permission;
        ;

        sql =         queryStmt;

        permission = new SqlStatementExecutePermission(sql);
        conn = new Connection();
        permission = new SqlStatementExecutePermission(sql);
        permission.assert();
        conn.createStatement().executeUpdate(sql);
        // the permissions needs to be reverted back to original condition.
        CodeAccessPermission::revertAssert();
    }

}
----------------------------------------------------------------------------------------------------------------

How to Access:

To access SqlBrowser class
Enter the following URL
www.YourERPUrl.com/?mi=sysclassrunner&cls=SqlBrowser

Output:


















Comments

Post a Comment

Popular posts from this blog

D365FO – AX – X++ –Refresh, Reread, Research, and ExecuteQuery

 D365FO – AX – X++ – Tutorial: Choosing Between Refresh, Reread, Research, and ExecuteQuery X++ developers often face challenges with the four datasource methods—refresh, reread, research, and executeQuery—in Dynamics 365 Finance and Operations (D365FO). Regardless of their seniority in AX, developers frequently make mistakes in the sequence of calling these methods. To address this, I've created a brief hands-on tutorial illustrating the typical use cases for each method. I've organized the methods based on their impact on the displayed grid rows. 1. Common Mistakes: Developers often incorrectly call two of these methods in the following order: - formDataSource.refresh() followed by formDataSource.research(), or - formDataSource.reread() followed by formDataSource.research(), or - formDataSource.research() followed by formDataSource.executeQuery(), or - formDataSource.research() followed by formDataSource.refresh() / formDataSource.reread(). These sequences are either entirely...
Read more

Create Inventory Journal through Code in D365FO X++

 Create Inventory Journal through Code in D365FO X++ Code:  internal final class Cls_FPS_MOVJRNL {     /// <summary>     /// Class entry point. The system will call this method when a designated menu      /// is selected or when execution starts and this class is set as the startup class.     /// </summary>     /// <param name = "_args">The specified arguments.</param>     public static void main(Args _args)     {         CBLFPS_StkAdjTbl       CBLFPS_StkAdjTbl;         ttsbegin;             // Create journal table             InventJournalTable inventJournalTable = Cls_FPS_MOVJRNL::createJournalTable();                          // Initialize journal trans             ...
Read more

SalesLine Reservation in D365fo x++

SalesLine Reservation in D365fo x++ SQL: /// <summary> /// reserve sales line /// </summary> /// <param name = "_salesline">SalesLine</param> public static void reserveSalesLine(SalesLine _salesline) { InventBatch inventBatch; InventSum inventSum; SalesLine salesLine; InventDim inventDim; InventTable inventTable; int daysLeftforExpiry = 0; int shelflife; int rule = 0; int acceptedPR = 0; Qty qtyReserve = 0; Qty saleLineQty = 0; DSAPickingRulePerId pickingrule; boolean qtyReservedCheck = false; salesLine = _salesline; pickingrule = _salesline.salesTable().DSAPickingRule; if(pickingrule == '') { warning("Picking rule is not available on sales order for reservation."); } else { ...
Read more