Debugging a Sample Script with IDERA SQL Debugger for Microsoft

From RapidSQL
Jump to: navigation, search

Go Up to Tutorial Sessions

This Getting Started section demonstrates basic debugging functionality. You will debug two procedures using the IDERA SQL Debugger for Microsoft.

The section topics are designed to familiarize you with basic debugging features and functionality:

Getting Started with IDERA SQL Debugger for Microsoft

This part of Debugging the Sample Script explains how to create the following two procedures to be used for debugging:

  • check_modulo
  • calculate_sum_with_overflow_bug
Note: The procedure calculate_sum_with_overflow_bug intentionally includes a bug which prevents it from executing successfully. You will use the IDERA SQL Debugger for Microsoft to identify this bug.

The Getting Started section includes:

Creating Procedure 1

Procedure 1, check_modulo, calculates the modulo of any two user-specified numbers. The user passes the numbers into the procedure as input parameters. The procedure returns the result as an output parameter. If the modulo equals zero, procedure execution returns the output “YES”. If the modulo is not zero, procedure execution returns the output “NO”. This procedure is nested in the second procedure, calculate_sum_with_overflow_bug.

To create this procedure, connect to a MSSQL datasource, open a new SQL editor and, in the SQL editor, type or copy and paste the following code: 

CREATE PROCEDURE username.check_modulo
@p_dividend_in INT,
@p_divisor_in INT,
@result VARCHAR(3)OUTPUT
AS
IF @p_dividend_in % @p_divisor_in = 0
    SELECT @result = 'YES'
ELSE
SELECT @result = 'NO'
go
Note: For the purposes of this walk-through, this procedure was created under the user name Spence. Before executing the DDL above, substitute your user name for the word “username”.
  1. Connect to a Microsoft SQL Server datasource.
  2. On the Datasource menu, select the database node, and then select the target database.
    Note: For this walk-through, we recommend that you select a non-production database.
  3. On the Main toolbar, click New.
    OR
    Press CTRL+N.
    An SQL Editor opens in the current workspace.
  4. In the SQL Editor, type the DDL for procedure check_modulo.
    Note: Substitute your user name once in the DDL for this procedure.
  5. On the SQL Editor toolbar, click Execute.
    The script executes and creates Procedure 1, then opens the SQL Editor Results tab with the results of the script execution. If you were not able to create the procedure, check the error messages to determine the problem.

Creating Procedure 2

Procedure 2, calculate_sum_with_overflow_bug, requires two user-specified numbers as input parameters. Upon execution, the procedure calculates the sum of the all numbers divisible by five between the two user-specified numbers. This procedure calls sample procedure 1 (check_modulo) to calculate the modulo of the user-specified numbers.

Note: The procedure calculate_sum_with_overflow_bug intentionally includes a bug which prevents it from executing successfully. You will use the IDERA SQL Debugger for Microsoft to identify this bug.
Caution: When you input parameters, enter the smaller number in the @p_num1_in int box.

To create this procedure, connect to a MSSQL datasource, open a new SQL editor and, in the SQL editor, type or copy and paste the following code: 

CREATE PROCEDURE username.calculate_sum_with_overflow_bug
@p_num1_in INT,
@p_num2_in INT,
@result TINYINT OUTPUT
/*INT-Integer (whole number) data from -2^31 (-2,147,483,648)
  through 2^31 - 1 (2,147,483,647).
  TINYINT-Integer data from 0 through 255.*/
AS
DECLARE @temp INT
DECLARE @temp_1 INT
DECLARE @v_divisor INT
DECLARE @v_condition VARCHAR(3)

SET @temp = @p_num1_in
SET @temp_1 = 0
SET @v_divisor = 5
SET @v_condition = 'NO'

WHILE 1=1
BEGIN

     SELECT @temp = @temp + 1 /*Increase temp starting from p_num1*/

    IF @temp = @p_num2_in /*Check if we reached p_num2*/
        /*If yes, leave the LOOP*/
        BREAK

