TIP Adding numeric data types with fixed precision

Classic Servoy
1

(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.

2

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.

[attachment=0]db_explorer_anno.png[/attachment]

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

Hope this helps.

db_explorer_anno.png
db_explorer_anno.png660×468 146 KB

3

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.

4

Bernd.N:
(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

5

if you always multiple & devide by 100, also double precisions work fine

6

Agree with Harjo here (even when I’m also using DbWrench), this function just always works for me:

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);
}