Update Tries To Save Non-Safe Fields

Hi - I have a computed column which is on a SQL Server database table. When I initially generated the model for this table, that column did not yet exist. I have a view and action that allows me to add data to this table. When I want to update though, I get a SQL error because the SQL contains this computed column when it should not. How can I address this? How does it even know to include this column - I thought only attributes marked as ‘safe’ would be saved?

pls help :-/

Hi,

Please try to give this rule in rules method

‘safe’,‘on’=>‘create’

I hope this will solve your problem

Validation will not happen on update only for that field.

Thanks

chandran nepolean

Hi - thanks for the response …

I don’t fully understand. I never want to set this field, not on create or update. It is a database computed column.

Just to test though, I set in rules:

array(‘ComputerColumnName’, ‘safe’, ‘on’=>‘create’),

but I still get the same error on update …

Hi

If you want this attribute calculated by code without affected by validate/massive assigment etc, remove entirely from the rules.

Did you have to calulate this attribute? like just before save ?

Hi - this attribute never was in the model or the rules, just on the database … very bizarre.

The field is updated via a database trigger.

Did you check if you can update records by raw sql directly in your database ?

If the attributes not exists in the model then it shouldn’t any problem.

Please post your model/controller-action/view from code to detect the problem.

Hi there - Thanks again for the quick response. I can run an update on the same record directly on the database no problem. I need to do some more investigation. I will post what the issue was if I fix it (no matter how silly on my side) … or I might be back for more help!

ok sifa.

Remember that Yii send to database (save/update) only the attributes that are set

in model either on rules or global variables in class that matches with table schema

Just an update - I have a bunch of master tables, which was all checked a while back (add, edit, delete) all worked. Since then a computed column has been added to all the tables - including the master tables. Now the update functionality on those master tables no longer work. I get the same error (example below):




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The column "DisplayName" cannot be modified because it is either a computed column or is the result of a UNION operator.. The SQL statement executed was: UPDATE [dbo].[Species] SET [SpeciesGroupID]=:yp0, [SpeciesName]=:yp1, [Targeted]=:yp2, [ETPSpecies]=:yp3, [Bait]=:yp4, [DateTimeUpdated]=:yp5, [ChangedByPersonID]=:yp6, [DisplayName]=:yp7 WHERE [dbo].[Species].[SpeciesID]='8'



These models definitely does not have this ‘DisplayName’ field … they have been untouched for weeks…

In your error message there are

1)The column "DisplayName" cannot be modified

2 SET form …[DisplayName]=:yp7 … WHERE …

so, through query you force to set the DisplayName that computed automatically…

Please add in your model


