Maximum decimals saved in Double and float

Archives Yii 1.0.x Discussions
1

I have a mysql table with a double field and a simple form created with Yiic to manage the table, if I insert in the field a number with more 6 decimal digits for example 9,1234567 when I save the number it is rounded after the sixth decimal, so it is saved as 9,123457. I try also with a float field and it saves maximum 5 decimals (always rounded).

I don't understand if this is a problem of Yii or a php problem or mysql problem. I found on the web that there was such a bug in php 5.2.1 version (it saved maximum 6 decimals), but I have the latest version of php (5.2.9) and mysql (5.1.36), and moreover if I use phpmyadmin all decimals are saved correctly. (could be a problem of pdo?)

Someone can try if they have the same problem? thanks

2

If the field is double type, then Yii will use PHP double type to represent the value. Other than this, Yii doesn't do any rounding or truncation. So most likely, this is caused by PDO. Maybe you can do some test using PDO directly to confirm this?

3

I made some test with PDO and I found that the problem is in the PDOStatement::bindValue method (in Yii it is used in CDbCommand::bindValue). I discover this:

if I do:

$sth->bindValue(':myvar', 9.1234567, PDO::PARAM_STR);

or $sth->bindValue(':myvar', 9.1234567);

the insert is wrong, decimals are rounded

if I do:

$sth->bindValue(':myvar', 9.1234567, PDO::PARAM_INT);

the insert is OK all decimals saved

In Yii there is:

$this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value)));

I verified that getPdoType return 2 that is PDO::PARAM_STR

The only link I found about this is here :

http://stackoverflow…ow-does-it-work

(it speaks about bindparam but i think is the same)

it shows the pdo code:

if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_STR && param->max_value_len <= 0 && ! ZVAL_IS_NULL(param->parameter)) {

                if (Z_TYPE_P(param->parameter) == IS_DOUBLE) {

                        char *p;

                        int len = spprintf(&p, 0, "%F", Z_DVAL_P(param->parameter));

                        ZVAL_STRINGL(param->parameter, p, len, 0);

                } else {

                        convert_to_string(param->parameter);

                }

        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_INT && Z_TYPE_P(param->parameter) == IS_BOOL) {

                convert_to_long(param->parameter);

        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_BOOL && Z_TYPE_P(param->parameter) == IS_LONG) {

                convert_to_boolean(param->parameter);

        }

Do you think there is a bug on the routine?

4

Have you tried to insert directly this number into MySQL? Or can you tell your column data type?

5

Quote

Have you tried to insert directly this number into MySQL? Or can you tell your column data type?

I tried to insert the number with PhpMyAdmin and there isn't any problem (but probably it doesn't use PDO or, if use it, it doesn't use bindvalue function).

I tried also to use PDO without the bindvalue function (so i create the insert statement indicating directly the number) and also in this case any problem. So I think the problem is in bindvalue method.

The column datatype is double (but the problem is also with float datatype)

6

Using PDO directly also has the problem? If so, you may file a bug report to PHP developer team.

7

Quote

Using PDO directly also has the problem? If so, you may file a bug report to PHP developer team.

Yes as I explained the problem happens also directly with PDO, so I will report a bug to PHP d.t.

8

Have you specified ‘decimals’ in MySql? I experienced the same problem, i was using FLOAT field, causing rounding problemas with 2 digits!! So, I changed it to double(13,2) and solved the problem.

9

I have the same problem trying to save float … 10 decimals are truncated to 5