Stored Procedure Tester Tutorial – WPDB Power Tool WordPress Table Utility

Testing Stored Procedure Tutorial with the WPDB Power Tool

Testing Stored Procedure Tutorial is to walk you through the steps of testing your Stored Procedure prior to implementing into code. Ideally it can help you find the bugs you might run across before it is implemented (best laid plans of Mice and Men) into your code.

This tutorial follows the INSERT Stored Procedure Tutorial which is what we will use as our example Tutorial. To start click on the Stored Procedures Tab in the WPDB Power Tool. Now click on the stored procedure name sp_AddNewClient to load the procedure in the the Editor so we can see our Stored Procedure code as we test. Then click on the TEST button.

The following should now be displayed:

WPDB Power Tool Stored Procedure Tester — Our Stored Procedure sp_AddNewClient has three parameters. The tester displays the data type and the parameter definition.

Now we are ready to test! $wpdb->show_errors is turned on! Lets debug! To start with lets put a valid record into our table by entering something legitimate in each of the parameters and clicking on the Run Test button. On the first run you should get an affirmative response stating the client has been added. Now click on Run Test once more and verify the client was recognized. If the Stored Procedure was built as specified in the INSERT Stored Procedure tutorial you will have gotten back two different responses.

Now lets submit another test. This time lets leave out the first name. And see if the record gets inserted. Houston we have a problem! My record got inserted did yours? Looks like we have to add more code to our stored procedure. Since we noticed a blank firstName can get through it is safe to assume an empty clientWebsite and an empty clientEmail can get through. Lets fix that by testing each parameter at the start with the following code example:
DECLARE emptyFirstName INT DEFAULT 0;

Don’t forget you have two more variables to setup to make this test complete.

We can set each of these with the MySQL/MariaDB LENGTH and TRIM functions (hackers might put a space in there or someone might hit the space bar so might as well test for that too) like so:
SET emptyFirstName = LENGTH(TRIM(firstName));
If you want to see how LENGTH and TRIM work you can enter this into the Query Editor: SELECT LENGTH(TRIM(‘ TEST ‘));
Now do the same for the other two parameters.

You have been provided with the logic to use IF THEN in your stored procedure. This should be sufficient to complete this in order to build a procedure that verifies all the data we need to go into the database table. Try and solve it before looking at the completed stored procedure.

CREATE PROCEDURE sp_clients_Insert(IN firstName VARCHAR(64), IN clientEmail VARCHAR(255), 
                                   IN clientWebsite VARCHAR(255))
BEGIN
    # Description:
    #	INSERT new Client into my_clients testing for firstName, clientEmail and clientWebsite
    #   for valid values. Then testing table for row present for email passed in clientEmail.
    # Application:
    #	None
    # Function(s):
    #	None
    
    DECLARE rowCount INT DEFAULT 0;               # For testing rows with this user.
    DECLARE thisMoment DATETIME DEFAULT NOW();    # For setting created_date and updated_date
    DECLARE firstNameLength INT DEFAULT 0;        # For testing for a firstName value
    DECLARE emailLength INT DEFAULT 0;            # For test for a clientEmail value
    DECLARE websiteLength INT DEFAULT 0;          # For testing for a clientWebsite value 
    DECLARE hasAtSign INT DEFAULT 0;              # For testing for an @ sign
    DECLARE hasPeriod INT DEFAULT 0;              # For testing for a . 
    DECLARE output TEXT DEFAULT '';               # For results output

    SELECT LENGTH(TRIM(firstName)) INTO firstNameLength;
    IF firstNameLength = 0 THEN
        SET output = 'You must enter a first name.<br />';
    END IF;
        
    SELECT LENGTH(TRIM(clientEmail)) INTO emailLength;
    IF emailLength = 0 THEN
        SET output = CONCAT(output, 'You must enter a valid Email.<br />');
    ELSE
        SET hasAtSign = INSTR(clientEmail, '@');
        SET hasPeriod = INSTR(clientEmail, '.');
        IF hasAtSign = 0 OR hasPeriod = 0 THEN
            SET output = CONCAT(output, 'You must enter a valid Email.<br />');
        END IF;
    END IF;

    SELECT LENGTH(TRIM(clientWebsite)) INTO websiteLength;
    IF websiteLength = 0 THEN
        SET output = CONCAT(output, 'You must enter a valid website.<br />');
    ELSE
        SET hasPeriod = INSTR(clientWebsite, '.');
        IF hasPeriod = 0 THEN
            SET output = CONCAT(output, 'You must enter a valid website<br />');
        END IF;
    END IF;
    
    IF LENGTH(output) = 0 THEN
    	SELECT COUNT(*) INTO rowCount FROM my_clients WHERE LOWER(email) = LOWER(clientEmail);	
	IF rowCount > 0 THEN
	    SET output = CONCAT(firstName, 
		' it seems you are alreay a part of our services as the email ',
		clientEmail, ' is already present in our system.');
	ELSE
	    # All is good, valid data coming in no email matching incoming email.
	    INSERT INTO my_clients(first_name, email, notes, main_website, created_date, updated_date)
	    VALUES(firstName, clientEmail, 'Stored Procedures for INSERT simplifies so much code!',
	           clientWebsite, thisMoment, thisMoment);
	    SET output = CONCAT('Welcome ', firstname, ' you now have the benefits of all our services!<br />');
	    SET output = CONCAT(output, ' should you need to change your email you will need to wait till I',
			     ' know how to perform an UPDATE Stored Procedure! Hopefully it will not be long.');
	END IF;
    END IF;
	
    SELECT output;
END