We are having the following problem : a numeric value of a table column shown as, for instance, 54,20 in a Servoy form and in debugger is stored in Sybase database as 54,19 .
Probably real value is 54,19999999 or something similar . Seems when it is written to database it is truncated instead of rounded.
It happens with column type numeric and, more seldom, with column type double. Numeric columns are defined as 19,2 (2 digits after decimal point)
We are trying to avoid it mulyiplying and dividing by 100 but it still remains. So also we optimize precission of intermediate calculations the problem is that what you see is not what is stored in database.
Any clue on this ? Any Sybase database/table setting to indicate rounding instead of truncation ? Any Servoy side functionality we are missing ?
More info about this post : we are working with Servoy 5.2.2 and Sybase Ianywhere 11 in Windows .
We tested with Postgres and works OK but customer is in production so we can not switch inmediately.
I have attached a sample solution that shows the problem (creates a table in user_data), try it as follows :
enter 29720 form quantity
enter 185 for price
press “Calculate” button (calculates and shows the rest of the values)
The problem is : neto is shown as 5498.20 but in Sybase it is stores as 5498,19 - if you quit exit the solution and launch it again you will see this las value.
It also happens with quantity=27777 and price=177
Thanks in advance to those that like to work on weekends
I have read that thread several times in the past 6 weeks and we have taken advantage of your post of April 23th : we coded a routine based on your idea in order to improve precission, and we have gained in this subject.
But the problem we are facing now (which initially we thought it was a floating point precission problem) is that what we watch in form´s variables and in debugger is not what is stored in Sybase numeric columns .
It seems that what we watch in forms and debugger is rounded and when it is stored in Sybase it is truncated; The sample solution I have attached shows it clearly.
We have made a small test with Postgress and it works OK : what you see is what is stored.
We have made a desperate move : once calculations are finished and before savedata we have added 0.004999999 to numeric columns and it solves the problem, because converts truncation in rounding, but of course this should be acomplished automatically.
BTW, we are using com.sybase.jdbc3.jdbc.SybDriver with Sybase 11 and Servoy 5.2.2 : is it OK ? I am asking this because we installed Servoy 5.0 and upgraded it several times, but we did not change database URL.
I’m not using any other driver, and as far as I know it’s the right one.
Did you try to find out what is really written to the DB by Servoy?
If you enable tracing in the logfile admin page of the server and you alter the value which triggers this problem and then save the data + disable the tracing, you should get the exact steps of what Servoy does with your data.
With these results it’s easier to see if it’s Servoy or Sybase that’s messing up.
Based on your experience with Postgres I’d say it’s the last one, but better find out…
I have looked at Servoy log and it seems it reports values sent to DB rounded, but those values are truncated when stored in DB.
I do not know if Servoy sneds SQL to DB or comunicates thru an API; in first case may be we can see SQL statements - another way to know what is going to DB is look nto DB log file but it is not understable accessed with Notepad, may be there is an utility to do it.
But we are pretty sure the problem is values are truncated when stored in DB.
We are working in the following workaround : coding table events to add (or subtract if the value is negative) 0.004999999 to column values defined as numeric with 2 decimals (mostly money amounts); it seems to work : we convert truncation in rounding ; of course it is a workaround and we should not need to take care of it.
Help ! This thread is still open ; Sybase is truncating numeric values
We are having the following problem : a numeric value of a table column shown as, for instance, 54,20 in a Servoy form and in debugger is stored in Sybase database as 54,19 .
Probably real value is 54,19999999 or something similar . Seems when it is written to database it is truncated instead of rounded.
It happens with column type numeric and, more seldom, with column type double. Numeric columns are defined as 19,2 (2 digits after decimal point)
We are trying to avoid it mulyiplying and dividing by 100 but it still remains. So also we optimize precission of intermediate calculations the problem is that what you see is not what is stored in database.
We are using Servoy 5.2.2 and Sybase Ianywhere 11.
Attached is a sample solution that shows what we describe here.
Any clue on this ? Any Sybase database/table setting to indicate rounding instead of truncation ? Any Servoy side functionality we are missing ?
What we do is : Every JavaScript (Servoy) computation (+ - / *) which has to yield two-digit decimal precision result we’ll throw into our own round function. That two decimal digit result we store in the table.
We have many hundreds of calls of that round function in our code. A big disadvantage in comparison to foxpro where a field has a stated number of decimals and a value will be saved rounded…
In JavaScript a subtraction as 14 - 1.1 could yield 12.8999999999999
You can use the debugger to see what a number variable is doing in terms of precision…
The sybase jdbc driver rounds a double value in an unexpected way.
We have filed a case with Sybase for this issue (sybase case id 11637481), and we made a workaround for this in Servoy 5.2.3