By kbpair on

I am trying to call a stored procedure in MySQL 5.0 from Drupal 5.0 but getting an error like

PROCEDURE DBName.ProcName can't return a result set in the given context query: call ProcName() in /public_html/includes/database.mysql.inc on line 167

I am new to PHP but am wondering if I need to be using mysql_query instead of db_query?

Any help is appreciated.

⋅ Categories: Drupal 5.x

Comments

kbpair’s picture

kbpair commented

Sorry should have included that my call looks like this

db_query("call ProcName()");

kbpair’s picture

kbpair commented

Turns out this is really an issue with PHP not knowing how to handle multiple resultsets combined with me not knowing the correct way to select data into variables in a MySQL 5.0 variable. Check the MySQL docs for the correct syntax but basically you have to use Select Into Variable rather than just select variable ...

http://10000Thoughts.com - Harness the Power of Thought

jonathanchris’s picture

jonathanchris commented

Can anyone explain how to use stored procedures with drupal? Or could the poster of this display their final code for calling the stored procedure.

I have a stored procedure but whenever I call db_query("CALL myProc()"); nothing is returned. All the procedure is doing is calling a select statement: SELECT nid FROM node; How can/should I handle this?

kbpair’s picture

kbpair commented

I am using the same syntax as you

db_query("call spname()");

and it is working. My original problem was the stored procedure tried to return multiple resultsets. I actually do not want to return anything. Since I do not want to get anything back I have not tested getting the results from the call.

Make sure you give execute permission to the drupal user accessing the database. I had to do this manually.

good luck,

http://10000Thoughts.com - Harness the Power of Thought

diabloview’s picture

diabloview commented

db_query ( "CALL delete_comments_and_topics_by_shared_user($uid)" )

user warning: execute command denied to user 'devforum_drupal'@'localhost' for routine

I am getting this access denied error when trying to execute a stored procedure within Drupal version 5.6.
Anyone know how to successfully execute a stored procedure in Drupal? I am not trying to get any values back, I just want this thing to fire when I tell it to.

kaliseetha’s picture

kaliseetha commented

Having permission But db_query('call fun()'); doesn't work.