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) sqldbgr is located in $SYBASE/$SYBASE_ASE/bin.
(Windows) sqldbgr is located in %SYBASE%\%SYBASE_ASE%\bin.

You do not have the ability to view sqldbgr version strings.

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 7-4: **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 7-5 lists all sqldbgr error messages:

**Table 7-5: 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.