SQL access on production in D365FO
With the introduction of D365 Finance & operations cloud environment over AX 2012, Microsoft took over the control of the production window server OS environment leading to requesting deployment via LCS as compared to manual deployment before and also SQL Server Management Studio(SSMS) is now not available for SQL manipulation.
Of course one of the intentions behind restricting production access was not able to access SQL commands thereby avoiding data inconsistency. But as a developer, I always wanted to explore the ways in which SQL could be accessed on production indirectly. So I present my way of SQL browser in D365 finance & operations production environment.
We need to create 2 class
1) SQLBrowser which will create a dialog to take SQL input
2) SQLHelper which will process the inputted query
Disclaimer: SQL usage over production environment should be used with caution
| class SqlBrowser | |
| { | |
| public static void Main(Args _args) | |
| { | |
| Dialog Dialog = new Dialog("SQL"); | |
| 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),'sqlresult.txt');//exporting result of select statement to txt file | |
| } | |
| } | |
| } |
by | 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; | |
| } | |
| } |
To access SqlBrowser class on production
Enter the following URL
www.YourERPUrl.com/?mi=sysclassrunner&cls=SqlBrowser
Above URL will open dialog where you can input SQL queries and get desired results.
I hope this article helped you learn how to access SQL on production environment in D365 Finance & operations AX. Don’t forget to share this article. You may also want to see our article on How to use POSTMAN to access D365FO Odata endpoint. You can follow us on facebook for more tips and tricks.
Facebook
Linkedin
Comments: 5
Worked for me. Thanks a lot for the help.
I’m glad to hear it worked well for you
Thank you very much. this article is very helpful
Does this work with parameters also like applying where condition?
landed on this page while searching something on google, glad that its from my office senior (from YMSLI)