testdriven.com Wrangling quality out of chaos

Programmer/Tester collaboration example

04.14.2011 · Posted in Articles by David Vydra

Most testers of enterprise applications have some programming background, some even have degrees in computer science, but most of them would not qualify as professional programmers and that is the way it should be because great testers should be great analysts and competent in the skills of testing. Recently, a tester described to me how she was testing the migration from one DB vendor to another. She saved the result set of an SQL query, imported it into an editor and converted it into a query for the the target databases by wrapping each item in quotes and separating by a comma.

Something like:

joe1234
mary2234

INTO “SELECT * FROM rwm.Loginlog WHERE userName in(“joe1234”, “mary2234”);

This is certainly a reasonable test technique for this situation and the manual step of ‘generating’ the test SQL is not very difficult. Still, doing manual test preparation that does not require any thinking will take its toll on the tester and should be automated if affordable. In this case, the tester, though competent in SQL did not know how to automate the entire operation. I decided to give it a shot and time boxed it for an hour. I decided to try writing a stored procedure that generated the required SQL code. I have never seen a MySql stored procedure before, but with Google as my pair-programming partner, I was able to write it in about 45 minutes. (Caveat, there maybe a much more elegant way of doing it, but my code works and does the job)

File: p1.sql

DELIMITER |
DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT 0;

DECLARE colValue VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT userName FROM rwm.Loginlog;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @outsql = ‘SELECT * FROM rwm.loginLog WHERE userName in( ‘;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO colValue;
IF done THEN
LEAVE read_loop;
END IF;

SET @outsql = CONCAT(@outsql,'”‘,colValue,'”,’);
END LOOP;

CLOSE cur1;

SET @outsql = LEFT(@outsql, LENGTH(@outsql) – 1);
SET @outsql = CONCAT(@outsql, ‘);’);

END|
DELIMITER ;

And you install and run it like this:

source p1.sql
call p1();
select @outsql

With this code in hand, the tester can easily modify it to suite many similar situations without needing any more help from the programmer.

Elisabeth Hendrickson (http://twitter.com/testobsessed) suggested that saving results from both databases and comparing the files using unix tools is another time-tested approach and I am sure there are at least a dozen others. The moral of the story is that most programmers would love to help you avoid doing mindless, repetitive work – just ask them for help.

Comments are closed