3

For one of my project, I'm thinking to use Stored Procedure (Of-course I will be using MySQL 5+ database). Before I get started, I'm curious if we can implement Stored Procedures at database end and then call it in our application (Most probably custom component).

I know, we will surely get a portability issue. But my client is not worrying about it as they always like to keep themselves up-to-date with latest technology.

If it is possible then, do we have helper functions in Joomla Framework to call SP (Stored Procedure). I tried to search it on internet, but looks like we do not have enough information on the same.

2

You can just execute a CALL in a $_db->setQuery( $query );.

From my experience working with stored procedures and PHP, it is not that simple.

  • Stored Procedures are useful to execute a bunch of statements together, with a few parameters
  • However, parameter binding and returned results must be checked for compatibility. Example, a stored procedure can execute several queries, by only the last set of results is retrieved
  • Some hosters restrict stored procedures and you have to ask to enable them
  • MySQL Stored Procedures are limited, not like Oracle PL/SQL where you have advanced statements, functions, JSON processing or debugging

To sum up, a Joomla project based on PHP and SQL queries is less complex than a 50% PHP - 50% MySQL project.

  • +1 for your detailed answer. It must also be consider that complexity comes with additional flexibility which is what I'm looking for. i will test your answer and revert back. Thank you very much for your time – Sahil Purav Jun 17 '15 at 15:54
4

Wouldn't advise using stored procedures in MySQL for a web app. MySQL compiles stored procedures on demand and holds them in a cache based on the connection. Pretty much every web app (Joomla included) opens a new connection with every URL (open a connection, serve the page, click a link, open a new connection serve a page, etc). Which means every stored procedure you call will be compiled once for every web request the server handles. The overhead can quickly eat up anything you might gain, unless your app is going to sit there and make repeated calls to the same stored procedure on every page request.

MySQL stored procedures might be useful for something that is called repeatedly before giving the user a change to ask for more, but pretty much every request to the web server will result in a new connection being opened and the procedure being recompiled.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.