[SOLVED] SQL Server 2008 returning blank LastInsertID

Archives Bug Discussions
1

Hi,

I’m running Yii 1.1.8 and SQL Server 2008, on Windows Server 2008 / IIS7 with PHP 5.3.8.

When saving a model via ActiveRecord, the getLastInsertID returns an empty value. Here’s the code I’ve written that would retrieve the ID, which is part of the model and runs as normal (as an Event after running $mdl->save()):





public function afterSave() { 

    $this->EntryID = Yii::app()->db->getLastInsertID();

    return parent::afterSave();

}

Here’s my database connection string:




'db' => array(

            'connectionString' => 'sqlsrv:Server=XXXXXXX;Database=XXXXXXX',

            'username' => 'XXXXXXX',

            'password' => 'XXXXXXX',

        ),

I’ve done similar code before with mySQL with success.

I’ve tried $this->EntryID = $this->getPrimaryKey() but this doesn’t appear to work either.

Is there anything I’m missing or is there another simple solution?

Currently I’m having to instantiate a new model and query on some of the attributes I’ve just saved in order to find out the ID, but this isn’t ideal!

Thanks in advance.

2

I have the same problem. I’m using Yii 1.1.8 and SQL Server Express 2008 RC 2.

Thanks.

3

Hello vario,

To solve your problem, you can change code in folder framework\db\schema\mssql\CMssqlPdoAdapter.php


	public function lastInsertId ($sequence=NULL)

	{

		$value=$this->query('SELECT SCOPE_IDENTITY()')->fetchColumn();

		$value=preg_replace('/[,.]0+$/', '', $value); // issue 2312

		return strtr($value,array(','=>'','.'=>''));

	}

to




	public function lastInsertId ($sequence=NULL){

		return $this->query('SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)')->fetchColumn();

	}

And use Yii::app()->db->lastInsertID for get the last insert id in database.

It works for me, hopefully it works for you

Thanks,

4

OOnie…Many thanks for your tweak on getLastInsertId for SQL server.

5

I just wanted to say thank you for this - that’s a perfect fix.

6

One day struggling. Thanks a lot

7

I had the same problem, Thank you a lot

8

It Works for me too!

9

the devs should roll this into the latest Yii release.

10

Can you help with pull request and testing it?

11

Many thanks! Solved the problem!

12

For those who do not want to touch the framework code, there is a workaround. As far as I can see, you could just safely use class PDO as long as you are not using transactions. To achieve this I created my own DBConnection class like so:


class DBConnection extends CDbConnection

{

	private $_attributes=array();


	protected function createPdoInstance()

	{

		$pdoClass=$this->pdoClass;

                

                //

                // Removed code block from original CDbConnection.

                // This way, $this->pdoClass will be used, which defaults to 'PDO'

                //


		return new $pdoClass($this->connectionString,$this->username,

									$this->password,$this->_attributes);

	}

}

Then, set this class in main configuration:


'db'=>array(

    'class' => 'DbConnection',

    // ...

),

Works for me (SQLSRV driver 2.0, SQL Server 2005 and 2008 RC2, from Yii 1.1.8; not using transactions!)

13

Created pull request: https://github.com/yiisoft/yii/pull/840

14

I’m having this exact issue currently and I’m running 1.1.13. The fix listed here is already in place I checked. But after you save a new model the redirect to the view always fails because nothing is available in the id. So it goes to id= nothing.

Anybody have any ideas why this would be happening?