SQL debugging using stored procedures

• US 7,155,426 B2
• Filed: 09/20/2001
• Issued: 12/26/2006
• Est. Priority Date: 09/20/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Al-Azzawe, Abdul Hamid
• Primary Examiner(s): NGUYEN BA, HOANG VU A
• Application Number:
  US09/957,970
• Publication Number:
  US 20030066053A1
• Time in Patent Office: 1,923 Days
• Field of Search: 717124-127, 717114-116, 707 1- 5, 707/10, 707/104.1, 719/320, 715762-768, 715/961, 715/968
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3624 by performing operations on the source code, e.g. via a compiler
• CPC Class Codes: G06F 11/3636 by tracing the execution of the program
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 715/968 interface for database querying and retrieval

Abstract

A method, apparatus and article of manufacture is provided for SQL debugging within a computer system network. The method uses stored procedures via a console for debugging of SQL instructions located in a server, wherein preferably only one database communication line exists between the server and the console. The server has a database management system for retrieving data from a database stored in an electronic storage device coupled to the server. The method uses a debugger manager at the console for commanding and monitoring debugging operations of the server-side SQL instructions performed by a debugger engine, and uses stored procedures of a debugger router as a database communication interface for receiving commands and sending status reports between the debugger manager and the debugger engine.

Claims

1. A method for SQL debugging within a computer system network, using stored procedures via a console for debugging of SQL instructions located in a server having a database management system for retrieving data from a database stored in an electronic storage device coupled to the server, the method comprising the steps of:

(a) debugging of the server-side SQL instructions performed by a server-side debugger engine; and

(b) using stored procedures from a server-side debugger router as a database communication interface between the console and the debugger engine.

2. The method according to claim 1, wherein the stored procedures being located in the debugger router serving as database communication interface for receiving commands and sending status reports between the console and the debugger engine.

3. The method according to claim 1, wherein the SQL instructions being written in Persistent SQL Modules (PSM) language.

4. The method according to claim 1, wherein the SQL instructions being written in PL/SQL language.

5. The method according to claim 1, wherein the SQL instructions being written in TSQL language.

6. The method according to claim 1, wherein the method being used in a distributed computing environment wherein at least two computer systems being connected by the network, and the computer systems having computers of different type.

7. The method according to claim 1, wherein the stored procedures being provided for managing server network resources.

8. The method according to claim 1, wherein only nonproprietary database connections being required between the server and the console.

9. The method according to claim 1, wherein the SQL instructions being selected from a group comprising stored procedures, user defined functions and triggers.

10. The method according to claim 1, further comprising the step of invoking a database manager process for performing debugging in a fenced mode.

11. The method according to claim 1, wherein the debugging method providing call-stack tracking, line-by-line execution status reporting, line breakpoints management, variable change breakpoints management, and variable value reporting and modification capabilities.

12. The method according to claim 1, wherein the debugging being performed through debugger interface software instructions, inserted during compilation of the SQL instructions as debugger hooks, for tracing the execution of the SQL instructions.

13. The method according to claim 12, wherein the debugger interface software instructions including C Application Programming Interface (C API) calls as debugger hooks.

14. The method according to claim 13, wherein the inserted C API calls providing for each SQL instruction a source code line number for each SQL statement, names of variables declared by each SQL DECLARE statement, including SQL type information, names of variables modified by the SQL statement, a current SQL code and state, for each routine entry and exit information, and for each exception entry and exit information.

15. The method according to claim 1, wherein the database communication interface enabling the console to command and monitor debugging operations of the server-side SQL instructions performed by the debugger engine.

16. A SQL debugger system within a computer system network, using stored procedures via a console for debugging of SQL instructions located in a server having a database management system for retrieving data from a database stored in an electronic storage device coupled to the server, the system comprising:

a server-side debugger engine for debugging of the server-side SQL instructions; and

a server-side debugger router using stored procedures as a database communication interface between the console and the debugger engine.

17. The system according to claim 16, wherein the debugger router serving as a database communication interface for receiving commands and sending status reports between the console and the debugger engine.