/*Call Procedure 2 to check if number is divisable by 5*/
EXEC username.check_modulo @temp,@v_divisor,@result=@v_condition output

   IF @v_condition = 'YES'
        SELECT @temp_1 = @temp_1 + @temp

END /*WHILE LOOP*/

SELECT @result = @temp_1

RETURN
go
Note: For the purposes of this walk-through, this procedure was created under the user name Spence. Before executing the DDL above, substitute your user name for the word “username”.

Creating Procedure 2

  1. Connect to a Microsoft SQL Server datasource.
  2. On the Datasource menu, select the database node, and then select the target database.
    Note: For this walk-through, we recommend that you select a non-production database.
  3. On the Main toolbar, click New.
    OR
    Press CTRL+N.
    An SQL Editor opens in the current workspace.
  4. In the SQL Editor, type the DDL for procedure calculate_sum_with_overflow_bug.
    Note: Substitute your user name twice in the DDL for this procedure.
  5. On the SQL Editor toolbar, click Execute.
    The script executes and creates Procedure 2, then opens the SQL Editor Results tab with the results of the script execution. If you were not able to create the procedure, check the error messages to determine the problem.

Confirming the Creation of the Procedures

After you create Procedure 1 and Procedure 2, you can confirm their creation in the Datasource Navigator.

Confirming the Creation of the Procedures

To confirm the creation of the procedures, do the following:

  1. On the Navigator tab, click the Navigatorlist, and then click Organize By Owner.
    The Navigator tab refreshes with the new display configuration.
  2. On the Navigator tab, double-click the Databases node, and then double-click the target database node.
    Tthe list of object owners is displayed.
  3. Double-click your user name to display a list of your objects.
  4. Double-click Procedures to display a list of procedures and confirm the creation of check_modulo and calculate_sum_with_overflow_bug.

Testing a Procedure

After you confirm the creation of the procedures, execute the procedure calculate_sum_with_overflow_bug (which includes a bug) to view its error message. This procedure requires two integer input parameters: @p_num1_in int and @p_num2_in int. For all integers between these two integers, this procedure identifies those divisible by 5, and then returns their sum.

Caution: When inputting parameters, enter the smaller number in the @p_num1_in int box.

Testing a Procedure

To test a procedure, do the following:

  1. On the Navigator tab, right-click calculate_sum_with_overflow_bug, and then click Execute.
    The Procedure Execution window opens.
  2. In the Value column of the @p_num1_in row, type 1.
  3. In the Value column of the @p_num2_in row, type 11.
  4. Click Execute.
    The procedure is compiled a Results tab opens, displaying the sum 15. There are two numbers between 1 and 11 that are divisible by 5: 5, and 10. The sum of these two numbers is 15.
  5. On the Navigator tab, right-click calculate_sum_with_overflow_bug, and then click Execute.
    The Procedure Execution window opens.
  6. In the Value column of the @p_num1_in row, type 100.
  7. In the Value column of the @p_num2_in row, type 121.
  8. On the Procedure Execution window toolbar, click Execute.
    The error returned states “Arithmetic overflow occurred”.

Starting the Debugging Session

After you test the procedure, open the procedure in IDERA SQL Debugger for Microsoft and enter input parameters before debugging.

To start the debugging session, do the following:

  1. On the Navigatortab, right-click the procedure, calculate_sum_with_overflow_bug, and then click Debug to start the debug session.
    The DDL for the procedure is extracted into a DDL Editor and opens the Procedure Execution dialog.
  2. In the Value column of the @p_num1_in row, type 100.
  3. In the Value column of the @p_num2_in row, type 121.
  4. Click Continue.
    The dialog box closes.

The IDERA SQL Debugger includes the following five windows:

Breakpoints

After you start the debugging session, insert a breakpoint into the code of the procedure calculate_sum_with_overflow_bug. Then run to the breakpoint. After you run to the breakpoint, IDERA SQL Debugger displays a yellow arrow on the red breakpoint icon and populates the Variables Window with values for the following variables:

Variable Value

@temp

