Using MySQL Stored Procedures in Cakephp

I am new to this topic in many ways. For ages I have prefered to do the database work in Cakephp - or being honest, I have been afraid to touch MySQL, being that close to the data seemed like a stupid idea. Especially because I wasn't bold enough to go playing with things.

That being said, I have been needing to make my data transfers faster for one project I am currently working on (under wraps at this time, more news soon I'm sure). Every second query was returning nothing. Not good. So the first thing was to speed up the queries.

Firstly I tried turning debug to zero in the config file of cake - the error still appeared. So next move was to shift all the data and queries from the cakephp controller into MySQL and save all that database interaction. A point made by my brother and excellent codeman @ghostpsalm.

So I set about getting the queries into a procedure, which was entirely new ground, but with some google searching and watching others play with it already. I managed it together, that was until I tried to excecute it from Cakephp.

Stored Procedures in Cakephp

The Controller call was easy. I have the $this->query() call in the Model, so it just looks to the Model for the call, and sets the var to pass to the view, and sets the view as an ajax - which means it doesn't render the layout around the view.

[php]function show($site = null){
Configure::write('debug', 0);
if(!empty($site)){
$advert = $this->Advert->findAd($site);
$this->set('advert', $advert[0]);
$this->render('show', 'ajax');
}
}[/php]

Nice and easy. The Model was easy as well. Because I had setup the procedure to return data like Cakephp does dealing with the resulting data was easy.

[php]function findAd($site) {
$advert = $this->query('CALL display_ad("'.$site.'");');
return $advert;
}[/php]

The $this->query() is the way to execute any SQL. Easy, just call the 'display_ad' procedure and the returned data is returned to the controller - where it is set for the view.

So with everything set up stock, I ran the page, and nothing worked. In fact I got this wonderful error : SQL Error: 1312: PROCEDURE telegraph.display_ad can't return a result set in the given context. Another SQL error that explains nothing, I'm still not used to these and after a confusing day learning Stored Procedures it was not a nice error to end the day on.

There was an easy fix. First I changed the Table Engine on my display table from InnoDB to MyISAM - because it seemed like the better idea for the high read volumes the table would have. Edited basically once a month. I don't think that was nessecary for it to run.



Next I had to change the database driver to be MySQLi instead of the standard MySQL. This was a simple change in config/database.php:

[php]'driver' => 'mysqli'[/php]

Actually its a change I think I'll be using from now on. "MySQLi is an improved version of the older PHP MySQL driver, offering various benefits" ((MySQLi from Wikipedia)) - So it seems from that and the PHP website that "The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above." ((MySQL Improved Extention)) - sounds nice.

With those two changes I was able to get CakePHP to run a Stored Procedure. Nice and easy, so if you get that 1312 error running a Stored Procedure from Cake, give that a go, and let me know how you go.

Comments

[Derick Ng]

Hey, I have added your site to http://planetcakephp.org/aggregator/sources/1244-james-rickard if you don't mind.

Cheers and keep baking!

[Adam]

Although I personally don't use stored procedures, I was under the impression you could call them directly on the model, and cake would execute them automatically.

So instead of this:

$this->Advert->findAd($site);

you would do:

$this->Advert->display_ad($site);

[James]

@Derick Ng - Cheers. It's an honour to be on such a feed.

@Adam - If by calling 'them directly on the Model' you mean calling them from the Controller, then yes, you are correct. You can do that. It looks like that is the case on book.cakephp.org : http://book.cakephp.org/view/456/query" rel="nofollow">Query

It would look like this:
$advert= $this->Advert->query('CALL display_ad("'.$site.'");');

But I do subscribe to the fat model skinny controller thing - as pointed out by http://cakebaker.42dh.com/2007/02/26/should-you-use-modelquery-in-the-controller/" rel="nofollow">cakebaker. That being said I also like to keep the database transactions in the Model, and leave to Controller to do what it does.

[ajmacaro]

problems with stored procudure is that
it is DB specific. what happen if one day
you would like to change database, does the
code in store procedure will still work?

[James]

Ture. It is database specific. If I was to change from MySQL to something else I would no doubt have to spend time figuring out how to change it over.

Of course, at this stage I'm more than happy making it work with MySQL, so the problem is nulified.

[MySQL procedūrų naudojimas Cak]

[...] dar neteko naudoti, bet pastebėjau gerą įrašą ta tema. Pirmiausia duomenų bazės nustatymuose (config/database.php) nustatom [...]

[ZAky]

1. @Adam don't put any thing that smell like sql outside your model, among other reasons see point 2 .
2. @ajmacaro stored procedure is the best performance choice so it's better to invest in the performance area being a 24/7 issue then investing in the ability to change database, which probably will not happen.
If you keep all your sql related syntax in the Model you can be sure to touch only your model files in case of migrating to new database type.

[Gabriel]

You should be aware that the ->query method doesn't protect you against SQL injection so you have to do it yourself.

When you are making queries by concatenating strings, you must use mysql_real_escape_string

This would be a safe implementation:

function findAd($site) {
uses("Sanitize");
$advert = $this->query('CALL display_ad("'.Sanitize::escape($site).'");');
return $advert;
}

[Gabriel]

mysql_real_escape_string or Sanitize::escape form CakePHP :)

[santanu]

Thanx a lot for your help, this is the thing exactly I have wanted