Is it posible to use stored procedure with drupal? And if so, How should I go about doing this?

Comments

greggles’s picture

Are you referring to mysql stored procedures?

As far as I know Drupal doesn't provide any helper functions to execute stored procedures so you would need to just use the php functions to execute the stored procedure.

If you find some way to make this easier then perhaps you could provide a patch for database.mysql.inc that makes it easier to call stored procedures.

--
Knaddison Family | mmm Free Range Burritos

DynV’s picture

Hmm isn't that patching the core ? If so it might not be flexible for different database (progresql) or different module access type as some might be naughty and bypass Drupal API. Anyway if anyone can shed light on Drupal's stored procedure policy, please let me know.

greggles’s picture

As far as I know there is no policy on this. My idea of a patch was that it would get contributed back to the project and hopefully included into core.

The idea of making a patch to the database.*.inc files is that the api we use would be consistent and then inside of there would be the DB-specific code to make it work. About loss of flexibility, I think we'd have to get into the concrete implementations to really know.

--
Knaddisons Denver Life | mmm Chipotle Log

DynV’s picture

If the patch doesn't follow Drupal DB API or guideline, there's possibility it might not be supported in another version or that it might cause conflicts with another module that also bypass the API. Personally, I'd be really hesitant to base a module on another which isn't safe and sound.

greggles’s picture

So, when people start using Drupal the advice is always "don't edit the core files or your contributed modules" and instead that people should use the APIs and events and hooks to implement the things they need without "hacking".

But then when you need a new features that people might want, you get to a point where you should edit the core/contrib files to try out your changes. Then you contribute that patch back and begin the process of reviews to get the patch included into core. So, yes there are certainly potential problems but if nobody created such patches then the project would never move forward - we would have no new code.

--
Knaddisons Denver Life | mmm Chipotle Log

brandon.dixon’s picture

Has any progress been made on this? I am working on a module for my company right now and one of the things I plan on doing is executing select statements through the use of stored procedures. I would like to use a wrapped method, but if not then it looks like I will just be using PHP functions.

Alexander Allen’s picture

    Because
  • Drupal 6 is still not completely object oriented.
  • It looks like Drupal 6's database abstraction layer still uses the function-based PHP MySQL extension, not mysqli.
  • Stored procedures are only supported in mysqli.

Until Drupal starts using the mysqli extension, there will be no support for stored procedures when using Drupal's db_query(), db_query_range() and similar functions.

Bear in mind that because the mysqli extension is only new in PHP5, by not using mysqli Drupal 6 remained backwards compatible with servers running PHP4 (and I have seen quite a few government and college servers running PHP4).

Else, if you really need them, you can:

  • Check if Drupal 7 supports them I submitted the question already
  • Create a new database connection in your module using the mysqli extension like this:
     $mysqli = new mysqli('localhost','user','password','database'); 
    $query = "CALL sp_yourstoredprocedure";
    
      if(mysqli_connect_errno()){
        drupal_set_message("Could not connect to the database. MySQL error: ".mysqli_connect_error(), 'warning');
      }
      $mysqli->query('SET NAMES "utf8"'); # If you don't do this and have a site in a language like Spanish, get ready to see a lot of weird characters
      
      $query = "CALL sp_yourstoredprocedure"; # Note the missing parenthesis at the end.
    
      if ($mysqli->multi_query($query)) { # I use multi_query because my stored procedure yields multiple results, instead of one for better performance   
        if ($result = $mysqli->store_result()) {
          // Return an array of result rows
          while ($row = $result->fetch_object()) {
            # Your application's logic
          }
          $result->free();
          $mysqli->next_result();
        }
      } else {
        drupal_set_message("An error ocurred in your module while calling the sp_yourstoredprocedure stored procedure. MySQL error: ".mysqli_error(), 'warning');
      }
    
    
  • Since no new features are going to get integrated into Drupal 7's core, you can create support for stored procedure in you own Drupal isntallation by modifying the core. Then you can submit a patch to the Drupal community - which may get integrated into a future release of Drupal (depending on how it affects the rest of the core modules and other factors I do not know).

For more info on the MySQLi extension you can visit PHP's documentation on http://www.php.net/manual/en/mysqli.summary.php where you will find different ways you can use stored procedures in PHP 5.

Alexander Allen’s picture

On the settings.php inside the sites folder the database connection url allows various databases, I noticed this recently. So for example, you can set

$db_url = "mysql://user:pass@databasedomain.com/database"

or

$db_url = "mysqli://user:pass@databasedomain.com/database"

I haven't tried using the mysqli version yet, but I imagine it should work.

yesnandhu’s picture

Hi there,

Read http://drupal.org/node/1015440

This is working fine

Thanks

Nanda

amardhumal’s picture

To execute mysql store procedure in drupal try to use following code:

1)If there is no any parameter passed to store procedure then
db_query("CALL your_store_procedure_name()");

2)If parameters are passed to store procedure then
db_query("CALL your_store_procedure_name($parameter1, $parameter2)");

replace 'your_store_procedure_name' with procedure name and '$parameter1' with your procedure's first parameter.

kaliseetha’s picture

Hi amard,

I've tried db_query("CALL Test()"); but it doesn't work.

ie,

$row=db_query("CALL Test()");

while($result=db_fetch_array($row))
{
$result['fid'] here . But i dint get any result.
}

----------------------------------

CREATE PROCEDURE Test()
BEGIN
SELECT * FROM files;
END

yesnandhu’s picture

Hi kaliseetha,

Try to use mysqli in settings.php.

I got output with lots of errors

Nanda

Donvitorio’s picture

function _db_query_call($paciente) {

global $db_url;

if (is_array($db_url)) {
$connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
}
else {
$connect_url = $db_url;
}
$url = parse_url($connect_url);

// Decode url-encoded information in the db connection string
$url['user'] = urldecode($url['user']);
// Test if database url has a password.
$url['pass'] = isset($url['pass']) ? urldecode($url['pass']) : '';
$url['host'] = urldecode($url['host']);
$url['path'] = urldecode($url['path']);

$mysqli = new mysqli($url['host'],$url['user'],$url['pass'],substr($url['path'], 1));

if(mysqli_connect_errno()){
drupal_set_message("Could not connect to the database. MySQL error: ".mysqli_connect_error(), 'warning');
}
$mysqli->query('SET NAMES "utf8"'); # If you don't do this and have a site in a language like Spanish, get ready to see a lot of weird characters

$query = "CALL Puntos($paciente)"; # Note the missing parenthesis at the end.

if ($mysqli->multi_query($query)) { # I use multi_query because my stored procedure yields multiple results, instead of one for better performance
if ($result = $mysqli->store_result()) {
// Return an array of result rows
while ($row = $result->fetch_object()) {
# Your application's logic
$resultado = $row->resultado;
}
$result->free();
$mysqli->next_result();
}
} else {
drupal_set_message("An error ocurred in your module while calling the sp_yourstoredprocedure stored procedure. MySQL error: ".mysqli_error(), 'warning');
}

return $resultado;
}

jdonson’s picture

MySQL Stored Routines = Functions, Procedure, Triggers

There are MANY benefits to the approaches we are considering on this page. Thank you Greggles.

The only "problem" I see that this creates is that it makes Drupal installations LESS database-platform-agnostic.

If you want to learn more about MySQL Stored Procedures, see the MYSQL 5.x New Features Series by P Gulutzan: http://tinyurl.com/8xwfdj5

Also, specifically which functions of Drupal 7 Core API are you considering candidates for Stored Proc design?

Regards and Thank You.