Advantage Developer Zone

 
 
 

Debugging SQL Scripts

Tuesday, March 03, 2009

Support for SQL Scripts was added with version 8 of Advantage Database Server and now with version 9 we have added SQL Script Debugging. This functionality is a combination of server APIs and Advantage Data Architect integration. The SQL Debugger allows for setting break points, stepping through statements, stack frame inspection and a watch window for variable inspection. The debugger allows for more efficient creation and testing of SQL Scripts.

The SQL Utility

The SQL Utility window has been improved in version 9; it now includes the debugger as well as a tabbed view to allow multiple SQL scripts to be opened in a single window. There is also a new button and keyboard shortcut (CTRL-F5) which runs a script in debug mode. A comparison of the two versions is shown below:


Advantage Data Architect version 8.x


Advantage Data Architect version 9.x

Debugging SQL Scripts

There are several ways to debug/test SQL scripts. First, the script can be loaded into the SQL Utility and run in a debug mode by pressing the debug run button or CTRL-F5. This runs the script in the debugger and will stop on the first breakpoint. You can also right-click on a SQL stored procedure and choose debug/test from the context menu.

Choosing this option will open the SQL Utility with a script that contains an EXECUTE PROCEDURE statement with all of the input variables displayed as named parameters. This script can be modified and it will be automatically saved so your changes will be used the next time you select debug/test. When the test script is run the stored procedure is opened in a new tab and it will stop on the first executable SQL statement, DECLARE statements are skipped. This also changes the SQL Utility window into the debug view which includes a Call Stack and Variables toolbars.

All of the toolbars within the SQL Debugging view can be moved around and docked within the window. This is similar to the way you can dock and move toolbars within Visual Studio. Along the bottom of the debug view are the information windows which show any messages, data, breakpoints and connection information. The connection status shows all open connections to the server and their status. When a breakpoint is active the connection will display its suspended state.

You can move through the SQL script using standard Step Into (F11), Step Over (F10) and Step Out (Shift-F11) commands. The shortcut key assignments can be changed by choosing SQL->Options and going to the Key Assignments tab.

The SQL Debugger includes a call stack which allows you to debug multiple SQL scripts within the tool. As additional stored procedures, triggers and UDFs are executed from a script the code will be opened in the SQL Utility. This allows you to step into other scripts from the calling script and observe the call stack. You can navigate through any of the items on the call stack by simply double-clicking on the script you wish to view.

In order to debug scripts you must be a member of the DB:Admin or DB:Debug groups. The adssys account always has full debug permissions.

Setting Breakpoints

You can set a breakpoint on a specific line by clicking in the gutter of the editor tab, simply click on the breakpoint to remove it. You can also toggle breakpoints on the currently selected line by pressing the F9 key. Breakpoints can also be set in the breakpoints toolbar. The New button sets a breakpoint on the current line or can create an object breakpoint (discussed below). The Delete button deletes the breakpoint selected in the Breakpoints toolbar (see below)


You can also specify an Object Breakpoint which will automatically stop at the first line in the specified object. These objects include SQL Triggers, SQL Stored Procedures and SQL user defined functions (UDFs). When the object breakpoint is set, any time these objects are called a debug session will be started.

An object breakpoint is automatically set when you choose debug/test on an SQL Stored procedure. You can set your own object breakpoints by choosing SQL->Debugging->Set Breakpoint at Object or by pressing CTRL+B. This brings up a dialog which allows you to choose an object from the current database. Finally, you can set an object breakpoint from the Breakpoints toolbar as mentioned above.

Summary

SQL Debugging provides an excellent tool for creating and testing your SQL Scripts. A complete debugging tool, which includes a stack trace, variable watch, step in, step out and step over functionality, is included in Advantage Data Architect. For a complete demonstration of SQL Debugging you can view the Advantage SQL Debugger screencast