Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Save problem through DataStore command .Update()

  1. Advice
0
Votes
Undo
  1. Irina Tokar
    Irina Tokar
  2. PowerBuilder
  3. Tuesday, 11 January 2022 16:53 PM UTC

Hi guys,

We have a very strange problem. Our big application written on PowerBuilder, C#, VB and works with Oracle and Mss databases.

The save process to database going by command dscontrol.Update() through array of datastores control. The problem is : for the column type "number" in Mss DB some values saved as user entered them, and some in the different format. For example in application i want to save those values:  

but the values stored in the database:

There are some numbers : “-0.97” is stored as "-0.97000003"
                                       “8.98” is stored as "8.9799995".

but the others are ok       : “0.96” is stored as "0.96", “0.95” is stored as "0.95", “0.98” is stored as "0.98", “8.99” is stored as "8.99"

This happening through all our application for all the columns type "number". When i try debug this problem in PowerBuilder environment ( or simple run the application from PowerBuilder) i cannot reproduce this problem ( all those numbers saved ok ), but when i working with the exe - it's happening. 

Why is this happened? What the problem? Maybe when i working in the PowerBuilder environment my computer has all the files and dll's from PowerBuilder installation and when i am working with exe on the other PC - something missing? 

What you can suggest to do, to check?

Best regards, 

Irina Tokar

                                       

  1. DataStore
PB license issue (appeon website issue?)
Manage licenses
Responses (5)
Benjamin Gaesslein
Benjamin Gaesslein Accepted Answer Pending Moderation
  1. Thursday, 13 January 2022 13:25 PM UTC
  2. PowerBuilder
  3. # 1

Hi Irina.

There are some numbers : “-0.97” is stored as "-0.97000003"
                                       “8.98” is stored as "8.9799995".

but the others are ok       : “0.96” is stored as "0.96", “0.95” is stored as "0.95", “0.98” is stored as "0.98", “8.99” is stored as "8.99"

I think that's very unlikely. You say this is a MSS Database and from the results it suggests that the datatype is either real or float with a precision of 24bit. (a single precision floating point number) Thus, -0.97 is actually stored as -0.9700000286102294921875 but there's some rounding to the 8th digit going on here. I'm guessing PB is rounding the numbers to 8 fractional digits. This is why 0.96 shows as 0.96, the value in the db is likely to be 0.959999978542327880859375, which will result in 0.96 if rounded at the 8th digit.

Unless it's a fraction with a denominator that is a power of two ( 0.5, 0.25, 0.125 etc ), a floating point datatype will literally never store the exact fraction because the binary representation would be infinitely long. It's simply not possible.

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 11 January 2022 21:31 PM UTC
  2. PowerBuilder
  3. # 2

Here is a link to a discussion on how floating-point numbers are represented in binary form:

https://www.geeksforgeeks.org/floating-point-representation-basics/?ref=rp

The actual mechanics and details, although interesting, are not important at this point. What is important is one sentence roughly one-half way through the discussion in a paragraph having the heading "Accuracy":

"Not all real numbers can exactly be represented in floating point format."

Some numbers that have two decimal places can be exactly represented in floating point and some cannot. This has nothing to do with PowerBuilder, it's just the way it is and it's due to the conversion process described in the discussion in the article.

Decimal number are stored/represented differently. Each 0-9 digit is kept intact (in binary, of course), as is the location of the decimal point.

While I'm not privy to how the DataWindow works internally, its "Number" datatype works, I suspect, as a numeric kind of  "Any" datatype. If a DataWindow column with a datatype of Number contains 0.97, PB will do its best to keep track of that value as 0.97. When the database eventually receives that value in an Insert or Update statement and has to store the value as a floating-point value, it's going to convert it to a binary floating-point format that is the closest approximation to 0.97 that can be made... its just not going to be exact.

Again, this is not due to a bug in PB... this is how floating-point numbers work.

