sqldbgr is a command-line utility that debugs stored procedures and triggers. As with many source-level debuggers, you can:
attach sqldbgr to a task
set, enable, and disable breakpoints
step through a task one line at a time
step into and out of procedures
detach sqldbgr from stored procedures or triggers once the debugging is complete.
sqldbgr is located in $SYBASE/ASE-12_5/bin (%SYBASE%\ASE-12_5\bin in Windows NT).
sqldbgr -Uusername -Ppassword -Shostname : port_number
specifies the user name.
specifies the user password.
specifies the machine name and the port number.
This example shows sqldbgr debugging stored procedures and triggers on host MERCURY:
$SYBASE/ASE-12_5/bin/sqldbgr -Usa -P -SMERCURY:16896
(sqldbg) stop in sp_who Breakpoint moved to line 20 (sqldbg) run sp_who (sp_who::20)if @@trancount = 0 (sqldbg) next (sp_who::22) set chained off (sqldbg) cont fid spid status loginame origname hostname blk_spid dbname cmd block_xloid 0 2 sleeping NULL NULL 0 master NETWORK HANDLER 0 0 3 sleeping NULL NULL 0 master NETWORK HANDLER 0 0 4 sleeping NULL NULL 0 master DEADLOCK TUNE 0 0 5 sleeping NULL NULL 0 master MIRROR HANDLER 0 0 6 sleeping NULL NULL 0 master ASTC HANDLER 0 0 7 sleeping NULL NULL 0 master ASTC HANDLER 0 0 8 sleeping NULL NULL 0 master CHECKPOINT SLEEP 0 0 9 sleeping NULL NULL 0 master HOUSEKEEPER 0 0 10 running sa sa 0 master SELECT 0 0 11 sleeping sa sa (sqldbg) show breakpoints 1 stop in sp_who (sqldbg)
In this example, the System Administrator first logs in to Adaptive Server using isql, then starts sqldbgr from the command line to debug a stored procedure that is running in another task:
$SYBASE/OCS-12_5/bin/isql -U sa -P 1> select @@spid 2> go ------ 12 1>
$SYBASE/ASE-12_5/bin/sqldbgr -U sa -P -SMERCURY:16896
(sqldbg) attach 13 The spid is invalid (sqldbg) attach 12 (sqldbg) show breakpoints (sqldbg) stop in sp_who Breakpoint moved to line 20 (sqldbg) /* at this point run the sp_who procedure from spid 12 */ (sqldbg) where (sp_who::20::@loginname = <NULL>) (ADHOC::1::null) (sqldbg) next (sp_who::22) set chained off (sqldbg) next (sp_who::25)set transaction isolation level 1 (sqldbg) cont (sqldbg) /* at this point the sp_who result will show up in the isql screen */ (sqldbg) detach 12 (sqldbg)
When you invoke sqldbgr at the command prompt, the utility starts and the prompt changes to a sqldbgr prompt:
(sqldbgr)
Once you see the (sqldbgr) prompt,
you can enter the following sqldbgr commands
to perform your tasks:
Command |
Description |
|---|---|
attach spid |
Attaches a task to sqldbgr when you are already logged in to Adaptive Server.
sqldbgr cannot debug multiple tasks in the same session. If you try to attach the utility to multiple tasks, the first spid continues to be marked as attached. Since you cannot attach to a spid that is already attached, you must use the detach command, and then attach to another spid. |
run procname |
Debugs stored procedures and triggers without attaching sqldbgr to an existing task. If you attempt to use run procname while you are already debugging an existing task with attach spid, run procname fails and you see the following: Cannot run a procedure while debugging another task |
stop in procname [at line #] |
Sets a breakpoint to stop the stored procedure or trigger being debugged at the beginning of the specified procedure name. stop in procname at line # sets a breakpoint to stop the stored procedure or trigger being debugged at a designated line within the specified procedure. If you enter an invalid line number, sqldbgr moves the breakpoint to the next valid line number, and displays: Invalid line number You can also use this command to set multiple breakpoints. |
show breakpoints |
Displays the breakpoint handle in the form of a unique number, as well as the breakpoint statements given by the user during the sqldbgr session. If you specify a breakpoint line number that does not contain a valid SQL statement, Adaptive Server moves the breakpoint to the next valid line number. However, Adaptive Server does not change the command you entered. This is why show breakpoints can return a breakpoint handle and a breakpoint statement given during the sqldbgr session that can be different. An asterisk (*) in the breakpoint line indicates that the breakpoint is set, but currently disabled. |
use dbname |
Tells sqldbgr what database to use in order to debug that database’s stored procedures or triggers. |
show variables [at level #] show @varname [at level #] |
show variables displays all the variables and their values in the current SQL stored procedure or trigger. show variables at level # displays the variables and their values in the current SQL stored procedure or trigger at the specified level. show @varname displays the indicated variable and its value in the current SQL stored procedure or trigger. show @varname at level # displays the indicated variable and its value in the current SQL stored procedure or trigger at the specified level.
|
show where |
Displays the call stack of the stored procedures and triggers that exist in the task being debugged. |
step or next |
step or next instructs sqldbgr to move to the next statement in the current stored procedure or trigger. |
step into |
Instructs sqldbgr to move into a procedure if the current statement is an execute statement. If the current statement is an update, delete, or insert statement, and if there are triggers in it, step into instructs sqldbgr to move into the update, delete, or insert triggers. |
step out |
Instructs sqldbgr to move out of the current stored procedure or trigger, and to stop at the next line in the calling procedure. |
set @varname = VALUE |
Sets the value of the indicated variable to the variable value declared in the command in the current stored procedure or trigger. The values for the variables set using set @varname = VALUE are valid only for the current session sqldbgr. |
cont[inue] |
Instructs sqldbgr to continue debugging, and to stop at the next breakpoint (if any). |
delete # |
Deletes the indicated breakpoint set in the current instance of sqldbgr. |
enable # and disable # |
Enables the indicated breakpoints. disable # does the opposite. |
mysql any_sql_statement |
Executes ad hoc SQL statements. You can use this command to select and analyze data from temp tables created by the task being debugged. mysql any_sql_statement returns a result set and any errors that occurred. |
detach spid |
Detaches sqldbgr from the indicated spid, and releases the task being debugged. It deletes the breakpoints that were set for the task being debugged during the current sqldbgr session. |
help [all] |
Display sqldbgr commands. |
Table 6-5 lists all of sqldbgr’s error messages:
Error message |
Description |
|---|---|
|
Indicates that Adaptive Server does not have sufficient memory resources to execute sqldbgr. Increase procedure cache size and restart sqldbgr. |
|
Indicates that Adaptive Server does not have sufficient memory resources to create a debugger handle. Increase procedure cache size and restart sqldbgr. |
|
Displays when you attempt to attach sqldbgr to an invalid spid. Double check the spid and try again. |
|
Displays when you try to debug a task that you do not own. You must log in to the server as the owner of the task to be debugged. |
|
Displays when you execute attach spid and attempt to attach to a spid that is already being debugged. |
|
Displays when you execute detach spid and attempt to detach from a spid that is not attached to sqldbgr. |
|
Displays when you enter an invalid command. |
|
Displays when you enter an invalid procedure name in stop in procname. |
|
Displays when you enter an invalid line number in stop in procname at line #. |
|
Displays when you enter an invalid variable in show @varname, show @varname at level #, or set @varname = VALUE. |
|
Displays when you execute set @varname = VALUE and attempt to convert the variable to an invalid value. |
|
Displays when set @varname = VALUE is unsuccessful. |
|
Displays if you use run procname while already debugging an existing task with attach spid. |
Do not use attach spid to
attach to a procedure that is not running.