The Advantage Query Engine supports a set of statements that allows the developer to debug SQL scripts. In addition to SQL scripts that are executed directly, scripts embedded in triggers, stored procedures, and user defined functions can be debugged as well. Advantage Data Architect’s interactive SQL debugger is implemented using this set of SQL statements. While, the information on specific "DEBUG …" statements will mostly be useful for developers who wish to implement their own SQL debugger, the generally information on the capability of the debugger should be useful for all users.
The SQL debug commands are available for both Advantage Local Server and Advantage Database Server. They can be used to debug a specific query, a specific connection or all connections to a specific database. The debugging process starts when a debugger session is initiated on a connection (debugger). The debugger can then designate one or more other connection(s) as debuggee, though the common scenario is that one debugger controls one debuggee. SQL statements or scripts executed on the debuggee connection(s) may be suspended or otherwise controlled by the debugger. The status of the debuggee(s) and the execution of the SQL script are reported to the debugger via the "::DEBUG_XXX" temporary tables.
With Advantage Local Server, the only connections that can be debugged are the ones in the same process (application) as the debugger and there can be only one default database debugger active, see DEBUG DATABASE.
With Advantage Database Server, a debugger with proper permissions can debug SQL scripts running on any connection and more than one default database debugger can be active as long as they are connected to different databases.
A limitation with Advantage Database Server is that the number of connections that may be debugged (debuggees) at the same time is limited by the number of configured worker threads. Specifically, the number of debuggees is limited to number of configured worker threads minus one. The reason for this limitation is that when the execution of the SQL script on a debuggee is suspended, a worker thread is suspended. Limiting the number of debuggees eliminates the possibility of locking up the server when all worker threads are suspended.