Calling Stored Procedure in foreach loop - only first executed

Question

This is really derivative of my earlier question today.

I created a Stored Procedure in my database that I wanted to call several times in a row from PHP.
Let's say this is my procedure:

CREATE PROCEDURE PROC_1(
  IN param1 VARCHAR(255),
  IN param2 VARCHAR(255))
BEGIN
  DECLARE ok INT;
  DECLARE success, failure VARCHAR(255);

  /* several SELECT, IF ... THEN, INSERT and UPDATE operations which then SET ok var to 0 or 1 */
  IF ok = 1 THEN
    SET success = 'Everything went well';
    SELECT success;
    LEAVE;
  ELSE
    SET failure = 'Problem description';
    SELECT failure;
    LEAVE;    
  END IF;
END

I did it this way (short version):

$calls = array(
    "CALL PROC_1('param1', 'param2')",
    "CALL PROC_1('param3', 'param4')",
    "CALL PROC_1('param5', 'param6')",
);

// assuming I'm already connected to DB with $link
foreach ($calls as $i => $call)
{
    echo $i . ': ';
    $result = mysql_query($call);
    $ok = ($result === FALSE) ? FALSE : TRUE;
    var_dump($ok);

    if ($result !== FALSE)
        mysql_free_result($result);
}

The first iteration works as expected, but anything after returns FALSE.
Why is that?

Tried mysqli just in case, but getting exactly same output:

0: bool(true)
1: bool(false)
2: bool(false)

What's interesting, I've checked MySQL logs (logging set to log all queries) and only first query ever gets to the server. Next queries never get to the server.

PS. I'm running PHP 5.3.2 and Apache 2.2.17.

UPDATE

As per Shakti Singh's suggestion I've checked $link state before querying the database. I noticed there's an error since the second iteration so here's the output with the error:

ERROR: (0) 
0: bool(true)
ERROR: (0) 

ERROR: (0) 
1: bool(false)
ERROR: (2014) Commands out of sync; you can't run this command now

ERROR: (2014) Commands out of sync; you can't run this command now
2: bool(false)
ERROR: (2014) Commands out of sync; you can't run this command now

Also, this appears in MySQL error log:

101126 15:46:28 [Warning] Aborted connection 129 to db: 'db1' user: 'root' host: 'localhost' (Got an error reading communication packets)

That was a good tip Shakti. Shows a bit more about the problem now. Details in the updated question. — Michal M, Nov 26 '10 at 14:55
A stored procedure may return more than one result set and it will always return one extra result set that does not contain any data but the overall error/warning information about the procedure call in addition to any explicitly returned result sets. - http://forums.mysql.com/read.php?52,228296,228347#msg-228347 — ajreal, Nov 26 '10 at 15:21
If you put that into a answer I'll happily accept it as it answers my question. — Michal M, Nov 26 '10 at 15:27

Nishu Tayal · Accepted Answer · 2011-09-27T10:49:06.770

In php, when we call a stored procedure in a loop, it just execute it once. It happens when the stored procedure returns any result set. I faced the same issue. I had a stored procedure for updating table records.

DELIMITER $$

DROP PROCEDURE IF EXISTS `espritkm`.`update_notification`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_notification`(item_id_val VARCHAR(11),item_source_val VARCHAR(50),item_type_id_val INT(50),item_type_val VARCHAR(50),created_at_val BIGINT(11),pivot_user_id_val VARCHAR(256),pivot_item_type_val VARCHAR(64),pivot_owner_type_val VARCHAR(64),pivot_owner_id_val INT(11),user_id_val VARCHAR(64),  OUT row_effect VARCHAR(11))
Begin
  Declare item_count INT(10);
   SET @SQL1 = CONCAT('select count(*) into @item_count from item_notifications where item_id = ''', item_id_val, '''');                                                                                    PREPARE S1 FROM @SQL1;                                                                                EXECUTE S1;                                                                                    DEALLOCATE PREPARE S1;     
    IF @item_count = 0 THEN
       SET @SQL2 = CONCAT('INSERT INTO item_notifications (item_id,item_source,item_type_id,item_type,created_at,pivot_user_id,pivot_item_type,pivot_owner_type,pivot_owner_id,user_id) value(''',item_id_val,''',''',item_source_val,''',''',item_type_id_val,''',''',item_type_val,''',''',created_at_val,''',''',pivot_user_id_val,''',''',pivot_item_type_val,''',''',pivot_owner_type_val,''',''',pivot_owner_id_val,''',''',user_id_val,''')');    
 PREPARE S2 FROM @SQL2;                                                                                  EXECUTE S2;                                                                                     DEALLOCATE PREPARE S2;      
      SET row_effect= "Insert";    
    ELSE             
        SET row_effect= "Update";    

    SET @SQL3 = CONCAT('UPDATE item_notifications SET viewer_id = ''',user_id_val,''' WHERE item_id = ''' ,item_id_val,'''') ;     
       PREPARE S3 FROM @SQL3;                                                                                 EXECUTE S3;                                                                                    DEALLOCATE PREPARE S3;      

    END IF;
    SELECT row_effect;
END$$

DELIMITER ;

And this was supposed to be executed for 1000+lines, but executed for just one record.

It does not support in case of when your SP return any dataset. Just eliminate the OUT var, or any select statement(just for any result reference), and it'll work fine.

score 1 · Answer 2 · answered Nov 26 '10 at 15:40

A stored procedure may return more than one result set and it will always return one extra result set that does not contain any data but the overall error/warning information about the procedure call in addition to any explicitly returned result sets.

source - http://forums.mysql.com/read.php?52,228296,228347#msg-228347

Calling Stored Procedure in foreach loop - only first executed

2 Answers2

Linked