Como se usa Debug Database objects en sybase 7.0

Chapter 20
Debugging Logic in the Database
About this chapter
This chapter describes how to use the Sybase debugger to assist in developing Java classes, SQL stored procedures, triggers, and event handlers.
Introduction to debugging in the database
With Java in the database, you can add complex classes into your database. SQL stored procedures and triggers are other ways of adding logic to your database. The debugger lets you test these classes and procedures, and detect and fix problems with them.
This chapter describes how to set up and use the debugger.
Debugger features
You can carry out many tasks with the Sybase debugger, including the following:
? Debug Java classes You can debug Java classes that are stored in the database.
? Debug procedures and triggers You can debug SQL stored procedures and triggers.
?Debug event handlers Event handlers are an extension of SQL stored procedures. The material in this chapter about debugging stored procedures applies equally to debugging event handlers.
? Browse classes and stored procedures You can browse through the source code of installed classes and SQL procedures.
? Trace execution Step line by line through the code of a Java class or stored procedure running in the database. You can also look up and down the stack of functions that have been called.
?Set breakpoints Run the code until you hit a breakpoint, and stop at that point in the code.
?Set break conditions Breakpoints include lines of code, but you can also specify conditions when the code is to break. For example, you can stop at a line the tenth time it is executed, or only if a variable has a particular value. You can also stop whenever a particular exception is thrown in a Java application.
? Inspect and modify local variables When execution is stopped at a breakpoint, you can inspect the values of local variables and alter their value.
? Inspect and break on expressions When execution is stopped at a breakpoint, you can inspect the value of a wide variety of expressions.
? Inspect and modify row variables Row variables are the OLD and NEW values of row-level triggers. You can inspect and set these values.
? Execute queries When execution is stopped at a breakpoint in a SQL procedure, you can execute queries. This permits you to look at intermediate results held in temporary tables, as well as checking values in base tables.
Requirements for using the debugger
The debugger runs on a client machine, and connects to the database using the Sybase jConnect JDBC driver.
You need the following in order to use the debugger:
? Permissions In order to use the debugger, you must either have DBA authority or be granted permissions in the SA_DEBUG group. This group is added to all databases when the database is created.
? Source code The source code for your application must be available to the debugger. For Java classes, the source code is held on a directory on your hard disk. For stored procedures, the source code is held in the database.
?Compilation options To debug Java classes, they must be compiled so that they contain debugging information. For example, if you are using the Sun Microsystems JDK compiler javac.exe, they must be compiled using the -g command-line option.
? JConnect Support The database that you want to connect to must have jConnect support installed.
Debugger information
This chapter contains three tutorials to help you get started using the debugger. Task-based help and information about each window is available in the debugger online Help.
For more information on the debugger windows, see Debugger windows .
Tutorial 1: Connecting to a database
This tutorial shows you how to start the debugger, connect to a database, and attach to a connection for debugging.
Start the Java debugger
The debugger runs on your client machine.
You can start the debugger from the command line or from Sybase Central.
Ñ To start the Java debugger (Sybase Central):
1For the purposes of this tutorial, close down the sample database and all other Adaptive Server Anywhere applications.
2Start Sybase Central. Choose Tools® Adaptive Server Anywhere 7® Open Database Object Debugger.
Ñ To start the Java debugger (command line):
1For the purposes of this tutorial, close down the sample database and all other Adaptive Server Anywhere applications.
2From a system command prompt, change directory to the directory holding the Adaptive Server Anywhere software for your operating system.
3Enter the following command to start the debugger:
dbprdbg
The Connect window appears:
You can now connect to a database from the debugger.
Connect to the sample database
To connect to a database, you need to supply a user ID, and a password. This section describes by example how to connect.
Ñ To connect to the sample database from the debugger:
1Start a personal database server on the sample database. You can do this from the Start menu or by entering the following command at a system prompt:
dbeng7 -c 8M path\asademo.db
where path is your Adaptive Server Anywhere installation directory.
2In the debugger Login window, enter the following information:
? User Enter the user ID DBA.
? Password Enter the password SQL.
? User to debug Enter an asterisk (*) to indicate the you wish to debug connections for all users.
? Host Leave at localhost.
? Port Leave at 2638.
3Click OK to connect to the database. The debugger interface appears, displaying a list of stored procedures and triggers, and a list of Java classes installed in the database.
For more information on valid connection parameters for the debugger, see Supplying a URL for the server .
Tutorial 2: Debugging a stored procedure
This tutorial describes a sample session for debugging a stored procedure. It is a continuation of Tutorial 1: Connecting to a database .
In this tutorial, you call the stored procedure sp_customer_products, which is part of the sample database.
The sp_customer_products procedure executes the following query against the sample database:
CREATE PROCEDURE dba.sp_customer_products(
INOUT customer_id INTEGER )
RESULT(id integer,quantity_ordered integer)
BEGIN
SELECT product.id,sum(sales_order_items.quantity)
FROM product,sales_order_items,sales_order
WHERE sales_order.cust_id = customer_id
AND sales_order.id = sales_order_items.id
AND sales_order_items.prod_id = product.id
GROUP BY product.id
END
It takes a customer ID as input, and returns as a result set a list of product IDs and the number ordered by that customer.
Display stored procedure source code in the debugger
The stored procedure source code is stored in the database. You can display the stored procedure source code in the Source window.
Ñ To display stored procedure source code in the debugger:
?From the debugger interface, double-click the sp_customer_products stored procedure. The source code for the procedure appears in the Source window.
Set a breakpoint
You can set a breakpoint in the body of the sp_customer_products procedure. When the procedure is executed, execution stops at the breakpoint.
Ñ To set a breakpoint in a stored procedure:
1In the Source Code window, locate the line with the query:
select product.id,...
2Click the green indicator to the left of the line, until it is red.
Repeatedly clicking the indicator toggles its status.
Run the procedure
You can call the stored procedure from Interactive SQL, and see its execution interrupted at the breakpoint.
Ñ To call the procedure from Interactive SQL:
1Start Interactive SQL. Connect to the sample database with a user ID of DBA and a password of SQL.
The connection appears in the debugger Connections window list.
2Enter the following command in Interactive SQL to call the procedure using the customer with ID 122:
CALL sp_customer_products( 122 )
The query does not complete. Instead, execution is stopped in the debugger at the breakpoint. In Interactive SQL, the Interrupt the SQL Statement button is active. In the debugger Source window, the red arrow indicates the current line.
3Step to the next line by choosing Run® Step Over. You can also press F7.
For longer procedures, you can use other methods of stepping through code. For more examples, see Tutorial 3: Debugging a Java class .
For the next lesson, leave the debugger stopped at the SELECT line.
Inspect and modify variables
You can inspect the values of variables in the debugger.
Inspecting local variables
You can inspect the values of local variables in a procedure as you step through the code, to better understand what is happening.
Ñ To inspect and modify the value of a variable:
1If the Local Variables window is not displayed, choose Window® Local Variables to display it.
The Local Variables window shows that there are two local variables; the stored procedure itself (which does not have a return value, and so is listed as NULL) and the customer_id passed in to the procedure.
2In the Local Variables window, double-click the Value column entry for customer_id, and type in 125 to change the customer ID value used in the query.
3In the Source window, press F5 to complete the execution of the query and finish the tutorial.
The Interactive SQL Results window displays the list of product IDs and quantities for customer 125:
Id
sum(sales_order....
301
60
700
48
...
...
Inspecting trigger row variables
In addition to local variables, you can display other variables, such as row-level trigger OLD and NEW values in the debugger Row Variables window.
Tutorial 3: Debugging a Java class
This tutorial describes a sample session for debugging a stored procedure. It is a continuation of Tutorial 1: Connecting to a database .
In this tutorial, you call JDBCExamples.Query() from Interactive SQL, interrupt the execution in the debugger, and trace through the source code for this method.
The JDBCExamples.Query() method executes the following query against the sample database:
SELECT id, unit_price
FROM product
It then loops through all the rows of the result set, and returns the one with the highest unit price.
Compiling Java classes for debugging
You must compile classes with the javac -g option in order to debug them. The sample classes are compiled for debugging.
Prepare the database
If you intend to run Java examples, such as Tutorial 3: Debugging a Java class , you need to install the Java example classes into the sample database.
For more information about how to install the Java examples, see Setting up the Java examples .
For more information about the JDBCExamples class and its methods, see Data Access Using JDBC .
Display Java source code into the debugger
The debugger looks in a set of locations for source code files (with .java extension). You need to add the jxmp subdirectory of your installation directory to the list of locations, so that the code for the class currently being executed in the database is available to the debugger.
Ñ To display Java source code in the debugger:
1From the debugger interface, select File® Edit Source Path. The Source path window appears.
2Enter the path to the jxmp subdirectory of your Adaptive Server Anywhere installation directory. For example, if you installed Adaptive Server Anywhere in c:\asa7, you would enter the following:
c:\asa7\jxmp
3Click Apply, and close the window.
4In the Classes window, double-click JDBCExamples. The source code for the JDBCExamples class appears in the Source window.
Notes on locating Java source code
The Source Path window holds a list of directories in which the debugger looks for Java source code. Java rules for finding packages apply. The debugger also searches the current CLASSPATH for source code.
For example, if you add the paths c:\asa7\jxmp and c:\Java\src to the source path, and the debugger is trying to find a class called asademo.Product, it looks for the source code in c:\asa7\jxmp\asademo\Product.Java and c:\Java\src\my\ asademo\Product.Java
Set a breakpoint
You can set a breakpoint at the beginning of the Query() method. When the method is invoked, execution stops at the breakpoint.
Ñ To set a breakpoint in a Java class:
1In the Source Code window, page down until you see the beginning of the Query() method. This method is near the end of the class, and starts with the following line:
public static int Query() {
2Click the green indicator to the left of the first line of the method, until it is red. The first line of the method is:
int max_price = 0;
Repeatedly clicking the indicator toggles its status.
Run the method
You can invoke the Query() method from Interactive SQL, and see its execution interrupted at the breakpoint.
Ñ To invoke the method from Interactive SQL:
1Start Interactive SQL. Connect to the sample database as used ID DBA and password SQL.
The connection appears in the debugger Connections window list.
2Enter the following command in Interactive SQL to invoke the method:
SELECT JDBCExamples.Query()
The query does not complete. Instead, execution is stopped in the debugger at the breakpoint. In Interactive SQL, the Stop button is active. In the debugger Source window, the red arrow indicates the current line.
You can now step through source code and carry out debugging activities in the debugger.
Step through source code
In this section we illustrate some of the ways you can step through code in the debugger.
Following the previous section, the debugger should have stopped execution of JDBCExamples. Query() at the first statement in the method:
Examples
Here are some example steps you can try:
1.Step to the next line Choose Run® Step Over, or press F7 to step to the next line in the current method. Try this two or three times.
2.Run to a selected line Select the following line using the mouse, and choose Run® Run To Selected, or press F6 to run to that line and break:
max_price = price;
The red arrow moves to the line.
3.Set a breakpoint and execute to it Select the following line (line 292) and press F9 to set a breakpoint on that line:
return max_price;
An asterisk appears in the left hand column to mark the breakpoint. Press F5 to execute to that breakpoint.
4.Experiment Try different methods of stepping through the code. End with F5 to complete the execution.
When you have completed the execution, the Interactive SQL Data window displays the value 24.
Options
The complete set of options for stepping through source code are displayed on the Run menu. You can find more information in the debugger online Help.
Inspect and modify variables
You can inspect the values of both local variables (declared in a method) and class static variables in the debugger.
Inspecting local variables
You can inspect the values of local variables in a method as you step through the code, to better understand what is happening. You must have compiled the class with the javac -g option to do this.
Ñ To inspect and modify the value of a variable:
1Set a breakpoint at the first line of the JDBCExamples. Query method. This line is as follows:
int max_price = 0
2In Interactive SQL, enter the following statement again to execute the method:
SELECT JDBCExamples.Query()
The query executes only as far as the breakpoint.
3Press F7 to step to the next line. The max_price variable has now been declared and initialized to zero.
4If the Local Variables window is not displayed, choose Window® Local Variables to display it.
The Local Variables window shows that there are several local variables. The max_price variable has a value of zero. All others are listed as variable not in scope, which means they are not yet initialized.
5In the Local Variables window, double-click the Value column entry for max_price, and type in 45 to change the value of max_price to 45.
The value 45 is larger than any other price. Instead of returning 24, the query will now return 45 as the maximum price.
6In the Source window, press F7 repeatedly to step through the code. As you do so, the values of the variables appear in the Local Variables window. Step through until the stmt and result variables have values.
7Expand the result object by clicking the icon next to it, or setting the cursor on the line and pressing ENTER. This displays the values of the fields in the object.
8When you have experimented with inspecting and modifying variables, press F5 to complete the execution of the query and finish the tutorial.
Inspecting static variables
In addition to local variables, you can display class-level variables (static variables) in the debugger Statics window, and inspect their values in the Inspection window. For more information, see the debugger online Help.
Common debugger tasks
If you want to...
Then choose...
Add a directory to the Java source file search path
File® Add Source Path
Display callee's context
Stack® Down
Display caller's context
Stack® Up
Enable capturing of connections by the debugger
Connection® Enable capture
Exit the debugger
File® Exit
Find a string in the selected window
Search® Find
Ignore the case of a word when searching
Search® Ignore Case
Load the procedure debugger's settings from a file
Settings® Load From File
Login to the database as a new connection
Connection® Login
Log out of the database
Connection® Logout
Restart a program's execution
Run® Restart
Run a debug script
File® Run Script
For more information, see Writing debugger scripts
Run a program, line by line, going through procedures and triggers line by line as well
Run® Step Into
Run a program, line by line, switching between a Java program and stored procedures in other environments, when applicable
Run® Step Through
Run a program, line by line, without going through procedures and triggers line by line. If any procedure or trigger contains a breakpoint, the breakpoint will be ignored.
Run® Step Over
Run a program, or resume a program's execution from a breakpoint
Run® Go
Run a program until the current procedure/method returns.
Run® Step Out
Save procedures' breakpoints within procedures
Settings® Remember Breakpoints
Save the procedure debugger's settings
Settings® Save
Save the procedure debugger's settings to a file
Settings® Save to File
Save the procedure debugger's window positions, fonts, etc. automatically upon exiting the debugger
Settings® Save on Exit
Save the procedure debugger's window positions, fonts, etc. with settings.
Settings® Remember Windows Attributes
Specify the path containing the Java source file
File® Edit Source Path
View the source code for a procedure or class
File® Open
Writing debugger scripts
The debugger allows you to write scripts in the Java programming language. A script is a Java class that extends the sybase.asa.procdebug.DebugScript class .
When the debugger runs a script, it loads the class and calls its run method. The first parameter of the run method is a pointer to an instance of the sybase. Asa. Procdebug.IDebugAPI interface . This interface lets you interact with and control the debugger.
A debugger window is represented by the sybase. Asa. Procdebug. IDebugWindow interface .
You can compile scripts with a command such as the following:
javac -classpath "c:\Program Files\Sybase\SQL Anywhere 7\java\ProcDebug.jar";%classpath% myScript.Java.
sybase.asa.procdebug.DebugScript class
You can write scripts to control debugger behavior. Scripts are classes that extend the DebugScript class. For more information on scripts, see Writing debugger scripts .
The DebugScript class is as follows:
// All debug scripts must inherit from this class
package sybase.asa.procdebug;
abstract public class DebugScript
{
abstract public void run( IDebugAPI db, String args[] );
/*
The run method is called by the debugger
- args will contain command line arguments
*/
public void OnEvent( int event ) throws DebugError {}
/*
- Override the following methods to process debug events
- NOTE: this method will not be called unless you call
DebugAPI.AddEventHandler( this );
*/
}
sybase.asa.procdebug.IDebugAPI interface
You can write scripts to control debugger behavior. Scripts are Java classes that use the IDebugAPI interface to control the debugger. For more information on scripts, see Writing debugger scripts .
The IDebugAPI interfaces is as follows:
package sybase.asa.procdebug;
import java.util.*;
public interface IDebugAPI
{
// Simulate Menu Items
IDebugWindow MenuOpenSourceWindow() throws DebugError;
IDebugWindow MenuOpenCallsWindow() throws DebugError;
IDebugWindow MenuOpenClassesWindow() throws DebugError;
IDebugWindow MenuOpenClassListWindow() throws DebugError;
IDebugWindow MenuOpenMethodsWindow() throws DebugError;
IDebugWindow MenuOpenStaticsWindow() throws DebugError;
IDebugWindow MenuOpenCatchWindow() throws DebugError;
IDebugWindow MenuOpenProcWindow() throws DebugError;
IDebugWindow MenuOpenOutputWindow() throws DebugError;
IDebugWindow MenuOpenBreakWindow() throws DebugError;
IDebugWindow MenuOpenLocalsWindow() throws DebugError;
IDebugWindow MenuOpenInspectWindow() throws DebugError;
IDebugWindow MenuOpenRowVarWindow() throws DebugError;
IDebugWindow MenuOpenQueryWindow() throws DebugError;
IDebugWindow MenuOpenEvaluateWindow() throws DebugError;
IDebugWindow MenuOpenGlobalsWindow() throws DebugError;
IDebugWindow MenuOpenConnectionWindow() throws DebugError;
IDebugWindow MenuOpenThreadsWindow() throws DebugError;
IDebugWindow GetWindow( String name ) throws DebugError;
void MenuRunRestart() throws DebugError;
void MenuRunHome() throws DebugError;
void MenuRunGo() throws DebugError;
void MenuRunToCursor() throws DebugError;
void MenuRunInterrupt() throws DebugError;
void MenuRunOver() throws DebugError;
void MenuRunInto() throws DebugError;
void MenuRunIntoSpecial() throws DebugError;
void MenuRunOut() throws DebugError;
void MenuStackUp() throws DebugError;
void MenuStackDown() throws DebugError;
void MenuStackBottom() throws DebugError;
void MenuFileExit() throws DebugError;
void MenuFileOpen( String name ) throws DebugError;
void MenuFileAddSourcePath( String what ) throws DebugError;
void MenuSettingsLoadState( String file ) throws DebugError;
void MenuSettingsSaveState( String file ) throws DebugError;
void MenuWindowTile() throws DebugError;
void MenuWindowCascade() throws DebugError;
void MenuWindowRefresh() throws DebugError;
void MenuHelpWindow() throws DebugError;
void MenuHelpContents() throws DebugError;
void MenuHelpIndex() throws DebugError;
void MenuHelpAbout() throws DebugError;
void MenuBreakAtCursor() throws DebugError;
void MenuBreakClearAll() throws DebugError;
void MenuBreakEnableAll() throws DebugError;
void MenuBreakDisableAll() throws DebugError;
void MenuSearchFind( IDebugWindow w, String what ) throws DebugError;
void MenuSearchNext( IDebugWindow w ) throws DebugError;
void MenuSearchPrev( IDebugWindow w ) throws DebugError;
void MenuConnectionLogin() throws DebugError;
void MenuConnectionReleaseSelected() throws DebugError;
// output window
void OutputClear();
void OutputLine( String line );
void OutputLineNoUpdate( String line );
void OutputUpdate();
// Java source search path
void SetSourcePath( String path ) throws DebugError;
String GetSourcePath() throws DebugError;
// Catch java exceptions
Vector GetCatching();
void Catch( boolean on, String name ) throws DebugError;
// Database connections
int ConnectionCount();
void ConnectionRelease( int index );
void ConnectionAttach( int index );
String ConnectionName( int index );
void ConnectionSelect( int index );
// Login to database
boolean LoggedIn();
void Login( String url, String userId, String password, String userToDebug ) throws DebugError;
void Logout();
// Simulate keyboard/mouse actions
void DeleteItemAt( IDebugWindow w, int row ) throws DebugError;
void DoubleClickOn( IDebugWindow w, int row ) throws DebugError;
// Breakpoints
Object BreakSet( String where ) throws DebugError;
void BreakClear( Object b ) throws DebugError;
void BreakEnable( Object b, boolean enabled ) throws DebugError;
void BreakSetCount( Object b, int count ) throws DebugError;
int BreakGetCount( Object b ) throws DebugError;
void BreakSetCondition( Object b, String condition ) throws DebugError;
String BreakGetCondition( Object b ) throws DebugError;
Vector GetBreaks() throws DebugError;
// Scripting
void RunScript( String args[] ) throws DebugError;
void AddEventHandler( DebugScript s );
void RemoveEventHandler( DebugScript s );
// Miscellaneous
void EvalRun( String expr ) throws DebugError;
void QueryRun( String query ) throws DebugError;
void QueryMoreRows() throws DebugError;
Vector GetClassNames();
Vector GetProcedureNames();
Vector WindowContents( IDebugWindow window ) throws DebugError;
boolean AtBreak();
boolean IsRunning();
boolean AtStackTop();
boolean AtStackBottom();
void SetStatusText( String msg );
String GetStatusText();
void WaitCursor();
void OldCursor();
void Error( Exception x );
void Error( String msg );
void Warning( String msg );
String Ask( String title );
boolean MenuIsChecked( String cmd );
void MenuSetChecked( String cmd, boolean on );
void AddInspectItem( String s ) throws DebugError;
// Constants for DebugScript.OnEvent parameter
public static final int EventBreak = 0;
public static final int EventTerminate = 1;
public static final int EventStep = 2;
public static final int EventInterrupt = 3;
public static final int EventException = 4;
public static final int EventConnect = 5;
};
sybase.asa.procdebug.IDebugWindow interface
You can write scripts to control debugger behavior. In scripts, the debugger window is represented by the IDebugWindow interface. For more information on scripts, see Writing debugger scripts .
The IDebugWindow interfaces is as follows:
// this interface represents a debugger window
package sybase.asa.procdebug;
public interface IDebugWindow
{
public int GetSelected();
/*
get the currently selected row, or -1 if no selection
*/
public boolean SetSelected( int i );
/*
set the currently selected row. Ignored if i < 0 or i > #rows
*/
public String StringAt( int row );
/*
get the String representation of the Nth row of the window. Returns null if row > # rows
*/
public java.awt.Rectangle GetPosition();
public void SetPosition( java.awt.Rectangle r );
/*
get/set the windows position within the frame
*/
public void Close();
/*
Close (destroy) the window
*/
}