Archive

Posts Tagged ‘text’

Logging to debug MySQL

October 30, 2013 No comments

It’s common, when using more or less complex stored procedures, to make mistakes that make us waste much time, and sometimes we would like to see the value of a variable or the result of a query because it could tell us what’s going wrong.

This is a little help, an easy piece of code we can insert into our database and will let is write in a table what’s happening.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
DROP SCHEMA IF EXISTS `PBUtils` ;
CREATE SCHEMA IF NOT EXISTS `PBUtils` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE PBUtils;

DROP TABLE IF EXISTS `PBUtils`.`LOG`;
CREATE TABLE IF NOT EXISTS `PBUtils`.`LOG` (
   `Log_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
   `Log_date` DATETIME NOT NULL,
   `Log_description` varchar(128) NOT NULL,
   `Log_text` TEXT NULL,
   `Log_number` bigint NULL,
   `Log_float` double NULL,
   PRIMARY KEY(Log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logText` $$
CREATE PROCEDURE `PBUtils`.`logText`(
    IN  In_description VARCHAR(128),
    IN  In_text TEXT
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_text`) VALUES (NOW(), In_description, In_text);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logNumber` $$
CREATE PROCEDURE `PBUtils`.`logNumber`(
    IN  In_description VARCHAR(128),
    IN  In_number bigint
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_number`) VALUES (NOW(), In_description, In_number);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logFloat` $$
CREATE PROCEDURE `PBUtils`.`logFloat`(
    IN  In_description VARCHAR(128),
    IN  In_float double
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_float`) VALUES (NOW(), In_description, In_float);
END $$
DELIMITER ;

Now, what we have to do is insert our procedure to debug values, anywhere in our code:

1
PBUtils.logText('Text to describe', variable);
1
PBUtils.logNumber('Text to describe', variable);
1
PBUtils.logFloat('Text to describe', variable);

depending on what we want to record, a text, number or float

To see what has been written in our log:

1
SELECT * FROM PBUtils.LOG ORDER BY Log_data DESC
Top