Problem:

The Stored Procedure Test Tool needed some enhancements to make it more functional and easier to use.

Resolution:

The following enhancements are available:

• New file name options for generating the input and output XML files
• Removing stored procedures from being listed and/or dropping stored procedures from the XDB location they were extracted from directly from the tool
• Generating a test command that can be used with other software products to test stored procedures
• Displaying a previously stored test results
• Displaying result sets in a separate tab

New options

There are new options that you enable on the Options dialog and you can modify the options to tailor how the new functions works.

• Character Delimiter to use around character data - you can generate the command string using either single quotes (APOST) or double quotes (QUOTE) to enclose character data in the command. Specify which format to use in the Character Delimiter to use around character data field in the Test Command section.
• Display Result Sets - you can change how results are displayed by setting the Display Result Sets option in the Execution section.

  Set the option to True to display in a separate tab any results that are produced by running the stored procedure.

• New options for Format Input Filename, Format Output Filename - enable you to set the formatting of the names of the default input and output XML file:
  
  • Format Input Filename – you can now select Stored procedure name plus number without IN.

    For example, if the stored procedure is called GETEMP, the name of the generated file is GETEMP_0001.xml.

  • Format Output Filename – you can now select Same name as input XML file except _OUT added.

    For example, if the input name is the same as in the previous example, the generated output file is called GETEMP_0001_OUT.xml.

New context menu commands:

The enhancements to the Stored Procedure Test Tool are implemented as new commands in the main context menu. To view them:

1. Place the cursor in any area other than the tab control.
2. Press the Windows menu key or, alternatively, right-click with the mouse.

   You see the following new commands in the context menu:

   
   
   • Edit List of Stored Procedures Displayed
   • Generate Test Command
   • Display Saved Results

• Edit List of Stored Procedures Displayed

  This command allows the user to customize which stored procedures are displayed in the tree view. If you have a large number of stored procedures that the tool displays, you can reduce the list to a more manageable size by using this function.

  You do this by checking each one of the stored procedures that you want to remove and clicking Remove.

  To take full advantage of this function, set Refresh List on Startup option to False. If this is not done, unless you delete the stored procedures from the XDB database, they will re-appear the next time you startup the tool or click Refresh.

  Also remove from database - if you check this, the tool will also generate a command file to drop the stored procedure from the XDB location they were extracted from. The drop command will be executed when you click Save using the file name you specified when the command file was generated.

• Generate Test Command

  Generate Test Command produces a text string that can be run with a third party software to test the stored procedure. To use it:

  
  
  1. Select the stored procedure for which you want to generate the command.
  2. Ensure all of the input parameters are specified.
  3. Choose Generate Test Command from the context menu.

     A dialog box containing the text string is displayed.

  4. You can select the text and paste it into the third party tool you want to use.

• Display Saved Results

  You do not always want to re-run a stored procedure to see the results. You can now select a stored procedure and use the Display Saved Results command. You receive a dialog that allows you to select the output XML file for which you want to display the results.

  Additionally, you can now display result sets in a separate tab. The information is re-formatted and displayed in a more familiar tabular format in which the columns are named after the column names used in the query that selected the data.

  The tab name becomes the SQL cursor name used to produce the result set. The text is displayed according to what result set this tab is for. If there are more than one result sets that can be produced you will know how many tabs can be displayed.

  Use Previous and Next to navigate through the result sets:

• Additional Changes

  When you select a stored procedure and click the Files tab, all of the default file names are now automatically filled in if you have configured the tool to automatically generate file names.

  You can override these names either by typing over the generated names or by browsing to the desired files.

Date:
 
Name:
 
Description of change: 
Date:
 
Name:
 
Description of change: