Calling Stored Procedure on Codeigniter

[eluser]Unknown[/eluser]
I just want to know because I'm developing a online shopping cart.
I would like to know your opinion because I'm thinking of better approach.

Is it better to create a stored procedure containing the business process and just call it using codeigniter or just embed the sql syntax to codeigniter?

Thanks in advance...

[eluser]Otemu[/eluser]
Hi,

There quite detailed answers to whether you should use stored procedures or just write SQL in your code, some are for it and some are not, check out these links below, although none are Codeiniter the principles when to use stored procedures are more or less the same.

http://stackoverflow.com/questions/46297...procedures
http://stackoverflow.com/questions/15142...ersus-code
http://www.codinghorror.com/blog/2004/10...yways.html






Hope that helps

[eluser]duartix[/eluser]
I guess there isn't an easy answer to this, as you'll have to weight the pros and cons.
I really depends a lot on what you are doing, the dimension of the project, your will to stick to a particular DB, the teams proficiency in SQL vs PHP, etc.

I'm currently ending a small project where there was already a lot of code on stored procedures (SP), most of it related to DB administration (creating DB users, managing their ROLES and permissions), in a team whose major talent is on the SQL side, so it made a lot of sense to push as much as possible to the DB. All the INSERT/UPDATE/DELETEs are done by calling SPs, however I failed completely when trying to find a way to get the SELECTS working from a SP which returned a CURSOR, so the SELECTS are plain PHP on Codeigniter Models.

One word of advice. When calling SPs, DO NOT USE:

Code:

result = $this->db->query($query);

use this instead:

Code:

result = $this->db->simple_query($query);

It will save you a lot of misery.

(EDIT) Otemu's links are very telling, especially the last one: http://www.codinghorror.com/blog/2004/10...yways.html