Using the Support Debugging Tool to create user accessible SQL Scripts – Part 2


David Meego - Click for blog homepageThis is the second article in the series, if you haven’t already, please see the previous article: Using the Support Debugging Tool to create user accessible SQL Scripts – Part 1.

Today’s article adds a simple user interface for the previously created SQL scripts using Dexterity sanScript.

Last article we created a couple of SQL queries and saved them in SQL Execute. We will now re-use those scripts in our code, adding an additional where clause based on input from the user.

This highlights a couple of very cool features of the Support Debugging Tool: The ability to execute previously saved SQL scripts from Dexterity and the ability to execute a previously saved Dexterity script (against any dictionary). Along with the helper functions that allow you to read and write to fields in any window on any form in any dictionary, it makes the Support Debugging Tool a very powerful cross dictionary, cross environment development tool.

Part 2: Create the User Interface

The next step is to use Runtime Execute to create a simple user interface using system dialogs and the SQL scripts saved previously.

  1. Open the Support Debugging Tool main window (Microsoft Dynamics GP >> Tools >> Support Debugging Tool or Ctrl-D).
  2. Open the Runtime Execute window (Options >> Runtime Execute).
  3. Enter the Script ID, Script Name and script for the code to execute.
    local string l_string;
    local text MBS_Text_Field;
    local integer MBS_Status;
    
    if not getstring("Customer ID begins with:", false, l_string) then
    	abort script;
    end if;
    
    call with name "MBS_Script_Load_SQL" in dictionary 5261,
    	"DEMO1", MBS_Text_Field;
    
    if not empty(l_string) then
    	MBS_Text_Field = MBS_Text_Field + "and CUSTNMBR like " + SQL_FormatStrings(l_string+"%");
    end if;
    
    {clear MBS_Text_Field;
    MBS_Text_Field = MBS_Text_Field + "select * from table" + char (13);
    }call with name "MBS_SQL_Check_Exists" in dictionary 5261,
    	MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
    case MBS_Status
    	in [OKAY]
    		warning MBS_Text_Field;
    	in [MISSING]
    		warning MBS_Text_Field;
    		abort script;
    	else
    		warning MBS_Text_Field;
    		abort script;
    end case;
    
    if ask("Do you want to clear Hold flags?", "Yes", "No", "") <> ASKBUTTON1 then
    	abort script;
    end if;
    
    call with name "MBS_Script_Load_SQL" in dictionary 5261,
    	"DEMO2", MBS_Text_Field;
    
    if not empty(l_string) then
    	MBS_Text_Field = MBS_Text_Field + "and CUSTNMBR like " + SQL_FormatStrings(l_string+"%");
    end if;
    
    {clear MBS_Text_Field;
    MBS_Text_Field = MBS_Text_Field + "select * from table" + char (13);
    }call with name "MBS_SQL_Check_Exists" in dictionary 5261,
    	MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
    case MBS_Status
    	in [OKAY]
    {		warning MBS_Text_Field;
    }	in [MISSING]
    {		warning MBS_Text_Field;
    }	else
    		warning MBS_Text_Field;
    		abort script;
    end case;
    
    warning "Completed.";
    

    RuntimeScript

  4. You can use the Helper Button at the bottom of the window to insert Helper Functions at the current cursor location for loading and executing the SQL script. Variables needed will be automatically added to the top of the script.
    Helper1
    Helper2
  5. Use the Execute Button to test your script and ensure it works as desired.
  6. Click Save.

The script uses the getstring() function to obtain an input from the user. It then loads the display SQL script previously stored and adds an additional expression to the where clause based on the user input. The script then executes the SQL query and displays the returned data.

If data has been displayed then the ask() function is used to display a dialog asking if the user wishes to clear the Hold flags. If the response is Yes, the previously stored update SQL script is loaded, has its where clause adjusted and is executed. Finally, the warning command is used to tell the user the update has been completed.

Notes

  • You can change the dictionary context that the Dexterity sanScript code is executed against. Use Microsoft Dynamics GP unless you specifically need to access resources in another dictionary.
  • You can use Dexterity system dialogs such as getstring(), ask(), getfile(), savefile() error, and warning to provide a user interface.
  • You can also use Helper Functions to read values from any window in any dictionary.
  • Using Helper Functions to load and execute either SQL or Dexterity scripts allows for code re-use.
  • SQL scripts loaded via the Helper Functions can be modified prior to execution via a Helper Function. Note: Remember to delete or comment out the lines added which create a simple query when using the Execute SQL Select Statement Helper Function
  • There are also Helper Functions to Set, Get and Delete Parameters which can be used to pass information between the main script and called Dexterity scripts.
  • The example scripts above comments out the unwanted sections of code in the scripts inserted by the helper functions rather than deletes them. This is so you can see what the helper functions adds.

Stay tuned for the final part of the puzzle.

David

This article was originally posted on http://www.winthropdc.com/blog.

16-May-2015: Edits after comments from Rob.

Advertisements

5 thoughts on “Using the Support Debugging Tool to create user accessible SQL Scripts – Part 2

  1. When I first read the blog and inspected the sanscript code, I thought it strange you would have so large amounts of code commented out. It was only after actually using the Helper function in the Runtime Executewindow that I realised you had used some of the Helper function to build parts of the code illustrated. Those parts of the template code that was not needed was commented out rather than deleted. However this was only indirectly mentioned in the Notes section at the end.

    What is the last bullet point in the Notes meant to read?

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s