Debugging a Stored Procedure

This example demonstrates how to debug MySQL stored procedure by stepping into the stored procedure. It also illustrates different debugging techniques such as setting breakpoints, viewing data items, and so on.

Creating a Sample Procedure

In this topic, some sample database objects are used for demonstrating how to work with the Fusion for MySQL Debugger. Use scripts specified in this topic to create necessary objects in your test database.

Database Tables

Use the script available in Sample Script to Create Database Tables to create necessary tables.

Database Procedure

Use the script below to create the procedure that takes two input parameters: DateFrom and DateTo. Based on these input parameters the schedule_list table fills up with data.

CREATE PROCEDURE FillSchedule (IN DateFrom date
, IN DateTo date
, IN ScheduleId int)
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
 
  DECLARE DateOut date;
  DECLARE TemplateCount tinyint;
  DECLARE RowNumber tinyint;
  DECLARE TimeSheetDate date;
 
  SET
  TemplateCount = (SELECT
      COUNT(*)
    FROM `schedule` s
      JOIN scheduletemplatedetail sdt
        ON s.TemplateId = sdt.TemplateId
    WHERE s.ScheduleId = ScheduleId), DateOut = DateFrom
  , RowNumber = 0;
   SET TimeSheetDate = GetFirstDayOfMonth(DateFrom);
 
  DROP TABLE IF EXISTS enumdays;
  CREATE TEMPORARY TABLE enumdays (
    DayOrder tinyint,
    DateOut date,
    TimeSheetDate date
  );
 
  WHILE (DateOut <= DateTo) DO
 
    IF RowNumber = TemplateCount
      THEN
      SET RowNumber = 1;
    ELSE
      SET RowNumber := RowNumber + 1;
    END IF;
 
    INSERT INTO enumdays (DateOut
    , DayOrder
    , TimeSheetDate)
      SELECT
        DateOut,
        RowNumber,
        TimeSheetDate;
 
    SET DateOut := DATE_ADD(DateOut, INTERVAL 1 DAY);
    SET TimeSheetDate = GetFirstDayOfMonth(DateOut);
 
  END WHILE;
 
  DELETE
    FROM s
  USING scheduledetail s
  WHERE s.ScheduleId = ScheduleId
    AND s.DateOut BETWEEN DateFrom AND DateTo;
 
  INSERT INTO scheduledetail (DateOut
  , TimeSheetDate
  , ScheduleId
  , AbsenceCode)
    SELECT
      d.DateOut,
      d.TimeSheetDate,
      ScheduleId,
      s.AbsenceCode
    FROM enumdays d
      CROSS JOIN (SELECT
          s.TemplateId
        FROM `schedule` s
        WHERE s.ScheduleId = ScheduleId) s2
      JOIN scheduletemplatedetail s
        ON d.DayOrder = s.DayOrder
        AND s.TemplateId = s2.TemplateId;
 
END
$$

Debugging the Stored Procedure

Assume that you need to get Time Sheet for a specific time period. To do this, execute the FillSchedule procedure:

1. In Database Explorer, choose your test database.

2. Click Procedures and then right-click the FillSchedule procedure, and click Execute on the shortcut menu.

   

3. Enter input parameters.

   

4. Click OK.

Suddenly we get the error message telling us that the TimeSheetDate column cannot be null.

Note

Actual document look may slightly vary from the above image, depending on the product you use.

A quick look through the code gives you nothing and you have no other choice but to start the debugger.

Debugging Procedure

To start debugging the stored procedure:

1. To compile the stored procedure for debugging, navigate to the required procedure in Database Explorer, select Compile, and then click Compile Dependants for Debugging on the shortcut menu.
2. Click the Start Debug button on the Devart - Debug toolbar, or press CTRL+F5.

3. If you have not compiled the procedure, Fusion for MySQL prompts you to compile it. Click Yes.

4. Enter the stored procedure input parameters once again and click OK.

   You can see that the yellow arrow identifies the stack frame where the execution pointer is currently located.

   

5. Add the TimeSheetDate variable to Watches. Right-click the TimeSheetDate variable in the source code and then select the Add Watch option on the shortcut menu. Thus you will be able to track the TimeSheetDate value while debugging. Now you can see that the TimeSheetDate variable appears in the Watches window.

6. Insert a breakpoint in the place where you insert the TimeSheetDate variable in the enumdays temporary table.

   

7. Click the Step Into button on the Debug toolbar, or press F11 for stepping through the code.

8. After several clicks you are hitting, this breakpoint which means that the current code isn’t ignored.

   

   However, as you can see in the Watches window, the value of the TimeSheetDate variable is NULL. We know that the TimeSheetDate field cannot accept NULL values. So it can be concluded that the TimeSheetDate variable should be initialized.

9. Initialize the TimeSheetDate variable before inserting it into the table.

   

10. Restart the debugging process. To do this, click the Restart button on the Devart - Debug toolbar.
11. Repeat the steps 2-8.

12. Now, once you hit the breakpoint, you can see that the value of the TimeSheetDate variable is the date, which is absolutely correct.

    

13. To stop the debugging process, click the Stop button.
14. Click Execute to run the stored procedure.
15. You can see the message telling us that the stored procedure executed successfully. To verify this, you can retrieve the data from the table.
16. In Database Explorer, choose the required database.
17. Click Tables and then right-click the required table.

18. Click Retrieve Data on the shortcut menu.

    

19. Fusion for MySQL displays the result table.

    

As you see, the Debugger for MySQL is a very simple and powerful tool to use. You can use different debugging techniques to achieve successful results.

• Previous
• Next