SQL Query on production in D365 Finance Operations #D365 #SQL #d365FO #x++

First of all, need to create 2 classes

1) SQLBrowser which will create a dialog to take SQL query as an input

2) SQLHelper which will process the query entered in Step 1 class

class SqlBrowser
{


? ? public static void Main(Args _args)
? ? {
? ? ? ? Dialog Dialog = new Dialog("SQL Query");
? ? ? ? DialogField queryInput = Dialog.addField(extendedTypeStr(EInvoiceCFDIOriginalString_MX),"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() )
? ? ? ? {
? ? ? ? ? ? str query = queryInput.value();
? ? ? ? ? ? container con = SQLHelper::getExpectedResultFromQuery(query);
? ? ? ? ? ? info(con2Str(con));
  ? ? ? ? ? File::SendStringAsFileToUser(con2Str(con),'sqlqueryresult.txt');
            //exporting result of select statement into txt file
            
            
        }
            
       ? ? }
            
            
 }

        

2

class SQLHelper
	{

	        
	        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 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 8: // Memo
	                                expectedSubResult += resultSet.getString(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));
	                // End of while
	            }
	            catch
	            {
	                Error('error');
	            }
	

	            return expectedResult;
	        }
	
	        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;
	        }
	

	}        

To access SqlBrowser class on the production environment.

www.YourProductionUrl.com/?mi=sysclassrunner&cls=SqlBrowser