Execute SQL query in PROD/Sandbox and fetch records from UI

 Sometime in PROD, user expect report without waiting report development/deployment or developer wants to execute SQL script to test some result. As we know, in PROD/Sandbox environment direct SQL script execution is not possible.

Will understand this development with below example:

1.      Lets assume end user wants a summery report which contains below details:

Sales order number, Customer number, Item, Line amount(SUM of line amount which are group on SalesId)

2.      Few of the development script not working as expected or Developer feels there are some data issue and due to that their development not working and immediately he/she wants to test his/her development script

 Both of the scenario’s achieve using today topic 😊

 To achieve this, we have developed a form:

We have added two tabs one for SQL script and second tab for maintain log for historical data

No alt text provided for this image

SQL Query Tab:

Filename: After executing the SQL script, a HTML file download with file name

MaxRows: Number of rows(records) display on script

SQLText: The SQL script which needs to execute. In our case we have executed below script –

select ST.SALESID,ST.CUSTACCOUNT,SL.ITEMID,sum(SL.LINEAMOUNT) as "Line amount" from Salestable ST join SALESLINE SL on

 ST.SALESID = SL.SALESID

 group by ST.SALESID,ST.CUSTACCOUNT,SL.ITEMID

USERID: Current user id 

Log Tab: This tab shows the historical run

No alt text provided for this image

RUN Button: Added “Run” button to execute SQL script. The result has placed on HTML viewer.

No alt text provided for this image

Development: Below objects have been used for development:

No alt text provided for this image

Form: XXX_SQLQueryExecute Design and associated code

No alt text provided for this image

[Form]

public class XXX_SQLQueryExecute extends FormRun

{

 public void init()

  {

    super();

    ttsbegin;

    XXX_SQLExecuteParm executeParmLocal;

    executeParmLocal.SQLText = 'select * from SalesTable';

    executeParmLocal.Filename = 'SQLOutcomes.html';

    executeParmLocal.UserId = curUserId();

    executeParmLocal.MaxRows = 1000;

    executeParmLocal.write();

    ttscommit;

    QueryBuildRange  qbr = ExecuteParm_ds.queryBuildDataSource().addRange(fieldNum(XXX_SQLExecuteParm, UserId));

    qbr.value(SysQuery::value(curUserId()));

  }


  [Control("Button")]

  class Run

  {

    public void clicked()

    {

      SourceSQL.text('select count(*) from salesTable');

      XXX_SQLQueryExecute queryExecute = XXX_SQLQueryExecute::construct(ExecuteParm);

      HtmlViewerResult.parmHtml(queryExecute.executeSQL());

      ExecuteParm_ds.reread();

    }

  }


  [Control("TabPage")]

  class LogTab

  {

    public void pageActivated()

    {

      SQLQueryExecuteLog_DS.research();

      super();

    }

  }

}

Class: XXX_SQLQueryExecute

class XXX_SQLQueryExecute

{

  XXX_SQLExecuteParm   sqlExecuteParm;

  XXX_SQLQueryExecuteLog sqlQueryExecuteLog;

  str           sqlResult;

  boolean         isRawOutput;

  void SQLlogStart()

  {

    sqlQueryExecuteLog.SQLText = sqlExecuteParm.SQLText;

    sqlQueryExecuteLog.StartDateTime = DateTimeUtil::getSystemDateTime();

    sqlQueryExecuteLog.insert();

  }

  void SQLLogEnd()

  {

    ttsbegin;

    sqlQueryExecuteLog = XXX_SQLQueryExecuteLog::findRecId(sqlQueryExecuteLog.RecId, true);

    sqlQueryExecuteLog.SQLText = sqlExecuteParm.SQLText;

    sqlQueryExecuteLog.EndDateTime = DateTimeUtil::getSystemDateTime();

    sqlQueryExecuteLog.Duration = any2Int(DateTimeUtil::getDifference(sqlQueryExecuteLog.EndDateTime, sqlQueryExecuteLog.StartDateTime));

    sqlQueryExecuteLog.write();

    ttscommit;

  }


  public XXX_SQLExecuteParm parmExecuteParm(XXX_SQLExecuteParm _executeParm = sqlExecuteParm)

  {

    sqlExecuteParm = _executeParm;

    return sqlExecuteParm;

  }


  static XXX_SQLQueryExecute construct(XXX_SQLExecuteParm _executeParm)

  {

    XXX_SQLQueryExecute   queryExecute = new XXX_SQLQueryExecute();

    queryExecute.parmExecuteParm(_executeParm);

   

    return queryExecute;

  }


  public str executeSQL()

  {

    str res;

    sqlResult = '';


    this.SQLlogStart();

    str fileExt = conPeek(fileNameSplit(sqlExecuteParm.Filename), 3);

    if (fileExt != '.html')

    {

      isRawOutput = true;

    }

    this.doExecuteQueryResult();

    if ( isRawOutput)

    {

      res = sqlResult;

    }

    else

    {

      res = '<!DOCTYPE html>\n <html><body><table>' + sqlResult + '</table></body></html>';

    }

    File::SendStringAsFileToUser(res, sqlExecuteParm.Filename);

    this.SQLLogEnd();

    return res;

  }

  public void doExecuteUpdate(str _strQuery)

  {

    Connection connection = new Connection();

    Statement  statement = connection.createStatement();

    SqlStatementExecutePermission permission;       

    permission = new SqlStatementExecutePermission(_strQuery);

    permission.assert();

    statement.executeUpdate(_strQuery);

  }

