I may have mentioned before, I do rather like SQL and stored procedures.  They let you work with large datasets, quickly, nice and close to the server, in a naturally secure fashion.

But how can you make them easy and seamless to work with?

Here I will show you a few tricks which will let you call your database API, return results, handle exceptions and even do some logging in the easiest, laziest ways possible. 

First off, call-time integration.

Classically, the way you would normally call a Stored Procedure is very similar to executing any SQL statement from PHP:

$sql = "call spSomeProc(?, ?, ?)"

$s = $db->prepare($sql);

$res = $s->execute(array(1, 2, 3));

(if $res is ok, start fetching result sets...)

It's a bit laborious, isn't it.

Let's start with abstracting this bit:  Create a class to contain this code, call it "SPWrapper".  Make it a Singleton so you can access the same instance from anywhere.  This is important, because its Constructor will be capable to connecting to the DB, but we only want a single DB connection.

And now the magic - PHP has what it calls a "magic method" called "__call".  Create a public function called __call.

The first argument to __call will be the name of the function that was actually called.  Use func_get_args to get all variable arguments.  Shift the first item from the array in to $procName.

Now build a "call query" like the above, prepare, execute etc.  If $res returns anything but true, find the last error message from the server and throw an Exception with it.

So, now you have API integration and can run any Proc like this:

$sp = SPWrapper::getInstance();

$rSet = $sp->spSomethingDoSomething($a, $b, $c);

Nice.

What about user-defined errors? 

Sometimes, you will want to check certain things are correct inside a stored proc and gracefully refuse to cooperate if all is not well.  There are limited ways to return errors from MySQL, and fewer ways to trap those errors.  So let's stick with what we know and what we can do - returning result sets:

Arrange that when your __call function has received a results set from a proc, it checks its length and columns.  If that results set is a single row consisting of an "ErrCode" and "ErrMessage", throw an Exception with that code and message.

This does, however, take a bit of work inside the proc.  Not much work, but adhering to this design pattern:

create procedure spSomethingDoSomething(a int, b int, c int)

begin

declare errCode int default null;  declare errMsg varchar(512) default null;

codeblock: 

 begin

  ....

  ....

  if (some condition not met) then

  begin  

    set errCode = 45;  set errMsg = "Some bad error"

    leave codeblock;

  end if;

end;

call spErrHand(errCode, errMsg);

end

So any time you detect an error condition, a simple setting of the code/message and leaving the block will create an exception in PHP which will bubble all the way up through your app until properly handled.

You may be wondering what spErrHand does:  It is a convenience function which tests if its arguments are NULL.  If not, they are selected out:

select vErrCode as ErrCode, vErrMsg as ErrMsg;

This guarantees consistency and saves much typing.  

So now we have the basics of an interface with PHP, the last thing we need is some logging.  SQL / Stored Proc programming can be a pain when you can't see what is going on, and a decent logger is your best friend.

Arrange a table to put log messages in.  It should be a permanent table, but using the Memory engine for speed.  The table itself should have a field for the message itself, an Id for ordering and a field for a "tag".

The "tag" will be a short, random string made up on entry to the proc, or (my preference) made up when PHP starts up.  A useful "tag format" I find is AAAAA/BBBBB, where AAAAA is the last 5 chars of the current session token, and BBBBB is 5 random chars.  This lets me track a user's session or a single click through the logs.

Arrange to have SPWrapper find or make up a "tag" and append it to the arguments for the proc.  Have each proc accept a final "tag" argument.

Create two new procs:  spLog and spLogPop.  The former takes the tag and a message and inserts it to the temp table.  The latter takes a tag and gives you all the messages associated with that tag, in order, then removes them from the log table.  These can be written out to the log file.  SPWrapper will execute an spLogPop immediately after any Procedure call.

Lastly, let's cover how you load your procs.  As with most things around here, it will be with a cunning script...

#!/bin/bash

db=$1; pass=$2

for x in $(ls ./sql/procs/*.sql); do

  cat "./sql/procs/$x" | mysql -u root -p$pass $db

done;

You'll want to do a bit more in this script, such as checking if password is on commandline, if not to prompt for it (one way suits an insecure dev server just fine, the other is preferable for a production server)

Dedicate a different .sql file to a different task or part of the database or part of your API.  Keep things separate and well named.  Then in each file, follow this pattern:

drop procedure if exists spSomeFabProc //

create procedure spSomeFabProc

This means all your procs can live with the rest of your app in lovely Source Control heaven and be loaded in to your test database at any time.

Hope this has been useful; Enjoy.





...Click for More