Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
By jonathanchris on
Is it posible to use stored procedure with drupal? And if so, How should I go about doing this?
Comments
what kind of stored procedures
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
--
Morris Animal Foundation
patch ?
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.
no policy?
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
--
Morris Animal Foundation
wise ?
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.
the edge of Drupal
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
--
Morris Animal Foundation
Has any progress been made on
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.
Drupal 6 does not support stored procedures natively
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:
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.
MySQLi connectivity
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.
Working patch
Hi there,
Read http://drupal.org/node/1015440
This is working fine
Thanks
Nanda
To execute mysql store procedure
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.
Can you explain bit more?
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
Try to use mysqli in settings.php
Hi kaliseetha,
Try to use mysqli in settings.php.
I got output with lots of errors
Nanda
function
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;
}
MySQL Stored Routines are a GOLDEN and timely topic....
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.