Yii Framework Forum: [SOLVED] SQL Server 2008 returning blank LastInsertID - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[SOLVED] SQL Server 2008 returning blank LastInsertID

#1 User is offline   vario 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 03-January 10

Posted 03 October 2011 - 06:47 AM

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.
0

#2 User is offline   0onie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 19-October 11

Posted 17 November 2011 - 04:51 AM

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

Thanks.
0

#3 User is offline   0onie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 19-October 11

Posted 17 November 2011 - 11:11 PM

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,
1

#4 User is offline   nraj 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 16-November 11

Posted 18 November 2011 - 01:49 AM

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

#5 User is offline   vario 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 03-January 10

Posted 13 January 2012 - 04:50 AM

View Post0onie, on 17 November 2011 - 11:11 PM, said:

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,
I just wanted to say thank you for this - that's a perfect fix.
0

#6 User is offline   Led 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 186
  • Joined: 16-January 12

Posted 22 January 2012 - 02:37 AM

View Postvario, on 13 January 2012 - 04:50 AM, said:

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


One day struggling. Thanks a lot
0

#7 User is offline   Diego Rodriguez 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 25-September 11

Posted 28 January 2012 - 07:12 AM

I had the same problem, Thank you a lot
0

#8 User is offline   cksaito 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 19-October 11
  • Location:Brazil

Posted 13 February 2012 - 11:21 AM

It Works for me too!
0

#9 User is offline   tommytcchan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 19-February 12

Posted 19 February 2012 - 09:31 PM

the devs should roll this into the latest Yii release.
0

#10 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,138
  • Joined: 17-January 09
  • Location:Russia

Posted 21 February 2012 - 07:11 PM

Can you help with pull request and testing it?
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#11 User is offline   Diego Jaxx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 24-February 12

Posted 24 February 2012 - 01:58 PM

Many thanks! Solved the problem!
0

#12 User is offline   fl007 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 27-July 11

Posted 07 March 2012 - 06:07 AM

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!)
0

#13 User is offline   Deckard 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 21-October 11

Posted 19 June 2012 - 01:31 AM

Created pull request: https://github.com/y...ft/yii/pull/840
0

#14 User is offline   Shelby Hill 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 03-April 13

Posted 08 October 2013 - 04:50 PM

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?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users