I've been working on a re-architecture of a project and I've been experimenting with using MySQL Stored Procedures for some of my business level logic. I'm using PHP 5.2.0, PHP's MySQLi Extension and MySQL 5.0, so stored procedures are new, but stable.

I also make use of the Qcodo ORM framework. Qcodo has support for both of the MySQL API's; Original Mysql and Mysqli. I created my first Stored Procedure using the following code:

<?php
--
-- Account Balance Stored Procedure
--

DROP PROCEDURE IF EXISTS Account_Balance;

DELIMITER |
CREATE PROCEDURE Account_Balance (IN account_id INT, IN before_date DATE)
BEGIN
    DECLARE acct_credit, acct_debit, balance INT;

    SELECT
        SUM(t.amount) INTO acct_credit
    FROM
        trxn AS t
    WHERE
        t.credit_account_id = account_id
      AND
        t.date <= before_date
    ORDER BY
        t.created DESC, t.trxn_id ASC;

    SELECT
        SUM(t.amount) INTO acct_debit
    FROM
        trxn AS t
    WHERE
        t.debit_account_id = account_id
      AND
        t.date <= before_date
    ORDER BY
        t.created DESC, t.trxn_id ASC;

    SET balance = acct_debit - acct_credit;
    SET balance = IFNULL(balance,0);

    SELECT balance;
END;
|
DELIMITER ;
?>

There are a lot of tutorials around the internet on how to write a stored procedure for MySQL, so I won't delve into that here. The link to the MySQL documentation on stored procedures is above. The most significant detail was that I chose to return my result, balance, using a result set rather then an OUT variable. The reason for not using an OUT variable was because that would require two queries. For example:

<?php
/* pseudo code */
mysqli_query("CALL Account_Balance(1,'2007-04-09',@balance);"); // No Result Returned
$result = mysqli_query("SELECT @balance;");
?>

Making two queries to get one value wasn't desirable for me. When I ran into problems using the result set return method, I even tested this method. It didn't work, so I went back to my result set method.

When I tested my stored procedure from the MySQL Command Line, it worked great.

mysql> CALL Account_Balance(1,'2007-04-09');
+---------+
| balance |
+---------+
|  117224 |
+---------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.05 sec)

The trouble started when I tried to run this procedure from my PHP code. The first call to my procedure works great, returns the balance and everything is great. However, the second call gives me a Commands out of sync; you can't run this command now error.

Here's a test script of what DIDN'T work.

<?php
$mysqli = new mysqli("localhost","**MyUser**","**MyPass**","**MyDB**");

/* check connection */
if (mysqli_connect_errno()) {
    printf("<br />Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* Call my stored procedure the first time */
if ($result = $mysqli->query("CALL Account_Balance(1,'2007-04-09');",MYSQLI_USE_RESULT)) {
    printf("<br />First select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}
else {
    printf("<br />First Error: %s\n", $mysqli->error);
}

/* Call my stored procedure the second time */
/* THIS WILL FAIL! */
if ($result = $mysqli->query("CALL Account_Balance(2,'2007-04-09');",MYSQLI_USE_RESULT)) {
    printf("<br />Second select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}
else {
    printf("<br />Second Error: %s\n", $mysqli->error);
}

$mysqli->close();
?>

The second call to the stored procedure will error out. I discovered that this is because in MySQL 5, stored procedures can possibly return multiple result sets. I found the documentation on how the PHP API deals with multiple result sets a little sparse, but I eventually got something to work.

The solution I discovered was to use mysqli_multi_query instead of mysqli_query.

<?php
$mysqli = new mysqli("localhost","**MyUser**","**MyPass**","**MyDB**");

/* check connection */
if (mysqli_connect_errno()) {
    printf("<br />Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* Call my stored procedure the first time */
if ($mysqli->multi_query("CALL Account_Balance(1,'2007-04-09');")) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}
else {
    printf("<br />First Error: %s\n", $mysqli->error);
}

/* Call my stored procedure the second time */
/* THIS TIME IT WORKS!!! */
if ($mysqli->multi_query("CALL Account_Balance(2,'2007-04-09');")) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}
else {
    printf("<br />Second Error: %s\n", $mysqli->error);
}

$mysqli->close();
?>

This time, the results are returned without the error. The main difference is the extra loop to check if more results are available, then retrieve them. After the result has been retrieved, you simply access the result set just like you would access a normal mysqli result set.

Now that I know it works, I'm definitely going to make more use of stored procedures. Qcodo 0.3.21 doesn't support the Multi-Query method. I've modified the QMySqli5Database.class.php to support Multi-Query and it works like a champ! I've attached the new database class file and have also submitted it to Mike Ho for inclusion in the next Qcodo release.

Here's the code to use the new Multi-Query functionality in Qcodo:

<?php
public static function AccountBalance($intAccountId, $objDate = NULL) {
    $objDatabase = Trxn::GetDatabase();

    if(is_null($objDate))
        $objDate = new QDateTime('Now');

    $strEnd = $objDate->PhpDate("Y-m-d");
    $strEnd = $objDatabase->SqlVariable($strEnd);
    $intAcctId = $objDatabase->SqlVariable($intAccountId);

    $strQuery = sprintf("CALL Account_Balance(%s,%s);",$intAcctId,$strEnd);
    $objResults = $objDatabase->MultiQuery($strQuery);

    /**
     * Because we may have multiple results, snag off the first
     * result object
     */
    $objResult = array_shift($objResults);
    $objDbRow = $objResult->FetchRow();

    $intBalance = QType::Cast(array_shift($objDbRow),QType::Integer);

    return $intBalance;
}
?>

AttachmentSize
QMySqli5Database.class.php.txt2.84 KB

»