Current number

@p_num2_in

Second input parameter

@p_num1_in

First input parameter

@temp_1

Sum of the numbers, between the input parameters, divisible by 5

@result

Condition of the output parameter

@v_condition

Output parameter

@v_divisor

Divisor

  1. In the DDL Editor, scroll to and click the following line:
    EXEC username.check_modulo @temp,@v_divisor,@result=@v_condition output
    Note: This line is located near the end of the procedure’s code.
  2. On the Debug menu, select Breakpoint.
    OR
    Press F9.
    A breakpoint (indicated by a dot) is inserted next to the number of the target line.
  3. On the Debug menu, select Go.
    OR
    Press F5.
    IDERA SQL Debugger for Microsoft displays the value of the variables before the breakpoint in the Variables Window.

Step Into

After setting the breakpoint, step into the dependent procedure, check_modulo.

To use the Step Into facility, do the following:

  1. On the Debug menu, select Step Into.
    OR
    Press F11.
    The DDL for the dependent, nested procedure is extracted into the DDL Editor.
  2. Step Into again.
    The next part of the code executes and displays the values for the variables in the Variables Window.
    The Call Stack Window displays calls to the procedures.

Step Out

After you Step Into the modulo_check (nested procedure) code, step back out and return to the calculate_sum_with_overflow_bug (outside procedure) code.

To use the Step Out facility, do the following:

  1. On the Debug menu, select Step Out.
    OR
    Press SHIFT+F11.
    The DDL Editor opens, containing the code for calculate_sum_with_overflow_bug.
  2. On the Debug menu, select Go.
    OR
    Press F5.
    When the value of the variable, @temp is equal to the value of the variable, @p_num2_in, the WHILE LOOP is complete and the IDERA SQL Debugger for Microsoft continues to the next executable statement in the code.
  3. While monitoring the value of the variables in the Variables Window, continue to click Go to cycle through the WHILE LOOP.
    After executing the SELECT and RETURN statements, the Debugger closes a DDL Editor opens to the Results tab.

Correcting the Script

When you finished Stepping Out of the nested code and encounter the error, to fully fix the bug, do the following:

  1. Locate the source of the error
  2. Scroll to the line in the script displaying the error
  3. Analyze the code
  4. Correct the error
  5. Compile the corrected script

When you first executed the procedure, the error message “Arithmetic overflow error for data type tinyint, value = 450” was displayed. According to Microsoft SQL Server Books Online: “This error occurs when an attempt is made to convert a float or real data type value into a data type that cannot store the result. This error prevents the operation from being completed.”

The data type used in this procedure (TINYINT) stores values from 0 to 255. The sum of the four numbers between 100 and 121 that are divisible by 5 (105, 110, 115, and 120) is 450. But because the TINYINT variable @result can only accept a maximum value of 255, the error message was returned and the procedure fails.

To correct the script, do the following:

  1. On the Navigatortab, right-click calculate_sum_with_overflow_bug, and then click Extract.
    Tthe DDL for the procedure is extracted into a DDL Editor.
  2. On the Edit toolbar, click Find.
    The Find dialog opens.
  3. In the Find What box, type TINYINT.
  4. Click Find Next.
    The first occurrence of TINYINT is selected.
  5. Change the data type for @result from TINYINT to INT.
  6. On the DDL Editor toolbar, click Execute to execute the modified script.
    The script executes and the Results tab opens.
  7. On the Navigator tab, right-click calculate_sum_with_overflow_bug, and then click Execute.
    The Procedure Execution dialog opens.
  8. In the Value column of the @p_num1_in row, type 100.
  9. In the Value column of the @p_num2_in row, type 121.
  10. Click Execute.
    The procedure executes with the new data type and opens the Results tab, returning the value 450. You successfully corrected the script and debugged the procedure.
Retrieved from "https://docwiki.embarcadero.com/RapidSQL/171/e/index.php?title=Debugging_a_Sample_Script_with_IDERA_SQL_Debugger_for_Microsoft&oldid=12461"