Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is "New transaction is not allowed" error in PHP and SQLSRV driver for?

I'm working on a web application written with PHP and uses SQL Server 2008. To connect to database, I used SQLSRV driever of Microsoft. In a part of this application, I have to use SQL Transactions. As Microsoft suggested, I did it exactly based on this article. The main processes in my codes follow these steps:
1- starting sql transaction
2- send information to PHP files through jQuery and check the result sent by JSON
3- rollback if the result was false and go to the next query if it was true.
4- commit transactions if no error occurred and all results were ok.

// This is my pseudo code
if (sqlsrv_begin_transaction( $sqlsrv->sqlsrvLink ) === true) {

  $firstQuery = sqlsrv_query($stmt1);
  if (!$firstQuery) {
     sqlsrv_rollback();
  } else {
     $nextQuery = sqlsrv_query($stmt2);
     if (!$nextQuery) {
        sqlsrv_rollback();
     } else {
        sqlsrv_commit();
     }
  }

} else {
   print_r(sqlsrv_errors()); // Here is where I get the error below.
}

The problem I have is this error:

[Microsoft][SQL Server Native Client 10.0][SQL Server] New transaction is not allowed because there are other threads running in the session

I'm using SQLSRV driver ver.2.
What is this error for? How can I solve it?

I included the my own sqlsrv class to the first part of index.php containing the methods below:

function __construct($dbServerName,$dbUsername,$dbPassword,$dbName)
{
    $connectionInfo = array("Database"=> $dbName, "CharacterSet" => "UTF-8");
    $this->sqlsrvLink = sqlsrv_connect($dbServerName, $connectionInfo);
    if ($this->sqlsrvLink === false) {
        $this->sqlsrvError = sqlsrv_errors();
    }
}

function __destruct()
{
    sqlsrv_close($this->sqlsrvLink);
}
like image 320
Mohammad Saberi Avatar asked Nov 13 '22 09:11

Mohammad Saberi


1 Answers

i think you should set MultipleActiveResultSets to true when you want to connect to sql server :

 $conn = sqlsrv_connect('127.0.0.1', array
        (
           'Database' => 'Adventureworks',
           'MultipleActiveResultSets' => true, // MARS ENABLED
        ));

http://php.net/manual/de/ref.pdo-sqlsrv.connection.php

like image 101
Saeed M. Avatar answered Nov 16 '22 02:11

Saeed M.