[daisy] Decimal fields don't store numbers greater than 99'999

[Andreas Deininger]

2008/5/27 Olivier Lange <wire at petit-atelier.ch>:
> Hi all,
>
> Using Daisy 2.2 and MySQL 5.0.27, I got an exception while trying to
> save a document where I entered the value 120'000.05 within a
> decimal field:
>
>  Problem storing document.
>  Data truncation: Out of range value adjusted for column
> 'decimalvalue' at row 1
>
> It is the repository database which throws that exception. The size
> (10,5) of the 'decimalvalue' column of the table 'thefields'
> restricts the range to 0-99'999.
>
> mysql> desc thefields;
> +---------------------+---------------+------+-----+---------+-------+
> | Field               | Type          | Null | Key | Default | Extra
>  +---------------------+---------------+------+-----+---------+-------+
> ...
> | decimalvalue        | decimal(10,5) | YES  | MUL | NULL    |
> ...
> +---------------------+---------------+------+-----+---------+-------+
> 25 rows in set (0.01 sec)
>
> The values I need to store are amounts, with two digits precision,
> range 0-1'000'000.
>
> As a workaround, I increased to 20 the maximum number of digits
> (while keeping a maximum range of 5):
>
>  mysql> ALTER TABLE thefields
>         MODIFY COLUMN decimalvalue DECIMAL(20,5);
>
> and restarted the repository server. As the Java couterpart of that
> column is a java.math.BigDecimal, I think this change should have no
> unexpected effect. Is that right?
>
> Anyhow, I would suggest making this schema change a default.

+1 for this suggestion.
I ran into this issue a while ago, too.


Regards
Andreas