  private ResultSet getResultSet(str _strQuery)

  {

    Connection connection = new Connection();

    Statement  statement = connection.createStatement();

    SqlStatementExecutePermission permission;

    ResultSet resultSet;      

    permission = new SqlStatementExecutePermission(_strQuery);

    permission.assert();

    resultSet = statement.executeQuery(_strQuery);

    return resultSet;

  }

  void addResultRow(container _row, boolean _isHeader = false)

  {

    int i;

    sqlResult += '<tr style="border: 1px solid gray; padding: 1px;">';

    for (i = 1; i <= conLen(_row); i++)

    {

      if (_isHeader)

      {

        sqlResult += strFmt('<th style="border: 1px solid gray; padding: 1px;">%1</th>', conPeek(_row, i));

      }

      else

      {

        sqlResult += strFmt('<td style="border: 1px solid gray; padding: 1px;">%1</td>', conPeek(_row, i));

      }

    }

    sqlResult += '</tr>';

  }


  public void doExecuteQueryResult()

  {

    int     currentRow,maxColumnCount,i;

    container  expectedSubResult;

    ResultSet  resultSet;

    ResultSetMetaData resultSetMetaData;

    boolean   isFirstRow = true;


    resultSet = this.getResultSet(sqlExecuteParm.SQLText);

    resultSetMetaData = resultSet.getMetaData();


    while(resultSet.next())

    {

      currentRow++;

      if (currentRow > sqlExecuteParm.MaxRows)

      {

        warning(strFmt("Number of allowed lines exceeding from %1", sqlExecuteParm.MaxRows));

        break;

      }

      if (isRawOutput)

      {

        if (isFirstRow)

        {

          isFirstRow = false;

          maxColumnCount = resultSetMetaData.getColumnCount();

        }

        else

        {

          sqlResult += '\n';

        }

        for ( i = 1; i <= maxColumnCount; i++)

        {

          str   sTmp = resultSet.getString(i);

          if (i > i)

          {

            sqlResult += ',';

          }

          sqlResult += strFmt('%1', sTmp);

        }

      }

      else

      {

        //print headers

        if (isFirstRow)

        {

          expectedSubResult = connull();

          for ( i = 1; i <= resultSetMetaData.getColumnCount(); i++)

          {

            expectedSubResult += resultSetMetaData.getColumnName(i);

          }

          this.addResultRow(expectedSubResult, true);

          isFirstRow = false;


          maxColumnCount = resultSetMetaData.getColumnCount();

        }

        sqlResult += '<tr style="border: 1px solid gray; padding: 1px;">';

        for ( i = 1; i <= maxColumnCount; i++)

        {

          str   sTmp = resultSet.getString(i);


          sqlResult += strFmt('<td style="border: 1px solid gray; padding: 1px;">%1</td>', sTmp);

        }

        sqlResult += '</tr>';

      }

    }

  }

}

Comments

Post a Comment

Popular posts from this blog

Execute D365 F&O SSRS report with parameter and upload report output on Azure blob storage using X++

Image
 Execute D365 F&O SSRS report with parameter and upload report output on Azure blob storage using X++ Steps: 1. Create controller class and execute SSRS report with parameter 2. Create Azure storage on Azure 3. Create Azure storage containers and folder to store report output 4. Get Azure connection key to establish connection between D365 F&O and Azure storage 5. Convert SSRS report output into byte stream 6. Upload byte stream to azure storage How to create Azure storage, containers and folder and get Azure connection key? Login to https://portal.azure.com Go to Storage account --> Storage browser --> Blob containers How to get  Connection string ? Go to Storage account --> Security + networking --> Access Key --> Connection string Code: using Microsoft.WindowsAzure.Storage; Using Microsoft.WindowsAzure.Storage.Blob; using System.IO; public class ExampleUploadReportToBlobStorage {     public static void main(Args _args)     {  ...
Read more

Microsoft D365 F&O: Remove custom models from deployable package

Image
Sometime we deploy custom model to PROD/Sandbox and due to some reason needs to revert back custom model code. If we revert back code, code reverted back but model still exist in the  environment. the next time when we suppose to deploy new package, deployment would failed with following reason: "The selected package is missing the following modules that are currently deployed on your environment. You must create an all-in-one deployable package that contains all your code customizations and ISV solutions. More information can be found at the following link:  https://go.microsoft.com/fwlink/?linkid=2118104 . Missing module(s): [ Package1 ] ,[ Package2 ] [ Package3 ]’ (Activity Id: ‘12345XXXX’)" I will explain how to remove the custom models from your environment(PROD/Sandbox). Steps 1.  Create a deployable package from your VM(Visual studio) or download the build package 2.  Unzip the deployable package and open the deployable package folder. 3. Go to   \AX...
Read more

Generate Text/CSV/DAT file using X++ and upload on Azure blob storage

 Generate Text/CSV/DAT file using X++ and upload on Azure blob storage There are many scenario's where we want to wrap data inside the file and upload on blob storage. Following code use to generate file and upload on azure blob storage. `   public void processOperation(Contract _contract)     {         #OccRetryCount         #File         InventTrans          inventTrans;         Query                query;         QueryRun             queryRun;         str                  stringToWrite;         int                  recordCount = 0;         stringToWrite = "Customer Account Number|Invoice Amou...
Read more