Connection is Busy with results for another hstmt ERROR

I am newbie. I have written most of my queries in PHP. I created a connection object and then hitting sql server.

function navigation($sel_subject, $s开发者_JS百科el_page,$filter=false) {

    $subject_set = get_all_subjects();
    $page_set = get_pages_for_subject($subject["id"],$sec_wp,$filter);
}

function get_all_subjects() {
    global $connection;
    $query = "  SELECT * FROM subjects ORDER BY position ";
    return odbc_exec($connection,$query);
}

Now i am updating few simple queries which just need to fetch the data from the database. I am upgrading them to stored procedures.

function get_all_subjects() {    global $connection;
    $query = "  EXEC get_all_subjects ";  
 return odbc_exec($connection,$query); }

I am getting an error message. Connection is Busy with results for another hstmt ERROR

I am using sql server 2005 and with ODBC connectors.

What is the best way to solve this issue. Or is it a better idea to ask admin if they can do something like http://sourceitsoftware.blogspot.com/2008/06/connection-is-busy-with-results-for.html Ottar Holstad said... When using BDE to connect to a MS SQL Server, via ODBC, this can (sometimes?) be solved by using SQL Native Client instead of SQL Server Driver for ODBC.

I would like to know, it is it better to activate MARS(multiple active resultset). If so, please help me in providing a step-by-step procedure to enable the same.

You have to enable MARS(Multiple Active Resultset) which is disabled by default. You can avoid this error message by switching to 'SQL Native Client'. For more info check this

Can you try adding odbc_free_result calls when you're finished with the results of an odbc_exec?

I am recommending other frameworks instead, such as

• PDO http://dev.mysql.com/tech-resources/articles/mysql-pdo.html
• ADOdB http://phplens.com/adodb/

These should be more flexible and advanced than odbc_. odbc_exec is bent on returning a server-side cursor, which does not allow another query to run on the same connection. A client-side cursor takes all the data as a static snapshot to the client in one go, therefore the connection is free for other queries.

Staying with odbc_, you may be able to get away with creating multiple connection handles and using a different one if you need another result set while the first is active.

I would say there are three parts to a potential solution.

First would be better optimization of your SQL statement.

Try to use the LIMIT statement and move away from using the * and define just the fields you need. These two things can help reduce the memory load of the SQL statement.

Secondly, try optimizing your database table structure better (ie: reduce Keyname/Index values and such things.) If your database is of any significant size (say, 50,000 rows or bigger) maybe try switching over to InnoDB to prevent row locking. You could also try doing some table duplication and query from secondary(temp) db tables to reduce the load from your primary db. Obviously without us knowing your DB Schema this is just me "throwing a suggestion out there."

Thirdly, unless you are hitting a db with 1million plus rows and you are getting a Connection Busy error, chances are pretty good that you need to talk with your hosting provider. Even in a shared server these type of error messages (if your code is as optimized as can be) is almost always a result of your server not having enough memory allocated.

As was indicated by cyberkiwi, you could try dropping ADOdb into your ftp and running a simple loop through it, just to see if the internal memory allocation of ADOdb can help at all. It would be as simple as uploading ADOdb including ADOdb and running the following loop:

$Data = $connection->Execute("SELECT field1, field2, field2 FROM subjects ORDER BY position ASC LIMIT 0, 10");
if (is_object($Data)&&(!$Data->EOF))
{
    while (!$Data->EOF)
    {
        //
        // string, array, echo, whatever here
        //
    $Data->MoveNext();
    }
}
unset($Data);

In the end though these type of errors are either a result of a poorly written SQL statement that is causing multiple connections to be stalling (in which case ask for your "max. concurrent connections" to be increased) or just not enough onboard memory or php allocated memory (in which case ask for your "memory_limit" to be increased).