MSSQL - Error converting data type varchar to bigint

I have created a model for student table in MSSQL database. When I update a record it causes an error saying

2009/07/17 05:46:25 [error] [system.db.CDbCommand] Error in executing SQL: UPDATE [dbo].[Students] SET [student_id]=:student_id, [first_name]=:first_name, [last_name]=:last_name, [address]=:address, [city]=:city, [state]=:state, [postcode]=:postcode, [phone]=:phone,  [current_group]=:current_group, [login_id]=:login_id, WHERE [dbo].[Students].[id]=2. Bind with parameter :student_id='ts', :first_name='S', :last_name='K', :address='Co', :city='Colombo', :state='Western', :postcode=112, :phone='0000000', :current_group='A', :login_id=NULL

2009/07/17 05:46:25 [error] [exception.CDbException] exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 10007 Error converting data type varchar to bigint. [10007] (severity 5) [(null)]' in D:xampphtdocsyiiframeworkdbCDbCommand.php:211

I think this is an issue in AR class with casting or converting values to bigint when generating the script. Here the login_id is a BIGINT type and it is a foreign key field which can be null.



Not quite sure about the problem. Do you mean :login_id should not be NULL?

Hi Qiang,

I have narrowed the problem down to the CDbColumnSchema, the typecast function reports bigint datatypes in mssql as "double". It then casts the value as a double which causes the above error about conversion of varchar to bigint. Please let me know if you need more information to reproduce the problem.

This seems to happen in conditions on find calls as well but I have not looked into where it is occuring

That was caused by the typecast function. I’ll list how I menage to overcome the issue. I overwrite the function typecast and modified the extractType in the CMssqlColumnSchema it looks like below.

	public function typecast($value)


		if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)

			return $value;


			return $this->type==='string' ? '' : null;



			case 'integer': return (integer)$value;

			case 'boolean': return (boolean)$value;

			case 'bigint':return number_format($value,0);

			case 'double': return (double)$value;

			case 'string': return (string)$value;

			default: return $value;



	protected function extractType($dbType)




		else if(strpos($dbType,'float')!==false || strpos($dbType,'real')!==false)


		else if(strpos($dbType,'int')!==false || strpos($dbType,'smallint')!==false || strpos($dbType,'tinyint'))


		else if(strpos($dbType,'bit')!==false)







hmm… this is not a viable solution because extractType() is supposed to return a PHP data type. Here you are returning ‘bigint’. Also the reason we treat bigint as double is because integer is not enough to represent bigint (the only way is to use string in fact).

I still don’t understand the error message you are showing here. It seems to be converting some varchar data to bigint?

For whatever reason if it is treated as a double MSSQL with PDO doesn’t like it and throws that misleading error. A very simeple test case is to create a table in my case “test” with a bigint column called “bigint_column” and nothing else. The following two lines of code will cause the error to be thrown

      $test->bigint_column = 1;


I have found simply removing


from the if statement in the extract type function in CMssqlColumnSchema to make it treat bigint as a string will work.

Do you mean for mssql, bitint is treated as a string for the database? that is, when you insert a row with bigint column, you need to use something like column_name=‘bigint value’ ?

Either column_name=bigint_value or column_name=‘bigint value’ seems to work when I set the attribute on the model and call the save method but only provided the extractType function returns the type as a string as per my previous post. If extractType returns it as double then neither column_name=bigint_value or column_name=‘bigint value’ will work.

Its actually a problem with the way the mssql PDO driver handles the big ints. Please find the solution below

In CMssqlColumnSchema line 29 change

if(strpos($dbType,'bigint')!==false || strpos($dbType,'float')!==false || strpos($dbType,'real')!==false)


if(strpos($dbType,'float')!==false || strpos($dbType,'real')!==false)

In CMssqlCommandBuilder change the contents of createFindCommand to


      if ( $criteria->select === '*' )

         $select = $table->getColumnNames();


         $select=!is_array($criteria->select) ? explode(',',$criteria->select) : $criteria->select;

      $changed = false;

      foreach($select as $key => $name)


         $name = trim($name);



            if ( strpos($column->dbType,'bigint')!==false )


               $select[$key]='CAST('.$name.' AS varchar) '.$name;

               $changed = true;




      if ( $changed )

         $criteria->select = $select;

      return parent::createFindCommand($table,$criteria);

What do you need to be able to include this in the codebase.