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.
In this topic, some sample database objects are used for demonstrating how to work with the dbForge Studio Debugger. Use scripts specified in this topic to create necessary objects in your test database.
Use the script available in Sample Script to Create Database Tables to create necessary tables.
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 $$
Assume that you need to get Time Sheet for a specific time period. To do this, execute the FillSchedule procedure:
Click Procedures and then right-click the FillSchedule procedure, and click Execute on the shortcut menu.
Enter input parameters.
Suddenly we get the error message telling us that the TimeSheetDate column cannot be null.
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.
To start debugging the stored procedure:
For more information about compiling, read the Compiling Stored Procedures, Functions, and Triggers for Debugging section from the Debug Engine topic.
You can see that the yellow arrow identifies the stack frame where the execution pointer is currently located.
Insert a breakpoint in the place where you insert the TimeSheetDate variable in the enumdays temporary table.
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.
Initialize the TimeSheetDate variable before inserting it into the table.
Now, once you hit the breakpoint, you can see that the value of the TimeSheetDate variable is the date, which is absolutely correct.
Click Retrieve Data on the shortcut menu.
dbForge Studio 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.