Apache OpenOffice Community Forum

Hello everyone
I'm new in this forum, but "senior" in 'Italian forum.
I'm trying to convert a embebbed database, split database by following your advice. viewtopic.php?f=83&t=65980 thanks DACM!
the database works a treat! I use it at work and I can do the bills as the "old" database embebbed.
I find, however, the difficulties with the [Wizard] Create a new 'split' HSQL 2.x database for creating new databases. viewtopic.php?t=61183
In practice:
Let's say I have to charge an amount of € 15 including VAT, the amount I put in a box and through a query extrapolate the taxable: 15 / 1.22 = 12.295 which approximated
me from a EUR 12.30
In the same query me the total revenue: (15 / 1.22) * 1.22 = 15.01 and this is correct! Indeed 12.30 * 1.22 results in 15.006 that given to two decimal places as a result 15.01.
I can not get the same result with the database created by the wizard, am I wrong or is there a different calculation between the two database split?

sorry for my english (blame google translator)

What incorrect result are you getting? Does (15.00 / 1.22) * 1.22 give the result you expect? (That is, making sure the first number in the calculation has the decimal accuracy you want to see in the result?)

(15 / 1.22) * 1.22 = 15.01 for my purposes is correct!
However if I try the file "wizard Create a new 'split' HSQL 2.x", the result is 15.00 instead 15.01
I realized that creating a new table, the settings values [FLOAT] and [REAL] not remain stored (if I close the table in edit mode and re-open, the settings are reset back to [DOUBLE])

I can not get the same result with the database created by the wizard, am I wrong or is there a different calculation between the two database split?

I lack knowledge of the Base wizard for creating tables as it is best avoided, but g Generally when a database is used for financial or accounting purposes the data-type to be used for storing currency values in the database should be Decimal with provision for at least 4 decimal places to ensure a reasonable chance of accuracy when rounding results to 2 decimal places.

Try the decimal data type.
DECIMAL(9,4) is a positive or negative number with 9 digits, 5 in front of the decimal point and 4 behind. (-99999,9999 until 99999,9999).
ALTER TABLE "T" ALTER COLUMN "C" DECIMAL(9,4)

Villeroy wrote:Try the decimal data type.

executing this statement I get the message "command completed successfully" but the table is not changed.
One question:
I can still use the first option? viewtopic.php?f=83&t=65980
recommended me?

The table contains the same figures. But they should behave a bit differently when used in arithmetics.

bydindi wrote:(15 / 1.22) * 1.22 = 15.01 for my purposes is correct!
However if I try the file "wizard Create a new 'split' HSQL 2.x", the result is 15.00 instead 15.01
I realized that creating a new table, the settings values [FLOAT] and [REAL] not remain stored (if I close the table in edit mode and re-open, the settings are reset back to [DOUBLE])

In this case, you converted an 'embedded database' to a 'split HSQL 1.8 database.' And note that the particular Base template you used generates a new 'split HSQL 2.x database.' So you have two databases, each running a different version/generation of HSQLDB. Otherwise, the particular steps and/or configurations are not related to the issue of data-types and SQL math.

As others have identified, this thread is actually about differences between the SQL engines in use: HSQLDB 1.8 and HSQLDB 2.x. There's apparently a difference in the 'default' settings that lead to different mathematical 'rounding' or 'truncation' (SQL math with INTEGERs -or- perhaps 'precision' and 'scale' handling) between HSQLDB 1.8 and HSQLDB 2.x.

Here's a few excerpts:

HSQLDB 1.8 documentaion:

In table definition statements, HSQLDB accepts size, precision and scale qualifiers only for certain types: CHAR(s), VARCHAR(s), DOUBLE(p), NUMERIC(p), DECIMAL(p,s) and TIMESTAMP(p).

By default specified precision and scale for the column is simply ignored by the engine. Instead, the values for the corresponding Java types are always used, which in the case of DECIMAL is an unlimited precision and scale. If a size is specified, it is stored in the database definition but is not enforeced by default. Once you have created the database (before adding data), you can add a database property value to enforce the sizes:

SET PROPERTY "sql.enforce_strict_size" true

This will enforce the specified size and pad CHAR fields with spaces to fill the size. This complies with SQL standards by throwing an exception if an attempt is made to insert a string longer than the maximum size. It also results in all DECIMAL values conforming to the specified precision and scale.

HSQLDB 2.x Guide > Chapter 2. SQL Language > Data Types and Operations > Type Length, Precision and Scale:

In older version of HyperSQL, all table column type definitions with a column length, precision or scale qualifier were accepted and ignored. HSQLDB 1.8 enforced correctness but included an option to enforce the length, precision or scale.

In HyperSQL 2, length, precision and scale qualifiers are always enforced.

SQL basics: Number data types...as suggested by Arineckaig and outlined by Villeroy above

Using INTEGERS in SQL calculations (while expecting a SCALE of 2 or more)...similar to MTP's suggestion above

How to format SQL results...various post-processing formatting options

MTP, Arineckaig,Villeroy,DACM
thanks to all for your contributions, I'm currently "migrating" on hsql 1.8, will test the operation with my database that I use for the past three years.
I had problems with data loss and I hope that with the version "split" things will improve. my doubt was the choice of HSQL 1.8 and a newer version (HSQL 2.X) but currently are not able to make changes to what I created with my Embebed database

Paolo