Stored Procedures Help

[iceangel89]

i created a stored procedure

 

CREATE PROCEDURE GET_USER_ACL_BY_USERNAME (IN USR VARCHAR(255)) 
   BEGIN
   SELECT ACL.Action, ACL.Path FROM ACL
   WHERE ACL.UserGroup
   IN (
     SELECT Usergroup.GroupID
     FROM Users, UserGroup, Privilages
     WHERE Users.UserID = Privilages.UserID
     AND Usergroup.GroupID = Privilages.GroupID
     AND Users.UserName = USR
   );
   END|

 

then when i CALL it,

 

CALL GET_USER_ACL_BY_USERNAME ('emp1') 

 

it says

#1312 - PROCEDURE employeemanager.GET_USER_ACL_BY_USERNAME can't return a result set in the given context

 

whats wrong? how can i fix this? i just started stored procedures... and recommendations for Stored procedures resources at the same time?

[fenway]

My guess is that it's your client (i.e. PHP) that has a problem, not mysql.

 

Try adding the CLIENT_MULTI_STATEMENTS flag to your call to mysql_real_connect() and see if it fixes the problem.

[gluck]

http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

 

HERE IT IS:

For all of you getting "can't return a result set in the given context" errors when using PHP to execute stored procedures,

the mysql_connect flag is:

 

mysql_connect( host, databaseuser,password,TRUE, 131074)

 

Worked with mysql 5.0.20 and PHP 5.1.4

Also, stored procedures seem to close the connection when they've finished running in PHP.

Can be fixed using mysql_ping( db_resource_id ) to reinstate lost connections