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

[Olivier Lange]

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.

***

I actually ran into that problem while trying to create a document
from Javascript:

  var oDoc = goRepo.createDocument( "Die Welt ist nicht gerecht",
"tbProject", gsBranch, gsLang);
  oDoc.addToCollection( goColl);
  oDoc.setField( "tbBetrag", java.math.BigDecimal( "120000.0000"));
  oDoc.save();

which leads to the following exception and isolates clearly its source:

  org.mozilla.javascript.WrappedException: Wrapped
org.outerj.daisy.repository.RepositoryException: Received exception
from repository server. (<stdin>#48)
  <snip/>
  Caused by: org.outerj.daisy.repository.RepositoryException:
Received exception from repository server.
  <snip/>
  Caused by:
org.outerj.daisy.repository.clientimpl.infrastructure.DaisyPropagatedException:
[org.outerj.daisy.repository.RepositoryException] Problem storing
document.
  Caused by:
org.outerj.daisy.repository.clientimpl.infrastructure.DaisyPropagatedException:
[com.mysql.jdbc.MysqlDataTruncation] Data truncation:
  Out of range value adjusted for column 'decimalvalue' at row 1

Regards,

--
Olivier

Le Petit Atelier de Génie logiciel
t. +41-21-6487665, m. +41-76-5166212, http://www.petit-atelier.ch/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3909 bytes
Desc: S/MIME Cryptographic Signature
Url : http://lists.cocoondev.org/pipermail/daisy/attachments/20080527/d6e32467/smime.bin