function beforeSave() {

 var_dump ($this->attributes();

 die();

}



post the results to analyse the problem

did you mean getAttributes()? Below is the output … I really don’t understand where it gets DisplayName from… see the full model code below …

Results from var_dump




array(9) { ["SpeciesID"]=> string(1) "8" ["SpeciesGroupID"]=> string(1) "1" ["SpeciesName"]=> string(6) "aaaaaa" ["Targeted"]=> string(1) "1" ["ETPSpecies"]=> string(1) "1" ["Bait"]=> string(1) "1" ["DateTimeUpdated"]=> NULL ["ChangedByPersonID"]=> NULL ["DisplayName"]=> string(14) "aaaaaa (Crabs)" }



Species Model




<?php


/**

 * This is the model class for table "Species".

 *

 * The followings are the available columns in table 'Species':

 * @property string $SpeciesID

 * @property string $SpeciesGroupID

 * @property string $SpeciesName

 * @property boolean $Targeted

 * @property boolean $ETPSpecies

 * @property boolean $Bait

 * @property string $DateTimeUpdated

 * @property string $ChangedByPersonID

 *

 * The followings are the available model relations:

 * @property WildlifeInteraction[] $wildlifeInteractions

 * @property HaulBait[] $haulBaits

 * @property LandedCatch[] $landedCatches

 * @property CatchUnitSpeciesWeight[] $catchUnitSpeciesWeights

 * @property FishingActivityGearCatch[] $fishingActivityGearCatches

 * @property SpeciesGroup $speciesGroup

 * @property Person $changedByPerson

 * @property FishingActivityGearReturn[] $fishingActivityGearReturns

 * @property FishingActivityTargetSpecies[] $fishingActivityTargetSpecies

 */

class Species extends CActiveRecord

{

    public $speciesGroup_title;

	/**

	 * Returns the static model of the specified AR class.

	 * @param string $className active record class name.

	 * @return Species the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


    function beforeSave() {

      var_dump ($this->getAttributes());

      die();

    }


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'Species';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('SpeciesGroupID, SpeciesName, Targeted, ETPSpecies, Bait', 'required'),

			array('SpeciesGroupID, ChangedByPersonID', 'length', 'max'=>18),

			array('SpeciesName', 'length', 'max'=>255),

			array('DateTimeUpdated', 'safe'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('SpeciesID, SpeciesGroupID, SpeciesName, Targeted, ETPSpecies, Bait, DateTimeUpdated,

			ChangedByPersonID, speciesGroup_title', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'wildlifeInteractions' => array(self::HAS_MANY, 'WildlifeInteraction', 'SpeciesID'),

			'haulBaits' => array(self::HAS_MANY, 'HaulBait', 'SpeciesID'),

			'landedCatches' => array(self::HAS_MANY, 'LandedCatch', 'SpeciesID'),

			'catchUnitSpeciesWeights' => array(self::HAS_MANY, 'CatchUnitSpeciesWeight', 'SpeciesID'),

			'fishingActivityGearCatches' => array(self::HAS_MANY, 'FishingActivityGearCatch', 'SpeciesID'),

			'speciesGroup' => array(self::BELONGS_TO, 'SpeciesGroup', 'SpeciesGroupID'),

			'changedByPerson' => array(self::BELONGS_TO, 'Person', 'ChangedByPersonID'),

			'fishingActivityGearReturns' => array(self::HAS_MANY, 'FishingActivityGearReturn', 'SpeciesID'),

			'fishingActivityTargetSpecies' => array(self::HAS_MANY, 'FishingActivityTargetSpecies', 'SpeciesID'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'SpeciesID' => 'Species',

			'SpeciesGroupID' => 'Species Group',

            'speciesGroup_title' => 'Species Group',

			'SpeciesName' => 'Species Name',

			'Targeted' => 'Targeted',

			'ETPSpecies' => 'Etpspecies',

			'Bait' => 'Bait',

			'DateTimeUpdated' => 'Date Time Updated',

			'ChangedByPersonID' => 'Changed By Person',

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->compare('SpeciesID',$this->SpeciesID,true);

		$criteria->compare('SpeciesGroupID',$this->SpeciesGroupID,true);

		$criteria->compare('SpeciesName',$this->SpeciesName,true);

		$criteria->compare('Targeted',$this->Targeted);

		$criteria->compare('ETPSpecies',$this->ETPSpecies);

		$criteria->compare('Bait',$this->Bait);

		$criteria->compare('DateTimeUpdated',$this->DateTimeUpdated,true);

		$criteria->compare('ChangedByPersonID',$this->ChangedByPersonID,true);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}

Ok, I got it

check this


public function beforeSave() {

    unset($this->DisplayName);

    return parent::beforeSave();

}

Thank you so much [color="#FF0000"]KonApaz [/color]- that did the trick! I still don’t understand why it is happening :huh: , but I am so happy to have a fix :D

Ok

lets explain that!

The CActiveRecord class fetch all attributes from your table schema according to the name of your table.

when the record is new all are set to null or to default values (that’s why worked)

when the record is stored for second time in your database the object has the previous values, so the DisplayName has a value.

Now the key of your issue:

after of load the model has values. when you update it, Yii sends the

DisplayName (like other attributes) back to the database to store, so your trigger fails!

What I did ? remove this attribute value and the attribute has no value to send back to the database (?! I have a doubt for that) so Yii send all the other fields to store to the database.

My doubt is the ‘null’ sent like a ‘value’ !

for example "Update myTable set MyColumn = NULL", so your trigger will could fail !!

I dodn’t know what is your trigger and if could be fail when the key-field set simultaneously to null.

Could anyone explain what happen in this case?

Now I have my own question and doubt :)

Thanks for your voting :)

Note unset($this->someAttribute) not remove the member-variables but set it to null, I couldn’t find how to unset variable from class. If I found it I will fully understand how the above works