Hi there,

I have a series of PHP pages that have pretty much the same functionality and allow me to manage information in a database (each page allows me to maintain a specific table). They all allow to Edit, Create and Delete records and do things in pretty much the same manner.

BTW: I am using SQL Server as a BE database

I am having a problem with one of them, as when the user presses the DELETE button the page loads again going to the appropiate section (via POST argument). The relevant procedure is called to delete the data from the table and then the page is rendered again.

Now on the rendering, one of the first actions is a call to another Stored Procedure and, if the user has just deleted an element, an error is shown stating:

"Connection is busy with results from another hstmt"

The only difference between this page and the others is that this STORED PROCEDURE deletes data from a table that has a FOREIGN KEY set to DELETE ON CASCADE. Could it be that the DELETE ON CASCADE is somehow holding up the resource and it is not allowing somehow PHP to establish another SQL connection?

Any help would be much appreciated. Thanks,

Javier

    4 days later

    Anyone has any ideas on this one guys? Thanks

      Hi,

      please post the relevant code (where you establish the connection and execute the statements).

      Do you use the mssql or odbc functions ?

      Seems like you need to clean up some resources of the first statement before you can execute the second statement (especially of one is a writing and the other is a reading statement).

      Regards,
      Thomas

        The code is way too lengthy. But in a nutshell this is what is running and causing the error:

        $dsn="DRIVER={SQL Server};SERVER=D971XB1J\SQLEXPRESS;DATABASE=XYZ";
$username="USER";
$password="PASSWORD";

// Open connection to the Database
$sqlconnect=odbc_connect($dsn, $username, $password) or die ('The site database appears to be down. Please contact the System Administrator or try again later.');

...
...

$sqlquery="EXEC SP_Delete_Order_Item ". $_POST['OrderID'].", '". $_POST['ItemToDelete'] ."';";
$process=odbc_exec($sqlconnect, $sqlquery);

...
...
...

<select name="SelOrderID">
	<?php	
		$sqlquery="EXEC Get_Orders NULL;";
		$process=odbc_exec($sqlconnect, $sqlquery);

	while(odbc_fetch_row($process)){
		$OrderID = odbc_result($process,"OrderID");
		if ($OrderDetails["OrderID"] == $OrderID) {
			echo "<option value='$OrderID' selected>$OrderID";
		} else {
			echo "<option value='$OrderID'>$OrderID";
		}
	}						
?>					
....
....

        Both SPs are quite basic, the first one, as you can easily guess, pretty much just deletes the relevant record, while the second one, when receiving NULL as an argument (like in this case) it performs a select to return all orders.

        Any ideas? Thanks

          Ok,

          you may need to free the resources after the call to the delete SPS, something like.

          $sqlquery="EXEC SP_Delete_Order_Item ". $_POST['OrderID'].", '". $_POST['ItemToDelete'] ."';";
$process=odbc_exec($sqlconnect, $sqlquery); 
odbc_free_result($process);

          Alternatively or additionally (not sure) you may need to call odbc_commit.

          If that doesn't help then try to use to different odbc connections. One to execute the writing/deleting mode SP_Delete_Order_Item and one to execute the reading mode Get_Orders. That was one of the suggestions in another forum.

          Thomas

            Cheers Thomas. ODBC_FREE_RESULT did the trick. Funny I've never used it until now and never had an issue... I guess maybe the lag time from the trigger could be causing the issue. Many thanks

              Write a Reply...