TestingMisconfused

MySQL Stored Procedures

The SQLexec() WebMethod defined in the WebService allows us to call Stored Procedures, so we can go beyond simple SQL commands and exploit the performance of MySQL, rather than relying on client-side code to manipulate the data.

/*
    MySQL version (created in Catalogs section of MySQL Administrator)
*/
CREATE PROCEDURE `SimulatedTestRun`(
    runStarted DATETIME,
    testPC VARCHAR(50),
    testBuild VARCHAR(50),
    testCode INT,
    outputMatch TINYINT(1))
BEGIN
    INSERT INTO TestRun (RunStarted,TestPC,TestBuild) 
    VALUES (runStarted, testPC, testBuild);
    SELECT MAX(RunID) FROM TestRun INTO @LastRunID;
    INSERT INTO TestResults (TestCode,RunID,OutputMatch)
    VALUES (testCode,@LastRunID,outputMatch);
END
/*
MySQL version
*/
CREATE PROCEDURE `CleanSimulatedTestRun`()
BEGIN
    DELETE FROM TestRun WHERE (RunID > 1003);
    DELETE FROM TestResults WHERE (TestResultsID > 4);
END

jQuery

A few changes to the previous examples: no need to specify contentType or dataType, the POST parameter is coded differently and there is no .d property in the returned JSON:

//jQuery method to pass a select command to SQL
//(via the WebService)
//and format the JSON result as a table
//an optional callback is provided to allow any additional work after the table is constructed
//the HTML tables are contained in divs with ids the same as the SQL table names
function getSQLtable(TableName, fnCallback) {
    $.ajax({
	type: "POST",
        url: "../PHP/MYSQL2JSON.php",
        data: "SQLparm=* FROM " + TableName,
/*
	url: "/mono/SQLconnect/WebService1.asmx/SQLselect",
	data: "{SQLparm: '* FROM " + TableName + "'}",
	contentType: "application/json; charset=utf-8",
	dataType: "json",
*/
	error: function(msg){
	    $("#" + TableName).text("Sorry - can't fetch " +
		TableName + "  from the database")
	}, 
	success: function(msg) {
	    var strJSON = eval(msg);
	    var JSONtable = "<table><caption>" + TableName + "</caption>";
	    JSONtable += "<thead><tr>";
	    for (var field in strJSON[0]) {
		JSONtable += "<th>" + field + "</th>";
	    }
	    JSONtable += "</tr></thead>";
	    for (var i = 0, l = strJSON.length; i < l; i++) {
		JSONtable += "<tr>";
		var thisRecord = strJSON[i];
		for (var field in thisRecord) {
		    JSONtable += "<td>" + thisRecord[field] + "</td>";
		}
		JSONtable += "</tr>";
	    }
	    JSONtable += "</table>";
	    $("#" + TableName).html(JSONtable);
	    if (fnCallback){
		fnCallback();
	    }
	}
    });
}
//example uses
getSQLtable("TestBed");
getSQLtable("TestConditions");
//this passes an anonymous function as the optional callback to add a pushbutton to the table's caption
getSQLtable("TestRun ORDER BY RunID", function(){
    $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
});
//this scrolls the browser viewport so that the button that fetches
//the tables is scrolled to the top of the screen - to bring attention to the tables.
getSQLtable("TestResults", function(){
    $("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
    $("#lblSQL").fadeIn("slow");
    $("#frmSQL").removeClass("loading");
});

Calling a Stored Procedure is simpler as the parameters can be passed in as quoted strings (but note that there is a bit more work to do in the PHP above):

function generateTestRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
    $.ajax({//create new TestRun and TestResults records
	type: "POST",
        url: "../PHP/MYSQLexec.php",
        data: "SQLparm=SimulatedTestRun('" + rtRunStarted + "','" + rtTestPC +
                "','" + rtTestBuild + "'," + rtTestCode + "," + rtOutputMatch + ")",
/*
	url: "/mono/SQLconnect/WebService1.asmx/SQLexec",
	data: "{SQLparm: 'SimulatedTestRun #" + rtRunStarted + "#,#" + rtTestPC + "#,#" +
	    rtTestBuild + "#,#" + rtTestCode + "#,#" + rtOutputMatch + "#'}",
	contentType: "application/json; charset=utf-8",
*/        
	success: function() {
	    getSQLtable("TestRun", function(){
		$("#TestRun caption").append(
		    "<form>" +
		    "<input id='btnSTR' type='button' value='Simulate Test Run' />" +
		    "</form>");
	    });
	    getSQLtable("TestResults", function(){
		$("#TestRun tr:last td:eq(0)").addClass("highlight");
		$("#TestResults tr:last td:eq(0)").addClass("highlight");
		$("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
	    });
	}
    });
}
$("#btnSTR").live("click",function(){
    generateTestRun("10/19/2009 6:02:00","TestVista","1.1.0.1",100000,"False");    
    return false;
});

Note that I've changed the date delimiter to a hyphen - trying to pass slashes from JavaScript to PHP to MySQL is just asking for trouble...

Possible Enhancements

The length of the JSON string returned by the WebService needs to be set to a sensible limit (if you find the default inadequate) - but if you were expecting more than a screenful of results, you would page it, wouldn't you?...

Questions

You said this was for performance - why are you using eval() and <table>?

• Because I'm in control of the string being evaluated.
• Because it's tabular data - I'm not using tables for layout.

Well, it's not that fast, is it?

It's a webserver that cost less than a ton, sips electricity and makes do with resources that would just about have been impressive on a desktop 10 years ago - what more do you want?

Besides, it's tiny, look:

Under it is an external hard drive based on Cylon technology, allowing the plug to function as a household NAS, among other things.
The thing on top is a 50 pence piece to provide scale.