If your business requirement is to store numeric values with two decimal places in EXACT form, then you need to use a decimal datatype in the database (and in PowerBuilder).

 

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 12 January 2022 03:22 AM UTC
That's a good comment! If you use a format with the amount of needed decimals, the data should show correctly (It'll be rounded on the diplay, not in the database). For example to show 6 decimals, apply this format:

#,##0.000000 (or even #,##0.###### if you only want to show decimals which are non 0)

regards
  1. Helpful
Irina Tokar
  1. Irina Tokar
  2. Wednesday, 12 January 2022 10:37 AM UTC
Of course i can use the any format ( as you see on the first grid ). But more interesting what we see in the table ( the second grid ). And one more question: why i cannot reproduce this behavior in the PowerBuilder environment ? and the same code behaved different when i run application exe on other PC without PowerBuilder setup? Can it be related to how we are build application ( platform 32-bit or 64-bit )?
  1. Helpful
John Fauss
  1. John Fauss
  2. Wednesday, 12 January 2022 14:26 PM UTC
It's possible, I suppose. Are you seeing a correlation that this issue occurs only when running the app as a deployed 64-bit exe/pbd's? One more thing you might wish to investigate/test: In a copy of one of the DataWindow data objects that exhibits this behavior, edit the data object source and change the column's datatype from number to real, then test... in doing this will make the DW column's datatype match the table column's datatype. Not sure it will help, just an idea.
  1. Helpful 1
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 11 January 2022 20:21 PM UTC
  2. PowerBuilder
  3. # 3

I've had this happen. Fortunately, I was dealing with currency and was able to specify a fixed DECIMAL width for the values. Using these fixed decimal widths resolved the issue for me.

decimal {2}      ld_amount_due
decimal {6}      ld_rates

Comment
There are no comments made yet.
Irina Tokar
Irina Tokar Accepted Answer Pending Moderation
  1. Tuesday, 11 January 2022 20:20 PM UTC
  2. PowerBuilder
  3. # 4

Hi John,

In the database (Oracle and MSSQL) there are float type of the columns.

PowerBuilder present those columns as number ( see the image below - the columns inst_range_min, dcs_range_min ) in the datastore :

 

I am confused that there are only some values saved on database different. As a wrote 0.95 saved as 0.95, 0.98 saved as 0.98 and many other are ok,

but 0.97 saved as  0.97000003 , 8.98 saved as 8.9799995 , 71.2 saved as 71.199997.  

Comment
mike S
  1. mike S
  2. Tuesday, 11 January 2022 20:37 PM UTC
that is the way float datatype works.



If you need fixed decimal places, then you need to change the database types to decimal, and then update the datawindows in your application appropriately.

  1. Helpful 1
Irina Tokar
  1. Irina Tokar
  2. Tuesday, 11 January 2022 20:48 PM UTC
i don't need the fixed decimal place ( it can be 1,2,3,... 8 decimal points ). In my example there are numbers i got from our customers. And when i am working from PowerBulder environment - all those numbers saved as user entered it, but in the exe we have this behavior.
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Thursday, 20 January 2022 00:05 AM UTC
When dealing with floating numbers, you MUSY have a spefified level of precision. Get with your clients and determine their desired level of precision; i.e. the number of decimal places which are relevant to them.



For multiple customers you can keep this info in a table.
  1. Helpful 1
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 11 January 2022 17:17 PM UTC
  2. PowerBuilder
  3. # 5

Hello, Irina - 

I believe a more critical question is: What is/are the datatype(s) of the columns in the databases?

A floating-point datatype is not always going to be able to represent a number with a fixed precision...that is what the decimal datatype (in SQL Server, not sure about Oracle datatypes) is designed to handle. The conversion between binary (base 2) floating-point (how floating-point values are actually stored) and base 10 floating-point (how we view and work with these values) is not exact.

If you require a numeric value with two decimal places, then the PB datatype you should use is decimal{2} and in the database, whatever is equivalent to that.

Regards, John

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.