( Pro Edition )
Database Workbench comes with a Stored Code Debugger that can handle Triggers, Stored Procedures, Stored Functions and Packages, depending on the DBMS used.
This topic describes the common usage for all these objects, when you start the Debugger, you can get a dialog, depending on the object properties. For more information, see:
Overview
Usually, the Debugger can be started by using context menus in the Database Navigator or Debug buttons in the object editors. Also see the object specific topics on how to reach the debugger.
When in the debugger main window, you will see something like in the screenshot below.
Stored Code Debugger
Depending on which database system you are using, not all Debug Windows might be available.
The following sections are available in this topic:
Each executable code-item will have a small blue dot in the gutter area. On these areas, you can set breakpoints - a place where the debugger will halt execution and return to the debugger. If you disable (right click the gutter area with a breakpoint or the breakpoint in the debug window), it will turn light green.
For some databases systems, breakpoints can be conditional: they will only "break" if a certain condition evaluates to "true", like "n => 4". You can put any condition in the Condition box if you right-click the breakpoint and select Properties - take in account that this expression should evaluate to TRUE or FALSE.
The Passes column lists how many times the code has passed by the breakpoint. If you modify the breakpoint properties and change the "passes" value, the breakpoint will break after the entered number of passes.
To set breakpoints in a sub-routine, you need to "open" the routine first, see viewing (sub)routines.
Parameters & Variables
In this window, you will see the current value of any parameters and variables used in the routine. These values will be updated after each statement.
The Parameters and Variables debug window
You can change the values of a parameter/variable by double clicking it. This will show the Evaluate/Modify Dialog - you can use this to view (the result of) expressions and/or change parameter and variable values.
If the debugger doesn't pick up a certain variable, you can select the variable name in the code window, use the context menu and select Add Variable.
'Watches" are expressions, variables or parameters that you are "watching" constantly. The Parameters and Variables have their own window, but with the Watches window, you can enter any valid expression and it will be evaluated (like in the Evaluate/Modify Dialog after every executed statement. Because of this, adding a large amount of watches will slow down the debugging process, but on a LAN, this should hardly be noticeable.
This dialog - use ctrl+F7 to show it - is a powerful dialog to view any valid expression or to modify existing parameters and variables. In order to modify a parameter or variable, enter the name of the parameter in the Expression edit box and use Evaluate - the dialog will show its current value. Next, enter a new value in the New Value box (use ' quotes around string values) and use the Modify button to modify the parameter. The Parameters and Variables window and Watches window will reflect the change as well.
The Evaluate/Modify dialog
If you have any text selected before opening the Evaluate/Modify dialog, this text will be selected as the Expression value.
You can also use the Add Watch button to create a "watch" of the current expression.
Viewing sub-routines
You can open sub-routines at new tabs by using the Open Routine for Debugging toolbar button, this will open up the Select Objects dialog.
Select Objects dialog
In this dialog you can select one or more objects that you would like to open up in the Debugger. You can also use the filter at the top of the dialog to shorten the list of available objects.
Running code and stepping through code
You can step a single statement by using the Step Over button. This will execute the statement and put the "current line" marker on the next statement. A blue highlighting bar will display the current line, as soon as you step to the next line, the current line will be executed and the next line becomes the current line.
At the top of the code window, you will see the current active routine. If you use Trace Into and you didn't open that routine yet, the debugger will attempt to open a new code window and continue stepping through the code in there. If the new routine is the same routine as an already opened routine, it will automatically switch tabs and switch back when the sub-routine has finished. Each routine will only be opened once in the debugger.
After each executed line, the values of the parameters and variables in the Parameters & Variables debug window will be updated. If you have watches enabled, these will be updated as well.
Other possibilities are simply using the Run button, which will execute the code until an error has occurred, the routine ends or an active breakpoint is reached. Also the Run To Cursor button will execute the code, until the line with the cursor is reached or passed (depending on the executable state of the line).
The Reset button clears all settings and values (like breakpoint passes) and aborts execution of the routine.
Testing routine results
By using the SQL button on the toolbar, you can start an SQL Editor that uses the same transaction as the debugger. With this SQL Editor, you can test your routine result (eg: INSERTs or DELETEs) before COMMITting or ROLLING back the transaction.