This example shows how to debug a stored function in dbForge Studio for MySQL. Use provided scripts to create sample objects in your test database.
Use the script available in Sample Script to Create Database Tables to create necessary tables.
Use the script available in Debugging a Stored Procedure to the procedure used in the sample below.
Use the script below to create the GetFirstDayOfMonth function that returns the first day of a calendar month.
CREATE FUNCTION GetFirstDayOfMonth (`date` datetime) RETURNS datetime BEGIN RETURN DATE_ADD(DATE_ADD(LAST_DAY(`date`), INTERVAL - 1 MONTH), INTERVAL 1 DAY); END $$
To debug the MySQL function:
Click in the gray margin next to the SET TimeSheetDate statement to insert a breakpoint in the place where we call the GetFirstDayOfMonth stored function.
Add the CurrDate variable to the Watches window. This option allows you to track the value of the variable while stepping through the code. To add the variable to the Watches window, right-click the CurrDate variable, and then select Add Watch on the shortcut menu. The variable will appear in the Watches window.
Step through the code using the F11 key or the Step Into button, or press CTRL+F5 to move directly to the breakpoint.
Note when you are at the SET TimeSheetDate statement, the CurrDate variable changes its value from NULL to DATE.
Press F11 or click Step Into to get inside the GetFirstDayOfMonth stored function.
You may ignore stepping through the function by clicking the Step Over button. In this case, you will continue stepping through the stored procedure.
Step through the GetFirstDayOfMonth function until you exit back to the stored procedure, and continue to the end.
You may get back to the stored procedure code by clicking the Step Out button. In this case, you will continue stepping through the stored procedure.
Now, the value of the CurrDate variable does not appear. The reason is that the variable is declared in the FillSchedule procedure, and at the moment you are in a different context. You may add any variable to the Watches window from the current context to track its value.
You may click the FillSchedule procedure in the Call Stack window to get back to the parent code. Notice that the date variable now is unidentified unlike the CurrDate one.