New to the world of stored procedures.

Am calling.

[code=php]	$execute1 = mysql_query("CALL firstsub('TWIT','$_POST[arg0]','1')") or die(mysql_error());	
	$execute2 = mysql_query("Update hierarchy set base_zip='$_POST[arg0]' where org_name='$_POST[arg0]'") or die(mysql_error());

[/code]

The call on the stored procedure calls:

SELECT @myleft := lft FROM hierarchy WHERE org_name = parent and org_id = origorgid;
SELECT @refid := MAX(ref_id) FROM hierarchy;


UPDATE hierarchy SET rgt = rgt + 2 WHERE rgt > @myleft;
UPDATE hierarchy SET lft = lft + 2 WHERE lft > @myleft;

INSERT INTO hierarchy(org_name, lft, rgt,ref_id) VALUES(old , @myleft + 1, @myleft + 2, @refid + 1);

And I end up with the error stated in the subject.....

Google searches didn't really address the issue for me.

It is failing on the update after running the SP.
Thanks

    Could MySQL be running the SP aysnchronously?

      Hmmm could be.
      I'm not thinking so. I should wait for some kind of response from the SP befor runing query two.

        Anyone?
        Is there a good tutorial for using mysql_query with SPs and then calling other mysql_query commands?

          a year later

          OK, 18 months later and nobody answered? Anyway, I have the exact same problem. Can someone help here?

            Does it work if you use a library that's not old and outdated (e.g. [man]mysqli[/man], [man]PDO[/man], ... anything but mysql)?

              bradgrafelman;10970407 wrote:

              Does it work if you use a library that's not old and outdated (e.g. [man]mysqli[/man], [man]PDO[/man], ... anything but mysql)?

              I use the mysqli interface, which is quite OK. I have used the mysqli converter tool from mysql dev site, so, everything is set OK. I have only one SP so far, and I don't know how to fix that problem. Forgot to mention:

              php -v
PHP 5.3.3 (cli)
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
    with XCache v1.3.0, Copyright (c) 2005-2009, by mOo

mysql 5.1

              I plan to use on with a similar sightly older PHP and MySQL 5.0 (that's what my host has). Executing the the exact same series of SQLs I do from in the PHP from the mysql client I have no problem.
              Note, that I use the procedural calls. Also note that I use the mysql_free_result after each SQL call.

              When I used a "solution" (repeatedly call the free) with the following function I found in php.net:

              1. function clearStoredResults($mysqli_link){
2. 	do{
3.		if($l_result = $mysqli_link->store_result()){
4.			$l_result->free();
5.		}
6.	} while($mysqli_link->next_result());
7. }

              I do not have the error, BUT, I have an error at line 3:

              mysqli::next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method in

              The strange thing is that I call the function as:

              if (mysqli_more_results($GLOBALS["___mysqli_ston"]))
    clearStoredResults($GLOBALS["___mysqli_ston"]);

              Another strange thing:
              When I call the mysql_free_result just after the SP call, I have a failure! Why?
              Generally, how on earth do you correctly call a SP in the middle of SQL queries?

                Write a Reply...