By jawaidgadiwala on

As far as I know Drupal 6 doesn't support stored procedures natively as it doesn't provide any function in core files to execute stored procedures properly. It is because Drupal 6 database abstraction layer still uses the function-based PHP MySQL extension, not MySQLi extension. This is really an issue with PHP MySQL extension not knowing how to handle multiple result sets. So when db_query() is used to execute stored procedures then some weird warnings and errors are shown. This usually occurs when stored procedure returns some results. That's why i have created a patch function to execute MySQL stored procedures, the function uses MySQLi connection and works exactly like db_query(). Following is the function code for executing stored procedures.


function sp_query($q)
{
    global $db_url;
    $url = parse_url(is_array($db_url) ? $db_url['default'] : $db_url);
    $db_user = isset($url['user']) ? urldecode($url['user']) : '';
    $db_pass = isset($url['pass']) ? urldecode($url['pass']) : '';
    $db_host = isset($url['host']) ? urldecode($url['host']) : '';
    $db_port = isset($url['port']) ? urldecode($url['port']) : '';
    $db_path = ltrim(urldecode($url['path']), '/');

    $mysqli = new mysqli($db_host,$db_user,$db_pass,$db_path);

    if(mysqli_connect_errno())
    {
        drupal_set_message("Could not connect to the database. MySQL error: ".mysqli_connect_error(), 'warning');
    }

    //For multi language sites
    $mysqli->query('SET NAMES "utf8"');

    $args = func_get_args();
    $query = call_user_func_array('sprintf',$args);

    if ($mysqli->multi_query($query))
    {
        if ($result = $mysqli->store_result())
        {
            return $result;
        }

        else
        {
            drupal_set_message("An error occurred while calling the stored procedure. MySQL error: ".mysqli_error(), 'warning');
            return false;
        }
    }
}

You can use this function like sp_query("call your_stored_procedure(%d,'%s',2011);",$id, $name);

Result returned from sp_query() will be a mysqli_result (if query returns any result set) otherwise returns false if nothing is returned or if some error occurs.

If you guys finds any issue/problem in the code then please do comment here, constructive comments are welcomed.

Regards,
Jawaid Gadiwala

⋅ Categories: Drupal 6.x

Comments

umair.vatao’s picture

umair.vatao commented

Good attempt,

the code tries to make a new connection for every query execution... this may be heavier if a site is calling multiple SP calls within single execution cycle. You should isolate the connection from this function.

Umair

sarfarazsoomro’s picture

sarfarazsoomro commented

hey Jawaid you can use,

global $active_db;

and pass it on as

mysql_multi_query($active_db, $query);

I haven't tried this. just a suggestion though.

Jztinfinity’s picture

Jztinfinity commented

I've used this approach in the past and it works.

Jztinfinity’s picture

Jztinfinity commented

by the way for stored procedures especially you may need to free your result

jim_at_miramontes’s picture

jim_at_miramontes commented

Being completely new at the whole stored procedure thing (but wanting to learn!) --

* How do you get a procedure into the database in the first place?

* How long does it stay around? (The answer to this may be obvious depending on the answer to the first question...)

Thanks!

monti’s picture

monti commented

create procedure myproc()
begin
your code...
end;

it stays there until you drop it.