Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Articles » Debugging procedures and functions using myDebugger for MySQL

Debugging procedures and functions using myDebugger for MySQL

Author: Darek Date: 5-04-2012, 21:26

 

Introduction.

 

Since version 5.0, MySQL server supports stored procedures and functions. Procedures and functions allow moving out database’s executable code. It allows reducing resources on communication between the database and application and even speeding up execution.

Sometimes you have to write more or less complicated procedures that in development may require code debugging. Despite the fact that the release version of MySQL 5.5 has been already released, a server-side code debugger still has not been implemented. Therefore, not many programs can boast of the ability to debug procedures and functions. I know two such tools - Debugger for MySQL that will are going to describe and dbForge Studio for MySQL.

 

 

Description of debugger and its capabilities.

 

Let’s review the capabilities of the Debugger for MySQL on an example. Run Debugger for MySQL and enter connection details. That’s it. Now you can start working.

 

Debugging procedures and functions using myDebugger for MySQL

 

We have the main menu, toolbar, main window of the debugger - 'Main', 'Schema Browser' window with the explorer tree of procedures and functions stored on the server, and additional windows: 'Breakpoints', 'Callstack', 'Watches', 'Local variables', 'Output'. All the windows can be moved or docked in the needed position.

Let’s assume we have a table `emp`, from which we want to get some data about an employer. The CREATE TABLE statement for the `emp` table is given in the appendix at the end of the article. Let’s enter the following code in the 'Main' window:

BEGIN
  SET @s = NULL;
  SET @d = NULL;
  SELECT sal, deptno INTO @s, @d FROM emp WHERE empno = emp_param;
  SELECT @s, @d;
END

To check code for errors, you have to perform the 'Compile (Ctrl+F9)' command. If there are no errors, it’s okay. Now let’s set a breakpoint on the second line and perform the 'Run (F9)' command after which additional commands become available. You can open the 'Watches' window and enter the @s and @d variables for analysis. The 'Step over (F8)' command moves the execution cursor to the next action in code and executes it. You can view the value of a variable in the 'Watches' window.

 

Debugging procedures and functions using myDebugger for MySQL

 

 

Creation of procedure and saving it on server.

 

The Debuger for MySQL allows creating new procedures and functions on a server. You just need to select the 'Create procedure...' or 'Create function...' command in the 'File' menu, enter a name, type new code of the procedure/function in the opened window, and save it by performing the 'Store routine (Ctrl+S)' command. Let’s try to create the `calc_distance` function based on the formulas in the 'Calculate distance, bearing and more between Latitude/Longitude points' article (http://www.movable-type.co.uk/scripts/latlong.html) to calculate a distance. To do this, select the 'Create function...' command, enter the name `calc_distance`, and in the editor window 'Main', enter the code:

CREATE FUNCTION calc_distance(lat1 FLOAT(10,6), lon1 FLOAT(10,6), lat2 FLOAT(10,6), lon2 FLOAT(10,6))
  RETURNS INT(11)
BEGIN
  DECLARE R INT DEFAULT 6371; -- // km
  DECLARE dLat FLOAT(10,6);
  DECLARE dLon FLOAT(10,6);
  DECLARE a, d, c FLOAT(10,6);
  DECLARE pi80 FLOAT(10, 6);
  
  SET pi80 = PI() / 180;
  SET dLat = (lat2-lat1) * pi80;
  SET dLon = (lon2-lon1) * pi80;
  SET lat1 = lat1 * pi80;
  SET lat2 = lat2 * pi80;
  SET a = SIN(dLat/2) * SIN(dLat/2) + SIN(dLon/2) * SIN(dLon/2) * COS(lat1) * COS(lat2); 
  SET c = 2 * ATAN2(SQRT(a), SQRT(1 - a)); 
  SET d = R * c;
  RETURN d;
END

To save a function on a server, perform the 'Store routine (Ctrl+S)' command. That’s it, our function was successfully saved.

 

 

Example of debugging procedure with parameters.

 

Let’s review the variant of stored function debugger with the input parameters (IN). How to set the input parameters with the debugger and to get a resulting value as a result? As an example, let’s use our function ‘calc_distance’, created in the previous section; let’s try to debug it in the Debugger for MySQL. Obviously to do it we need to execute a function and to set input parameters; also we should write code in which we would write sting with function calling, for example 'SELECT function1(10)' or 'CALL parocedure1()' if we would call a procedure.

 

Overall, we need to prepare an environment for debugger. And in this case Debugger for MySQL helps us, it’s enough in 'Schema browser' to allocate the needed function and to choose the command 'Configure environment for debug this routine', herewith all infrastructure will be created automatically.

 

In the window 'Main' we will get code with function calling, where we can set and count the parameter values and the window with function body. Herewith, one breakpoint automatically will be set at the beginning of the function body.

Lets’ input text coordinates for calculation of distance and set breakpoint in the calculation result string:

SET @lat1 = 48.730141;
SET @lon1 = 2.441246;
SET @lat2 = 52.475142;
SET @lon2 = 13.55941;

 

Debugging procedures and functions using myDebugger for MySQL

 

Also, we put breakpoint in the needed string of the function window `calc_distance` and run command 'Run (F9)', herewith at the beginning an initialization of the function executing (window 'Main') will be done and then by call stack we will get into calling function `calc_distance` and will stop on breakpoint, for visualization values of users variables which were set in the function are shown in the window 'Local variables': 

 

Debugging procedures and functions using myDebugger for MySQL

 

Then it’s up to a programmer, with the command help 'Step into (F7)' and 'Step over (F8)' we can move forward by code and analyze the way of execution. If our function would call another procedure or function, then in the command execution 'Step into (F7)' (for example, on statement CALL) the window with code of the calling procedure/function would open, and we would automatically move to code of its procedure/function.

As a result, after finishing of the function execution `calc_distance`, we get back to the window 'Main' where we can check the work results of function, analyzing the received meaning.

 

Debugging procedures and functions using myDebugger for MySQL

 

 

Appendix – code to create table `emp`.

 

CREATE TABLE emp (
  empno INT(11) NOT NULL,
  ename VARCHAR(10) DEFAULT NULL,
  job VARCHAR(9) DEFAULT NULL,
  mgr DECIMAL(4, 0) DEFAULT NULL,
  hiredate DATE DEFAULT NULL,
  sal DECIMAL(7, 2) DEFAULT NULL,
  comm DECIMAL(7, 2) DEFAULT NULL,
  deptno INT(11) DEFAULT NULL,
  PRIMARY KEY (empno)
);
INSERT INTO emp VALUES 
  (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
  (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
  (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
  (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
  (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
  (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
  (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
  (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, NULL, 20),
  (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
  (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
  (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.00, NULL, 20),
  (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
  (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
  (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.