Lesson 2: Debugging a stored procedure

In this lesson, you learn how to use the debugger to identify errors in stored procedures. The SQL Anywhere sample database, demo.db, contains a stored procedure named debugger_tutorial, which contains a deliberate error. The debugger_tutorial procedure should return a result set that contains the name of the company that has placed the highest value of orders, and the value of their orders. It computes these values by looping over the result set of a query that lists companies and orders. (This result could be achieved without adding the logic into the procedure by using a SELECT FIRST query. The procedure is used to create a convenient example.) However, the bug contained in the debugger_tutorial procedure results in its failure to return the specified result set. In this lesson, you run the stored procedure and diagnose and fix the bug.

 Run the debugger_tutorial stored procedure

  1. In the left pane of Sybase Central, double-click Procedures & Functions.

  2. Right-click Debugger_Tutorial (GROUPO) and click Execute From Interactive SQL.

    Interactive SQL opens and the following result set appears:

    top_company top_value
    (NULL) (NULL)

    This is an incorrect result. The remainder of the tutorial diagnoses the error that produced this result.

  3. Close Interactive SQL.

To diagnose the bug in the procedure, set breakpoints in the procedure, beginning with the first executable statement. Step through the code, watching the value of variables as the procedure is executed.

 Diagnose the bug

  1. Click Mode » Debug.

  2. In the right pane, double-click Debugger_Tutorial (GROUPO).

  3. In the right pane, locate the following statement:

    OPEN cursor_this_customer;

  4. To add a breakpoint, click the vertical gray area to the left of the statement. The breakpoint appears as a red circle.

  5. In the left pane, right-click Debugger_Tutorial (GROUPO) and click Execute From Interactive SQL.

    In the right pane of Sybase Central, a yellow arrow appears on top of the red circle indicating the break point.

  6. In the Debugger Details window, click the Local tab to display a list of local variables in the procedure together with their current value and data type. The Top_Company, Top_Value, This_Value, and This_Company variables are all uninitialized and are therefore NULL.

  7. Press F11 to scroll through the procedure. The value of the variables changes when you reach the following line:

    IF SQLSTATE = error_not_found THEN

  8. Press F11 once more to determine which branch the execution takes. The yellow arrow moves back to the following text:

    customer_loop: loop

    The IF test did not return true. The test failed because a comparison of any value to NULL returns NULL. A value of NULL fails the test and the code inside the IF...END IF statement is not executed.

    At this point, you may realize that the problem is that Top_Value is not initialized.

You can test the hypothesis that the problem is the lack of initialization for Top_Value without changing the procedure code.

 Test the hypothesis

  1. In the Debugger Details window, click the Local tab.

  2. Click the Top_Value variable and type 3000 in the Value field and press Enter.

  3. Press F11 repeatedly until the Value field of the This_Value variable is greater than 3000.

  4. Click the breakpoint so that it turns gray.

  5. Press F5 to execute the procedure.

    The Interactive SQL window appears again. It shows the correct results.

    top_company top_value
    Chadwicks 8076

  6. Close Interactive SQL.

The hypothesis is confirmed. The problem is that the Top_Value is not initialized.

 Fix the bug

  1. Click Mode » Design.

  2. In the right pane, locate the following statement:

    OPEN cursor_this_customer;

  3. Type a new line underneath that initializes the Top_Value variable: 

    SET top_value = 0;

  4. Click File » Save.

  5. Execute the procedure again, and confirm that Interactive SQL displays the correct results.

You have now completed the lesson. Close any open Interactive SQL windows.

Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1