Request Executed By Yii When Validating Model

Hi,

I am trying to migrate a project from MySQL to Oracle Database.

I am using "unique" rules to validate relation on my models.

I am using this code to log my MySQL request :


array(

	'class'=>'CFileLogRoute',

	'levels'=>'error, warning, trace',

	'categories'=>'system.db.*',

	'logFile'=>'sql.log',

),

When validating the model, this request is executed (according to the sql.log file):


WITH USER_SQL AS (SELECT 1 FROM "users" "t" WHERE countryid), 

     PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL) SELECT * 

FROM PAGINATION

WHERE rownum <= 1 

It seems that something like “= :val” is missing after countryid and quotes ("") are missing around countryid too. I tried to add quotes around countrid in the definition of the relation, but it doesn’t seem to help.

(I just tried to add quotes around countrid in the unique rule definition and they are added on the request).

This Oracle SQL request causes an error :

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 920 OCIStmtExecute: ORA-00920: invalid relational operator (ext\pdo_oci\oci_statement.c:148)

But if we execute it manually with "countryid" = 1, it works fine.

I don’t really know what to do for this ? Where can I look at ?

Thank you !

Hi,

Please can you paste your controller code.That is important. I think problem is there…

Thanks

chandran nepolean

Please post your rules() where you define the unique validator.

Hi,

Below is content of controller for this action :


	public function actionCreate()

	{

		$model=new User('create');


		$this->performAjaxValidation($model);


		if(isset($_POST['User']))

		{

			// On récupère les attributs et on met une date de création

			$model->attributes=$_POST['User'];

			$model->creationDate = date('Y-m-d H:i:s');

			

			// Si l'utilisateur n'est pas SA, le countryid de l'utilisateur est forcément celle du créateur

			if (Yii::app()->user->model->role != 'SA')

				$model->countryid = Yii::app()->user->model->countryid;

			

			if ($model->validate())

			{

				// Si tout est correct, on met le mot de passe en md5 dans un premier temps

				$password = $model->password;

				

				$model->password = md5($model->password);

				if($model->save(false))

				{

					// On sauvegarde le model, et on créé un has sha256 basé sur l'id de l'utilisateur (c'est pour ca qu'on a que du md5 avant, on avait pas encore l'id de l'utilisateur)

					$model->password = hash('sha256', hash('sha256', hash('sha256', ($password.$model->id.preg_replace('#[^a-zA-Z0-9]+#','',$model->creationDate)))));

					$model->save(false);

					$this->redirect(array('index'));

				}

			}

		}


		$this->render('create',array(

			'model'=>$model,

		));

	}

and rules :


	public function rules()

	{

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

		// will receive user inputs.

		return array(

			array('username, password, email, countryid, creationDate, role', 'required'),

			

			// CountryId must be a correct Country

			array('countryid', 'numerical', 'integerOnly'=>true),

			array('"countryid"','exists', 'attributeName'=>'"id"', 'className'=>'Country'),

			

			// The username must be unique and between 4 and 50 caracters

			array('username', 'length', 'max'=>50, 'min'=>4),

			array('username','unique'),

			

			// Password and password confirm must match. 

			array('password', 'length', 'max'=>64, 'min'=><img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,

			array('passwordConfirm', 'required', 'on'=>'create'),

			array('passwordConfirm', 'compare', 'compareAttribute'=>'password', 'on'=>'create'),

			

			// Those fields are used for setting a new password

			array('newPasswordConfirm', 'compare', 'compareAttribute'=>'newPassword', 'on'=>'update'),

			array('newPassword', 'length', 'max'=>64, 'min'=>8, 'on'=>'update', 'allowEmpty'=>true),

			

			// Roles : Super Administrator, Administrator for Country, Country Sales

			array('role', 'length', 'max'=>2),

			array('role', 'in', 'range'=>array('SA','A','C')),

			

			// Use function checkRoleAuthorization to check Role

			array('role', 'checkRoleAuthorization'),

			

			// Email

			array('email', 'length', 'max'=>150),

			array('email','email'),

			

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

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

			array('id, username, role, email, countryid', 'safe', 'on'=>'search'),

		);

	}

Hi,

This rules lookes something strange…check whether its returning countryid

 array('&quot;countryid&quot;','exists', 'attributeName'=&gt;'&quot;id&quot;', 'className'=&gt;'Country'),

This country id is assigend according to user role… Please check are you in correct role.

 if (Yii::app()-&gt;user-&gt;model-&gt;role &#33;= 'SA')


     &#036;model-&gt;countryid = Yii::app()-&gt;user-&gt;model-&gt;countryid;

You shouldn’t quote attribute names in rules.

Thanks for your feedback. Indeed the rule was wrong :


array('"countryid"','exists', 'attributeName'=>'"id"', 'className'=>'Country'),

change it by this and now it works :


array('countryid','exist', 'attributeName'=>'id', 'className'=>'Country'),

trouble was "exists" instead of "exist".

Now I see this error :

2013/10/16 08:15:34 [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[HY000]: General error: 1858 OCIStmtExecute: ORA-01858: a non-numeric character was found where a numeric was expected

(ext\pdo_oci\oci_statement.c:148).

By in sql.log I have only this so I can not check which values is wrong :

The SQL statement executed was: INSERT INTO "users" ("role", "username", "email", "countryid", "password", "creationDate") VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5) RETURNING "id" INTO :RETURN_ID.

I thought about the date, but I set it this way so I would have expect it to be okay :

$model->creationDate = new CDbException(‘SYSDATE’);

How can I check which columns fail ?

Thanks.

That’s what rules and validation before saving is for, to make sure the data types will match with schema of the database so the query won’t fail.

I treat all application exceptions as my fault, that is it indicates that some condition has not been considered in the code and the code needs adjustments. That’s why I log everything to email.

Try to adjust your validation rules so no database exceptions are raised.

Euh ? That definitely is not correct!

Maybe try $model->creationDate = date(‘Y-m-d H:i:s’, time());

I suppose he meant CDbExpression.