CDbException on inserting empty value for numeric fields

Archives Feature Requests
1

I’m using PostgreSQL, don’t know if it’s same for MySQL

In a table I have a field numeric(12,2) - it’s optional so it can be NULL

in the model rules I set the CNUmberValidator so that it checks if entered value is a number

The problem is if a user does not enter anything (because it’s optional) the CNumberValidator just return because it’s allowed to be empty

and then I’m getting this error:

[i]CDbCommand failed to execute the SQL statement: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type numeric: ""INSERT INTO "table" …

/srv/www/htdocs/yii/framework/db/CDbCommand.php(227[/i])

Seems like Yii is inserting empty string for a numeric value

I solved this for now by using a rule like:




    array('field','default','setOnEmpty'=>true,'value'=>0),

But it’s not a nice solution to keep tracking models/fields that needs this.

If possible the framework should be changed to insert a 0 or a NULL.

2

I had a similar problem, I actually wanted NULL values to be inserted in my database whenever any field was left blank. Also I wanted all input to be trimmed. So I extended ActiveRecord like that:




class MyCActiveRecord extends CActiveRecord {

    protected function beforeValidate($scenario) {

        $attributes = $this->getSafeAttributeNames($scenario);

        foreach($attributes as $attr) {

            if (gettype($this->$attr) == "string") {

                $this->$attr = trim(str_replace("\r","",$this->$attr));

            }

        }

        return parent::beforeValidate($scenario);

    }




    public function beforeSave() {

        $attributes = $this->getSafeAttributeNames($this->scenario);

        foreach($attributes as $attr) {

            if ($this->$attr === "") {

                $this->$attr = null;

            }

        }

        return parent::beforeSave();

    }

}

Maybe this helps

3

The problem is that for numerical field, we can’t represent it using PHP double because it would lose precision otherwise.

So the parameter is actually bound as a string.

I think setting the ‘default’ rule is necessary in this case.

4

But it’s not intuitive… maybe to add defaulValue to the CNumberValidate… th a default value of 0 or NULL…

One soulution could be to check for empty strings and change them to ‘NULL’ if the field allows NULL or to ‘0’ if it don’t.