[Solved] Split database question

Discuss the database features
Post Reply
bydindi
Posts: 7
Joined: Thu Feb 19, 2015 8:24 pm

[Solved] Split database question

Post by bydindi »

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! :super:
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)
Last edited by bydindi on Tue Feb 24, 2015 2:14 am, edited 2 times in total.
OOo 4.1.1 on windows Vista
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Split database question

Post by MTP »

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?)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
bydindi
Posts: 7
Joined: Thu Feb 19, 2015 8:24 pm

Re: Split database question

Post by bydindi »

(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])
OOo 4.1.1 on windows Vista
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Split database question

Post by Arineckaig »

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.
Last edited by Arineckaig on Sat Feb 21, 2015 4:14 pm, edited 2 times in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split database question

Post by Villeroy »

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bydindi
Posts: 7
Joined: Thu Feb 19, 2015 8:24 pm

Re: Split database question

Post by bydindi »

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?
OOo 4.1.1 on windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split database question

Post by Villeroy »

The table contains the same figures. But they should behave a bit differently when used in arithmetics.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Split database question?

Post by DACM »

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
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
bydindi
Posts: 7
Joined: Thu Feb 19, 2015 8:24 pm

Re: Split database question

Post by bydindi »

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
OOo 4.1.1 on windows Vista
Post Reply