18. The system according to claim 16, wherein the SQL instructions being written in Persistent SQL Modules (PSM) language.

19. The system according to claim 16, wherein the SQL instructions being written in PL/SQL language.

20. The system according to claim 16, wherein the SQL instructions being written in TSQL language.

21. The system according to claim 16, wherein the system being used in a distributed computing environment wherein at least two computer systems being connected by the network, and the computer systems having computers of different types.

22. The system according to claim 16, wherein the stored procedures being provided for managing server network resources.

23. The system according to claim 16, wherein only nonproprietary database connections being required between the server and the console.

24. The system according to claim 16, wherein the SQL instructions being selected from a group comprising stored procedures, user defined functions and triggers.

25. The system according to claim 16, further comprising a means for invoking a database manager process for performing debugging in a fenced mode.

26. The system according to claim 16, wherein the debugger system providing call-stack tracking, line-by-line execution status reporting, line breakpoints management, variable change breakpoints management, and variable value reporting and modification capabilities.

27. The system according to claim 16, wherein the debugging being performed through debugger interface software instructions, inserted during compilation of the SQL instructions as debugger hooks, for tracing the execution of the SQL instructions.

28. The system according to claim 27, wherein the debugger interface software instructions including C Application Programming Interface (C API) calls as debugger hooks.

29. The system according to claim 28, wherein the inserted C API calls providing for each SQL instructions a source code line number for each SQL statement, names of variables declared by each SQL DECLARE statement, including SQL type information, names of variables modified by the SQL statement, a current SQL code and state, for each routine entry and exit information, and for each exception entry and exit information.

30. A program storage device readable by a computer tangibly embodying a program of instructions executable by the computer to perform a SQL debugging method within a computer system network, using stored procedures via a console for debugging of SQL instructions located in a server having a database management system for retrieving data from a database stored in an electronic storage device coupled to the server, the method comprising the step of:

(a) debugging of the server-side SQL instructions performed by a server-side debugger engine; and

(b) using stored procedures from a server-side debugger router as a database communication interface between the console and the debugger engine.

31. The method according to claim 30, wherein the stored procedures being located in the debugger router serving as a database communication interface for receiving commands and sending status reports between the console and the debugger engine.

32. The method according to claim 30, wherein the SQL instructions being written in Persistent SQL Modules (PSM) language.

33. The method according to claim 30, wherein the SQL instructions being written in PL/SQL language.

34. The method according to claim 30, wherein the SQL instructions being written in TSQL language.

35. The method according to claim 30, wherein the method being used in a distributed computing environment wherein at least two computer systems being connected by the network, and the computer systems having computers of different type.

36. The method according to claim 30, wherein the stored procedures being provided for managing server network resources.

37. The method according to claim 30, wherein only nonproprietary database connections being required between the server and the console.

38. The method according to claim 30, wherein the SQL instructions being selected from a group comprising stored procedures, user defined functions and triggers.

39. The method according to claim 30, further comprising the step of invoking a database manager process for performing debugging in a fenced mode.

40. The method according to claim 30, wherein the debugging method providing call-stack tracking, line-by-line execution status reporting, line breakpoints management, variable change breakpoints management, and variable value reporting and modification capabilities.

41. The method according to claim 30, wherein the debugging being performed through debugger interface software instructions, inserted during compilation of the SQL instructions as debugger hooks, for tracing the execution of the SQL instructions.

42. The method according to claim 41, wherein the debugger interface software instructions including C Application Programming Interface (C API) calls as debugger hooks.

43. The method according to claim 42, wherein the inserted C API calls providing for each SQL instruction a source code line number for each SQL statement, names of variables declared by each SQL DECLARE statement, including SQL type information, names of variables modified by the SQL statement, a current SQL code and state, for each routine entry and exit information, and for each exception entry and exit information.

44. The method according to claim 30, wherein the database communication interface enabling the console to command and monitor debugging operations of the server-side SQL instructions performed by the debugger engine.