Stored Procedure: Part I

I finally got my stored procedure to work. Its a dynamic select stement for MySQL. It also has an output so you can check the executed query. Since this is a relatively new feature for MySQL (introduced with version 5) its been hard to find any documentation on it. You need to use MySQLI to use the results a query would return using a stored procedure. Some of the stored procedure documentation out there is for other SQLs and servers. What is awesome about this is that it resides on the server. The applications simply use whatever native connection methodology and call this procedure. That way there is a unified layer of queries to the database and other applications can use them. Also, since its cached on the server box it makes for a much faster execution and it reduces some network traffic. Here is an example:

CREATE PROCEDURE p_getDBInfo(IN inputTable char(30), IN inputOrder char(10), IN inputColumn char(20), IN inputFrom INT, IN inputMax INT, IN inputName char(25), IN inputTag char(4), OUT outputQuery varchar(4000))
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""

BEGIN
    DECLARE query varchar(4000);
	DECLARE debug int; 

    SET debug = 1;
    SET outputQuery = "";
    SET @query = "SELECT Ranks, Names, Tags,
                  Territory, Ratings, Wins, Losses FROM";

    IF inputTable IS NOT NULL THEN
	SET @query = CONCAT(@query, " ", inputTable);
    ELSE
	SET @query = CONCAT(@query, " ladder_world");
    END IF;

    IF inputName IS NOT NULL || inputTag IS NOT NULL THEN
	SET @query = CONCAT(@query, " WHERE");
    END IF;

    IF inputName IS NOT NULL THEN
	SET @query = CONCAT(@query, " Names LIKE ", inputName);
    END IF; 

    IF inputName IS NOT NULL && inputTag IS NOT NULL THEN
	SET @query = CONCAT(@query, " AND");
    END IF;

    IF inputTag IS NOT NULL THEN
	SET @query = CONCAT(@query, " Tags LIKE ", inputTag);
    END IF; 

    IF inputColumn IS NOT NULL THEN
	SET @query = CONCAT(@query, " ORDER BY ", inputColumn);
    ELSE
	SET @query = CONCAT(@query, " ORDER BY Ranks");
    END IF; 

    IF inputOrder IS NOT NULL THEN
	SET @query = CONCAT(@query, " ", inputOrder);
    ELSE
	SET @query = CONCAT(@query, " ASC");
    END IF;

    IF inputFrom IS NOT NULL THEN
	SET @query = CONCAT(@query, " LIMIT ", inputFrom);
    ELSE
	SET @query = CONCAT(@query, " LIMIT 0");
    END IF;

    IF inputMax IS NOT NULL THEN
	SET @query = CONCAT(@query, ", ", inputMax);
    ELSE
	SET @query = CONCAT(@query, ", 20");
    END IF; 

    IF debug = 1 THEN
	SET outputQuery = @query;
    END IF;

    PREPARE pst FROM @query;
    EXECUTE pst;
    DEALLOCATE PREPARE pst;
END;

To execute this stored procedure with PHP 5 with MySQL 5 you would:

$link = mysqli_connect("localhost", "username", "password", "ladder");
    $query = mysqli_multi_query($link, "CALL p_getDBInfo(null, null, null, null, null, null, null, @a)") or die(mysql_error());
    if ($result = mysqli_store_result($link))
    {
	while ($row = mysqli_fetch_row($result)) {
		print_r($row);
        }
	mysqli_free_result($result);
    }

mysqli_close($link);

From what I’ve read the procedural language is very much like PASCAL but I wouldn’t know. I recognize the IF… END IF; from my computer science classes and stuff. Anyway, I need to rework the connection tools I put together. We’ll see.

This entry was posted in Programming. Bookmark the permalink.

One Response to Stored Procedure: Part I