question about number daattype Servoy - Oracle

[Hans Nieuwenhuis]

Hi,

I posted this in the Forms section as :http://www.servoy.com/forum/viewtopic.php?f=2&t=11314
But i realised that the Sql databases group is better, so here it is :

1) Number(38)

I have a number(38) column in Oracle it contains : 12345678901234567890123456789012345678 (38 digits) without problems.
I entered this value by using Sqlplus, not Servoy.
Now i start a Servoy form that displays this column. it shows the correct value
When i change the last digit from 8 to 1 the field changes to 12345678901234567525491324606797053952
But when i do a find /search the old value( 12345678901234567890123456789012345678 ) is shown again !!


When i create a new record and enter the same value in the same field in Servoy the same problem occurs
i enter 12345678901234567890123456789012345678 but when i leave the field or save it changes
to 12345678901234567525491324606797053952 ( ans sometimes to 12,345,678,901,234,568,000,000,000,000,000,000,000)
and when i do a find /search the old value( 12345678901234567890123456789012345678 ) is shown again !!

2) Number(19,2)

I have a number(19,2) in Oracle and i can enter (using Oracle Sqlplus) the value : 12345678901234567.12
Now I start a Servoy form that displays this column. It shows the correct value.
if i change the last digit from 2 to 3 the field changes to 12345678901234568.00
but when i do a find / search the old value (12345678901234567.12) is shown again !!

When i enter 12345678901234567.12 in Servoy it changes to 12345678901234568.00 and stays that way.

3) Integer

I create an integer field in Servoy dataprovider
This becomes a number(10) in Oracle.
I can enter more then 10 digits for instance 1234567890123
when i leave the field or save it seems to be saved.
When i do a find/search ist still shows 1234567890123.

But when i stop/start Servoy solution, the same field now shows : 1912276171


Am i missing something or can someone comment on this.

It happens with Oracle 10.2.0.4 and also with 11gr1 ( i installed only it to test this )

I use the correct jdbc drivers from the specific Oracle version for 10g : ojdbc14.jar and for 11g ojdbc6.jar)

No Errors in logfile

[Hans Nieuwenhuis]

As an add-on observation ( also mentioned in post http://www.servoy.com/forum/viewtopic.php?f=4&t=11307)

What does the length parameter in the dataprovider mean for numbers ??

When I create a number(38) in Oracle and look in the dataprovider it shows number with no length.

When I add a new number column in Servoy and set length to 1 or 4 or 8 or 20 and apply, it changes to 2.
In Oracle this becomes a number(19,2).

When i enter a lengt of 126 it stays 126 in Servoy and in Oracle it becomes a Float(126)

Is this a bug or do i miss something ?
I looked in the documentation, but i can not find any info on this.

Regards,

[rgansevles]

Hans,

Servoy assumes that numerical data to be saved will fit in the database column.
When a field is saved and the database reports no error, the value is kept in memory assuming that is is the same value as the database holds.

If you do a find, servoy only searches for PKs of the records that match and looks for the row data in memory cache, so often the value is reused from memory.
This explaines why only after a restart you see the data read from the database.


On the column creation, when Servoy creates tables or adds columns to a table, only floats for columns of type NUMBER (as shown in the editor) are supported.
When a number is entered in the length field,a number(19,2) (oracle) is created regardless of what length is entered.

We agree this is confusing, in future release (is already the case in Servoy 4) NUMBERs will always map to floats.

We advice to create custom data model script for your database if you have specific requirements to the data model.
Servoy will always discover the actual data model from the database and work with that.

Hope this helps,

Rob

[Hans Nieuwenhuis]

Rob,

thanks for your answer.
But how do you explain

1) Number(38)

I have a number(38) column in Oracle it contains : 12345678901234567890123456789012345678 (38 digits) without problems.
I entered this value by using Sqlplus, not Servoy.
Now i start a Servoy form that displays this column. it shows the correct value
When i change the last digit from 8 to 1 the field changes to 12345678901234567525491324606797053952
But when i do a find /search the old value( 12345678901234567890123456789012345678 ) is shown again !!


When i create a new record and enter the same value in the same field in Servoy the same problem occurs
i enter 12345678901234567890123456789012345678 but when i leave the field or save it changes
to 12345678901234567525491324606797053952 ( ans sometimes to 12,345,678,901,234,568,000,000,000,000,000,000,000)
and when i do a find /search the old value( 12345678901234567890123456789012345678 ) is shown again !!

2) Number(19,2)

I have a number(19,2) in Oracle and i can enter (using Oracle Sqlplus) the value : 12345678901234567.12
Now I start a Servoy form that displays this column. It shows the correct value.
if i change the last digit from 2 to 3 the field changes to 12345678901234568.00
but when i do a find / search the old value (12345678901234567.12) is shown again !!

When i enter 12345678901234567.12 in Servoy it changes to 12345678901234568.00 and stays that way.

The colums are created in Oracle and not in Servoy.
If i use only Oracle (sqlplus) the mentioned values fit. And i can also make the mentioned changes with no problems.
It looks like Servoy does not see the digits from a certain point on.
If i change 12345678901234567890123456789012345678 to 123456789012345678901234567890111111111
the save seems to be done, no error but no change is made if i restart the solution the old value is back 12345678901234567890123456789012345678

?????

And would it not be possible for Servoy to check if there are to many digits entered for a number filed ( with text fields, you can not enter more then the <length> number of characters !)

Regards,

[rgansevles]

Hans,

This sounds like a bug, please create a case in our support system (and include a nice small sample and the create table statement for Oracle).

Rob

[Hans Nieuwenhuis]

Hi Rob,

Created a case : 165980

Regards,

[rgansevles]

Hans,

The numbers were handled as doubles internally in Servoy.
These doubles cannot store such large values without loosing precision, which caused the behavior you described.

We made changes to Servoy to support larger numbers in next release.
There are a few limitations however.

- java version
the client (or the server in case of webclient) needs to run java 1.5 or higher (we are using a feature introduced in java 1.5)

- the numbers are not used in scripting
javascript converts numbers to double, the numbers you are using do not fit in a double, precision is lost

- database column type in oracle
the example showed a float column (from the create-table statement), these are handled in Servoy by doubles which can not hold the values without loosing precision.
we created another table with type number(38,2), this column worked as expected with the large values (after the changes made to Servoy)

Rob

[Hans Nieuwenhuis]

Hi Rob,

Thanks, i love the way Servoy deals with these questions !!

B.t.w. does anyone know if there are any (future) developments in javascript to handle larger numbers ??

Or possible workarounds ?

Regards,

[pbakker]

The ECMA script foundation is simultanious working on a maintenance release of the JavaScript specs versioned 3.1 and the future new release, which has project name Harmony.

A couple of months ago, the new major version was still called ECMAScript 4 (ECMAScript being the official name for Javascript) and in those specs they had support for the decimal type described.

But the ECMAScript 4 specs was scrapped completely and they started from scratch, splitting it up in maintenance release 3.1 and codename Harmony for the next major version.

The draft specs for 3.1 (http://wiki.ecmascript.org/lib/exe/fetc ... 0oct08.pdf) does contain stuff about the decimal type.

Now, those specs are still in draft phase, so it needs to be seen if this will make it into the final specs, when the specs become final and when the different JavaScript engines (In Servoy's case Rhino) will implement the new specs.

So, in short: a lot of "if's", but the issue of supporting large numbers is recognized as something to look at.

Paul

[Hans Nieuwenhuis]

Paul,

Is there any news on this issue ?

Regards,