Numeric Data Types, Calc Fields Problem

Classic Servoy
1

Bug and/or Enhancement Request:

Dear friends at Servoy - for your consideration … for other readers, the following is to assist anyone else with similar calculated field problems.

I am not sure if this fits into the ER hopper more than anything else, but here’s the issue:

As previously discussed on this forum at some length and going back to 2004 (see recent posts at

http://forum.servoy.com/viewtopic.php?t … ght=double), there are still concerns with how Numeric data types are managed by Servoy. The 4.0 Eclipse cutover will likely deal with this exhaustively once and for all as well as some forthcoming changes in the 3.5 data providers (data provider Default, Convert).

Recently, we posted a problem with on thread http://forum.servoy.com/viewtopic.php?t=8442 (Circular Reference or Fatal Loop in Calculation?).

At the end of the day, this was an old and known issue (data types and casting for numeric data type).

Since topic 8442, we isolated the problem down to the following precise and replicable symptom:

When stepping through a Postgres table (single form running - isolated from other relation dependencies (e.g. - that could arise from a form on a Related Tab)), we would hit a record (in our case, record # 23) and the dev machine cpu would red-line (50% or higher on javaw.exe) and stall.

All of the records looked “the same” as records 1 - 22. We ended up looking at these for several hours (including a PG dump/reload of our records).

We isolated down to the following problem (actually our problem on the Servoy form calculated fields):

  • A simple calculation of shares * price/share caused javaw.exe to pin/red-line
  • If the calculation was commented out, all was fine
  • The backend data types were shares (numeric 14,6) and price/share (numeric 14,6) destined for a stored field (calc_amount_due - type is numeric 12,2)

When we hit a record where the calculated product (shares * price/share) was in excess of two decimal places, javaw.exe would
really act up and get cpu-thirsty. As long as the mathematical product did not go over 2 decimal places, all was fine. As Murphy’s Law goes, of course this never appeared in the initial test records and only acted up in QA (better than production I suppose :-) ) … it would have appeared immediately if some of the earlier records had greater stored precision from the data port.

So, it came down to the usual issues of trying to stuff a round peg in a square hole (3 decimals or greater into a precision 2 numeric target).

We used the quick and easy fix of Math.Round((shares * Price_Share) * 100 / 100) and away it went. We dropped this calculation into a var to be sure:

var ret_val = 0.00;
ret_val = Math.round((number_shares * price_share * 100)/100)
return ret_val;

We know now what to watch for (including some earlier development digressions where we failed to pre-initialize “text” objects on our form that were intended to house numeric data and so new record adds would fail - strings headed for a numeric db backend!).

Anyway, my ER to Servoy is the following:

  1. Increase the ability to manipulate numerics. Multiplying by 100 and dividing by 100 feels cludgy. Math.round only rounds to an integer. Other methods like moving the calc out to string (ToFixed) and then back to numeric again also feels cludgy. Maybe there is a better way in 3.1.x? A round(numvar, precision) would be helpful as well as some additional type casting functions.

  2. Data provider defaults can be a “big deal” - especially for newcomers to the product. When you pull schema and data type information from the backend db, can you suggest a “Default” value for data provider columns if the developer has not already “tweaked” this? Newcomers to Servoy may not always grasp the significance of this right away. As a newcomer, the sense I had from reading the Product features was that one didn’t have to be concerned too much about this if connecting to an existing database. Well, actually, one does need to watch this area carefully. Yes, Servoy is seamless and plug-n-play. Up to a point and with
    certain data type conditions and scenarios. After that, there are areas one needs to watch. I had to learn some rules that I did
    not realize existed with the product. This can be a concern or disconcerting for newcomers: my initial assumptions were proving
    invalid. The ability for developers to ramp up quickly and smoothly is a big revenue-positive-plus for Servoy.

  3. Could you propose type casting when not explicitly directed otherwise by a developer? I realize this is somewhat backend db specific, but I am thinking of reasonable assumptions as to target data type. For example, when using Groovy within iReport (Jasper) instead of Java, one does need to be quite so fussy with data types. This becomes helpful for learning curves so one can stay “up a notch” (less low level watchfulness of data types needed) and still get the job done. In our example above, a “proposed type casting” approach would automatically truncate the return value down to 2 decimal places of precision … unless of course the
    developer (or a development level flag like (“I will look after Type Casting, thank you”)) looks after it otherwise. By the way -
    I note that when Servoy creates the db column, the rules of the game change and it seems to be more well-behaved. Just an
    observation. So, I am asking for a “middle-ground” (like what Groovy would use - reasonable data type assumptions) approach that can be fine-tuned if needed.

Thanks for your consideration of the above.

Really enjoying the product - a real pleasure to use! Tons of good and very sensible and well-thought features and developer-friendly things going on.

Michael