sqldbgr

Description

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.

UNIX platforms sqldbgr is located in $SYBASE/$SYBASE_ASE/bin.

Windows sqldbgr is located in %SYBASE%\%SYBASE_ASE%\bin.

Syntax

sqldbgr
	-U username
	-P password
	-S host:port

Parameters

-U username

specifies the user name. You must insert a space between -U and username.

-P password

specifies the user password. You must insert a space between -P and password.

-S host:port

specifies the machine name and the port number. You must insert a space between -S and host:port.

Examples

Example 1

Shows sqldbgr debugging stored procedures and triggers on host MERCURY:

$SYBASE/$SYBASE_ASE/bin/sqldbgr -U sa -P -S MERCURY: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)

Example 2

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/$SYBASE_OCS/bin/isql -U sa -P 
1> select @@spid
2> go
------
12
1>

$SYBASE/$SYBASE_ASE/bin/sqldbgr -U sa -P -S MERCURY: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) 

Usage

• The sql command is executed in the context of debugged task, while the mysql command is executed in the context of debugger task. Setting session-specific information, such as for set quoted_identifier on through sql does not work.

• By default, the Sybase jConnect JDBC driver uses set quoted_identifier on. Since the sqldbgr utility is built using jConnect arguments that need quotes, use single quotes instead of double quotes when entering options. For example, use sp_configure 'allow update' instead of sp_configure "allow update".

• Before you can run sqldbgr, you must set either the SYBASE_JRE or JAVA_HOME environments to the location containing the Java run environment.

• 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:

  Table 8-3: sqldbgr commands and their descriptions

  Command	Description
  attach spid	Attaches a task to sqldbgr when you are already logged in to Adaptive Server. Do not use attach spid to attach to a procedure that is not running. 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. sqldbgr does not support Java variables.
  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.
  sql 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. sql 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 8-4 lists all of sqldbgr’s error messages:

  Table 8-4: sqldbgr error messages and their meaning

  Error message	Description
  Cannot allocate resource in ASE	Indicates that Adaptive Server does not have sufficient memory resources to execute sqldbgr. Increase procedure cache size and restart sqldbgr.
  Cannot create Debugger handle in ASE	Indicates that Adaptive Server does not have sufficient memory resources to create a debugger handle. Increase procedure cache size and restart sqldbgr.
  The spid is invalid	Displays when you attempt to attach sqldbgr to an invalid spid. Double check the spid and try again.
  You cannot debug a task that is not owned by you	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.
  Spid is already being debugged	Displays when you execute attach spid and attempt to attach to a spid that is already being debugged.
  Spid is not debugged currently	Displays when you execute detach spid and attempt to detach from a spid that is not attached to sqldbgr.
  Invalid command	Displays when you enter an invalid command.
  Invalid procedure name	Displays when you enter an invalid procedure name in stop in procname.
  Invalid line number	Displays when you enter an invalid line number in stop in procname at line #.
  Variable not found	Displays when you enter an invalid variable in show @varname, show @varname at level #, or set @varname = VALUE.
  Illegal conversion attempted	Displays when you execute set @varname = VALUE and attempt to convert the variable to an invalid value.
  Conversion from text to datatype failed	Displays when set @varname = VALUE is unsuccessful.
  Cannot run a procedure while debugging another task	Displays if you use run procname while already debugging an existing task with attach spid.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF