Procedure and function debug with the help of dbForge Studio for MySQL
Starting from 5.0 version in MySql stored procedures, functions, triggers were supported. Since then all possibilities of the database increased, and MySql became more popular and flexible in usage. These possibilities let database developers to use maximum server logic database capabilities.
From time to time developers need to create compound procedures or functions, which during the developing can demand code debugging. As is well known in MySQL do not have a mechanism for debugging executable code. That’s why not all programs for working with MySql server can boast procedure and function debugger capability. One of such programs is dbForge Studio for MySQL, we will talk about it further, another one is MyDebugger.
A little bit about dbForge Studio for MySQL.
dbForge Studio for MySQL – it is a very powerful environment for work with the MySQL database, developed using .NET technology, differs with the comfortable Visual Studio-like user interface, with a lot of different capabilities, one of which is code debugger. In addition – there is a possibility to debug SQL scripts and even triggers!
A little bit about debugger engine.
dbForge Studio developers had to implement their own debugger engine. One of the most advanced debugger engine implementations was chosen. The chosen technology uses debugger support from server side – it’s automatically created cr_debug database, contained the necessary for work objects, in fact this database is the heart of the debugger. Before debugging procedures and functions are compiling with debugging information, herewith additional instructions which are necessary for engine support are embedded in the code; in further debugging information can be removed. It should be noted that all this happens easy and unnoticed for user, what is more important is that this engine guarantees exact debugger work, there are no disarrangements between actual values and those which the debugger shows.
We pass from words to deeds. Let’s create procedure (its code is shown below) and open it from Database Explorer. In the opened editor let’s go to the Text tab, where the procedure code is shown. To start debugging it is necessary to compile the procedure with debugging information – for this in Database Explorer for procedure the \’Compile for Debugging\’ (Ctrl+Alt+F7) command should be run, if it won’t be done then when you start debugging the program will offer to do it automatically. Then we can start to debug- let’s run \’Step Into\’ command (F11) to enter into debug mode, having input parameters (IN or INOUT) the window for entering the parameter values will be opened.
Pic of the window with parameter entering
At our disposal the all power of the code debugger, similar to the debugger Microsoft Visual Studio, code developers will appreciate it, everything is comfortable and familiar – user interface and tool windows and commands.
We have the next capabilities:
- We can set breakpoints. The Breakpoints window contains all breakpoints and allows controlling them: delete, disable, and move to breakpoints place.
- We can view the current variable values. The Watches window allows to add variables for value analyzing.
- There is a possibility to trace the call stack; it is comfortable and demonstrative when procedure calls another program. The Call Stack window shows the all chain of the procedure and function calls.
To step through the code, you can use the following commands:
- Step Into (F11) – is used for the step transition to the next statement, if the current one is a call of another procedure or function, then the debugger would step to the called stored routine.
- Step Over (F10) – transition to the next statement, herewith if the current statement is a call of another procedure or function, then the debugger only executes this stored routine and doesn’t enter it.
- Step Out (Shift+F11) – exit from the subprogram, returns the called program execution.
On the picture you can see an example of the debugger state with breakpoint and variables in the Watches window:
Pic of the debugger
After debugger finishing, when our procedure works correctly, we can clear the debugging information from the procedure, to do it we need to run the \’Compile\’ command (Ctrl+F7).
The additional information how to debug SQL scripts and triggers you can find in the product documentation.