Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Sqlsrv driver has pdo features for transaction but in framework it use old transaction functions #112

Closed
qiangxue opened this Issue Feb 15, 2012 · 2 comments

Comments

4 participants
Owner

qiangxue commented Feb 15, 2012

What steps will reproduce the problem?
Try to insert row in some table using transaction with pdo_sqlsrv driver (version 2.0)

What is the expected output? What do you see instead?
Inserted must be successful, but there are specific errors about MARS (feature of MSSQL) with point to "beginTransaction" (CMssqlPdoAdapter.php, line 45 - $this->exec('BEGIN TRANSACTION');)

What version of the product are you using? On what operating system?
Yii 1.1.8, sqlsrv 2.0, MSSQL 2008

Please provide any additional information below.
New driver for MSSQL - sqlsrv - already has features for transaction - begin, commit and revoke, like other pdo drivers. So, for sqlsrv don't need use old version of using transaction ($this->exec('BEGIN TRANSACTION');), moreover, old version raises errors. Sqlsrv must use common PDO class.

Solving - change line 401 at file CDbConnection.php:
if($driver==='mssql' || $driver==='dblib' || $driver==='sqlsrv')
to
if($driver==='mssql' || $driver==='dblib')
Now sqlsrv uses PDO driver instead of CMssqlPdoAdapter and all good!

Migrated from http://code.google.com/p/yii/issues/detail?id=2691


earlier comments

alex.lukjanenko said, at 2011-07-29T12:03:39.000Z:

Some problem - pdo_sqlsrv has wrong implementation of lastInsertId(): when parameter $sequence is null, method work wrong - return nothing, and in this case we must call method without parameter for proper value of last insert id.

Because of this problem we can't just use PDO class for sqlsrv, we must implement method lastInsertId() (and nothing else!) in some PDO adapter class like CMSsqlPdoAdapter or see what driver we use when call methods of CMSsqlPdoAdapter.

Differents of CMssqlPdoAdapter and required CSqlsrvPdoAdapter:
CMssqlPdoAdapter extends 4 methods - lastInsertId, beginTransaction, commit and rollBack.
CSqlsrvPdoAdapter must extends only 1 method (at now :) ) - lastInsertId:
public function lastInsertId ($sequence=NULL)
{
$value = $sequence ? parent::lastInsertId($sequence) : parent::lastInsertId();
$value=preg_replace('/[,.]0+$/', '', $value); // issue 2312
return strtr($value,array(','=>'','.'=>''));
}

kevinmuma said, at 2011-11-08T18:40:53.000Z:

I am using php_pdo_sqlsrv_53_nts_vc9.dll version 2.0.1802.200 running against MSSQL 2005. The PDO driver's implementation of lastInsertId is working for me.

None of the Yii 1.1.8 CMssqlPdoAdapter override methods are working for me. So using the PDO driver instead of CMssqlPdoAdapter fixed everything for me.

alex.lukjanenko said, at 2011-11-09T06:31:33.000Z:

Maybe this issue exists only on MSSQL 2008 ... I'll try to do test set.

qiang.xue said, at 2012-01-01T03:36:54.000Z:

set for 1.1.10 milestone

iGrog commented Mar 29, 2012

Same thing:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. (C:\WWW\framework\db\schema\mssql\CMssqlPdoAdapter.php:44)

#0 C:\WWW\manager\framework\framework\db\schema\mssql\CMssqlPdoAdapter.php(44): PDO->exec('BEGIN TRANSACTI...')

Contributor

resurtm commented Aug 26, 2012

@iGrog temporary solution is to disable MARS. Add ;MultipleActiveResultSets=false to your DSN, so it should look like:

$dsn='sqlsrv:Server=localhost;Database=testdb;MultipleActiveResultSets=false';

@resurtm resurtm added a commit to resurtm/yii that referenced this issue Aug 29, 2012

@resurtm resurtm Bug #112: MSSQL: database abstraction layer now uses native transacti…
…on support of the SQLSRV driver.
62d0119

@samdark samdark added a commit that referenced this issue Aug 29, 2012

@samdark samdark Merge pull request #1307 from resurtm/112-sqlsrv-transactions
Bug #112: MSSQL: database abstraction layer now uses native transaction support of the SQLSRV driver.
0b41f60

@samdark samdark closed this Aug 29, 2012

@cebe cebe added a commit to cebe/yii that referenced this issue Sep 3, 2012

@cebe cebe Merge branch 'master' into 93-relational-beforefind-criteria-modifica…
…tion

* master: (180 commits)
  Updated changelog with last-modified date change.
  Changed the CHttpCacheFilter to use RFC 1123 complaint dates when returning the last-modified header.
  tabs...
  gii: better default validation "length" rule for fixed-point / floating-point field type (MYSQL)
  fixes #1319
  updated comment in CHttpSession
  Update framework/YiiBase.php
  Update docs/guide/database.arr.txt
  New unit test for enhanced $gCProbability s/getters in CHttpSession #486. Fixed those methods to process corner cases properly. Initial $gCProbability is now double(1.0), was > 1.0 before.
  Bug #112: MSSQL: database abstraction layer now uses native transaction support of the SQLSRV driver.
  Forgot about spaces in comments creating procedure call. [ci skip]
  Better comments testing method name.
  Forgot about local MSSQL database settings.
  MSSQL unit testing SQL file reformatted and decorated. MSSQL testing class improved. Added unit tests for column comments retrieving.
  Removed old message.
  Update docs/guide/topics.i18n.txt
  Update docs/guide/topics.i18n.txt
  Requirements checker: russian (ru_RU) messages.
  Minor fixes and refinements to the MSSQL unit tests.
  better fix for issues #124
  ...

Conflicts:
	UPGRADE
be1fbf1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment