Skip to main content

Notifications

Dynamics 365 Community / Blogs / Peter's X++ developer Blog / Using Direct-SQL in X++, or...

Using Direct-SQL in X++, or not.

Peter Villadsen Profile Picture Peter Villadsen
I have been asked on my position on Direct-SQL and how it compares with using the normal X++ based data access primitives. When I refer to Direct-SQL (henceforth DSQL) I am mainly referring to the executeQuery and executeUpdate methods on the Statement class, and their versions accepting parameters: executeQueryWithParameters and executeUpdateWithParameters.

The main difference, as you will see below, is that DSQL passes strings containing T/SQL source code to the APIs described above whereas the X++ data access statements are first class citizens in X++. The normal data access statements in X++ cannot do everything that T/SQL can, but the compiler is there to find any error you make.

Let's consider the pros and cons of each data access approach:

Pros:
  • Using DSQL offers all the power of T/SQL unabridged. Nested queries, case/when statements, unions etc. are all at your disposal. Knock yourself out.
 Cons:
  • As described above, there is no help from the X++ compiler when authoring T/SQL strings. The strings that you pass to the SQL server are not analyzed in any way at compile time, only at runtime. This obviously increases the test burden, since you are vulnerable to type issues and misspellings of fields etc that the X++ compiler (your best friend) trivially catches. Make sure you have automated tests that exercise your code throughly, and that those tests run repeatedly.
  • There is no Go To Definition, Find All References for tables and fields in the developer experience for tables and fields that are referenced in the strings.
  • You must understand the name mangling that takes place when representing table and field names in T/SQL. You also have to understand the mechanics behind supertype / subtype for tables that are arranged in that way.
  • You must deal with companies yourself. The X++ runtime will restrict all access to data to the current company (for tables that are per company, obviously). The X++ runtime does this by automatically inserting a where clause that you will have to remember to add yourself in T/SQL. The same applies if you are using partitions.
  • No eventing is performed. There is no way to be notified in X++ when CUD operations happen (this may count as advantage sometimes, depending on the scenario). This makes extensibility of very difficult to acheive.
  • You MUST be very careful when you construct the strings that you pass to the SQL server. Under NO CIRCUMSTANCES may you construct T/SQL source doing string concatenations with information that comes over the wire from a service call or that is entered on a form. You MUST use the parameters feature to pass information to the T/SQL text. There are no exceptions to those rules. You must make sure that your peers who will review your code are aware of these dangers.
You may want to peruse https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/query-with-parameters for a more thorough walkthrough of the risk of injection attacks. If you are using the versions of the APIs mentioned above without the ...WithParameters suffix you will have to rewrite the code when we will make using the non-parameterized version into hard errors. Why not do it now, and sleep better?





Comments

*This post is locked for comments