TIP Adding numeric data types with fixed precision

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

TIP Adding numeric data types with fixed precision

Postby Bernd.N » Sun May 08, 2016 11:16 am

(Tip for Servoy beginners...)

When creating a numeric field inside Servoy, you get 'double precision' in Postgres.

For business applications, e.g. ERP or accounting systems, you want to avoid the default data type 'double precision', as that will lead to problems as some numeric values will be represented like 3.60999999997 or something like that.

Solution:
(1) Open an SQL editor and send the following line to add a numeric field with exactly two decimal places
ALTER TABLE posting_lines ADD COLUMN pli_credit_amount numeric(15,2)

(2) In Servoy, right click on the database, use 'Synchronise with DB Server information', and let Servoy add the new field to the Database Information File.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 474
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: TIP Adding numeric data types with fixed precision

Postby ROCLASI » Sun May 08, 2016 11:47 am

Servoy was never really designed to be a tool for managing your database schema's. It allows for some very basic actions on your database. For instance Servoy has no way of adding indexes or any (foreign key, unique, etc.) constraints or even changing existing column properties. You then need to use plain SQL in the SQL editor.
In my opinion, if you really serious about your database schema then you use a SQL tool designed for your DB vender.

For those who like to design their database schema's visually I can recommend DbWrench. This is a Java based, commercial (one time US$99, damn cheap), application that can reverse- and forward engineer databases.
If you already have an existing database you create a connection, reverse engineer and you have the (ERD) schema in DbWrench. You can then make any changes offline and forward engineer your changes directly to the database. No need to write any line of SQL, the app creates that for you. It's also nice to keep multiple (cloned) db's in sync this way or create new DB's from scratch.
It supports PostgreSQL, MySQL, MSSQL and Oracle.

db_explorer_anno.png
Screenshot taken from the DbWrench site
db_explorer_anno.png (164.47 KiB) Viewed 1319 times


I have no affiliation with DbWrench, just being a longtime happy customer.

Hope this helps.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: TIP Adding numeric data types with fixed precision

Postby Bernd.N » Thu May 12, 2016 6:54 pm

For our requirements, the integrated Servoy table editor served us very well, the case above was really the first time where we needed to use the described workaround.

Sure a full-featured database editor has its advantages like the graphic representation of the tables.

However for the bread-and-butter-task of adding some fields, Servoy if absolutely fine, especially as you can work on right away without the need to synchronize the DBI file.

Regarding the indexes, we use an own table to store, manage and set all index information, and copy that table to a customer server in case of an update.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 474
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: TIP Adding numeric data types with fixed precision

Postby Gordon McLean » Fri May 13, 2016 11:04 am

Bernd.N wrote:(Tip for Servoy beginners...)


Don't know about beginners Bernd !!!! .....some of us old timers needed this tip, especially me !!! Had a job that was consistently a few pence out every month and the problem was related to exactly this issue !!!

the complex is so blindingly obvious when you know how :?

Best
Gordon
Gordon McLean
Clickdigital.com
Gordon McLean
 
Posts: 191
Joined: Wed Aug 03, 2005 12:24 pm
Location: UK

Re: TIP Adding numeric data types with fixed precision

Postby Harjo » Fri May 13, 2016 2:32 pm

if you always multiple & devide by 100, also double precisions work fine
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4269
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: TIP Adding numeric data types with fixed precision

Postby mboegem » Sat May 14, 2016 12:28 am

Agree with Harjo here (even when I'm also using DbWrench), this function just always works for me:
Code: Select all
function roundPrecision(value, precision) {
   if (isNaN(parseInt(value, 10))) return value;

   if (!precision) precision = 0; // no precision = default round function

   return Math.round(value * Math.pow(10, precision)) / Math.pow(10, precision);
}
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1382
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands


Return to How To

Who is online

Users browsing this forum: No registered users and 2 guests