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.
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;
if($value==='')
return $this->type==='string' ? '' : null;
switch($this->type)
{
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;
}
}
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;
$test->save();
I have found simply removing
strpos($dbType,'bigint')!==false
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.
