Debugging Stored Procedures


In BBj 9.0 and higher, developers can interactively debug a stored procedure (SPROC). This feature can help track down problems in SPROCs without needing to add Java java.lang.System.out.println() calls to the SPROC.

Debugging is done via a SYSWINDOW that displays when the stored procedure is called from an BBj/ODBC/JDBC application.

Enabling SPROC Debugging

Set the following configuration settings to debug SPROCs via the BBj Enterprise Manager:

1. Enable stored procedure debugging by locating the “SPROC Debugging” checkbox on the database Information tab under the Misc sub-tab for the database in which debugging is to occur.  

2. Configure the SPROC to point to an existing config.bbx file.

3. Ensure that the config.bbx file has a SYSWINDOW alias defined:

    ALIAS T0 SYSWINDOW

4. Verify BBjServices settings is running in an environment that supports a graphical SYSWINDOW.

     a. If BBj is running on MS Windows, BBjServices must not be set to run as a service. This may be done two different ways:

          1. In the Admin, set BBjServices to run manually or automatically after log in.

          2. Stop the BBjServices Windows service, then run BBj manually with the -noservice command line switch:

            "C:\Program Files\Basis\bin\BBjServices.exe" -noservice

      b. If BBj is running under a UNIX-based operating system, BBjServices must not be running headless.

1. Ensure your UNIX-based server has a window environment available.

2. Modify the BBj.properties file in one of the following ways:

         a.  Remove the -Djava.awt.headless property line

         b.  Locate the -Djava.awt.headless property and set it to false

3.  Run BBjServices from the system console in the GUI console environment.

5. Modify the SPROC in preparation for debugging -- add an ESCAPE to dot-step, INPUT a parameter value, add a MSGBOX, PRINT the contents of a variable, or whatever is appropriate for debugging.

6. Call the SPROC as usual. When the stored procedure runs, an interpreter session will appear that allows user interaction. The interpreter session will return the results to the client only after a BYE or a RELEASE. If the SPROC code normally does an END, the calling program will not receive the data until you do a RELEASE in the SPROC’s interpreter session.

Note: The debug interpreter session will remain on the desktop until BBj does a garbage collection. Execute .gc in an interpreter session to request an immediate garbage collection to remove the interpreter sessions from the desktop.

Remember to remove all of the debug code from the SPROC before deploying it.