Type "number" not supported in SmartGWT FieldType; and floats rounding/truncating

[gsaxena]

Hi,

We have a MySQL 5.5.13 in which a table (which happens to use the InnoDB engine) has fields which are of type:

decimal(19,10).

That is, a precision of 19; a scale of 10.

When we use Visual Builder to create a datasource definition form this file, it translates this field to a type of "number", as in:

<field name="c_terminalResidueModificationMass" type="number"/>

However, in our SmartGWT client side code, we have discovered that "number" is not a supported field type. That is, the code below will return NULL for the formItemType.

FormItem currentFormItem2 = event.getItem();
String formItemType = currentFormItem2.getType();

Furthermore, the javadocs for fieldstype (http://www.smartclient.com/smartgwt/javadoc/com/smartgwt/client/types/FieldType.html) do NOT have a type of "number".

1) So, our first question is: is this a bug?
2) Secondly, what type should be used for decimal values?

So, what we did was the we simply changed the datasource definition to float; this worked, except for the following problem:

In our dynamic form, we can set a "float" (ie what was supposed to be a decimal field) field to some nice value, such as 1.23456789

However, when we try to retrieve the value in that dynamicform formitem though the code listed below, it TRUNCATES/ROUNDS the value that the user keyed in (without any warning).

FormItem currentFormItem2 = event.getItem();
....
currentFormItemValue = currentFormItem2.getValue();
...
System.out.println("The decimal is (from set)" + currentFormItemValue.toString() );


3) So, our third question is: is there a way to prevent float from truncating/rounding values that a user keys in?

Until we hear otherwise, the only hack we're thinking about implementing is to make the datasource definition as "any", and then internally have our code assume that "any" really means "decimal", and to then convert the "any" field to a string and continue with rest of the code. (But, we would then lose the *automatic/prebuilt* validation for a numeric field).

It seems like SmartGWT probably either does support a decimal field and/or has a way of prevent rounding/truncation of floats.

Any thoughts would be appreciated. Further operational details are below:

========
OS: Windows XP Pro
IDE: MyEclipse 9.0 with Google Plugin for Eclipse (2.3.1)
SmartGWT EE 2.4
Browwer: Mozilla Firefox 4.0.1
GWT SDK: 2.2
Sun JDK 1.6.0_13

[Isomorphic]

1. We'll check on this, we might have a bug or perhaps MySQL is misrepresenting the type in JDBC. Please let us know the source and version of the JDBC driver you're using.

Note that in general you shouldn't be using the Visual Builder wizards to generate DataSources, you should be using dynamic generation as described in the QuickStart Guide, Server Framework chapter.

2. "float"

3. what actual value did you type in? The sample you've indicated won't truncate, but longer decimal values may if they exceed the range or precision that the browser can represent.

[gsaxena]

Regarding #1:

The MySQL jdbc jar this is used is called: mysql-connector-java-5.1.16-bin.jar
A ever so slightly newer version (v 5.1.17) can be downloaded from here (as of today): http://dev.mysql.com/downloads/connector/j/

(I can ftp the actually used jar file if needed.)

Regarding #3:

I did another test where I keyed into a DynamicForm formItem's field (which has been set to "float") the following number:

50.9876543211

The following code is then run inside as a result of the formitem's "OnBlur" event:

=================

...
Object currentFormItemValue ;
FormItem currentFormItem2 = event.getItem();
String formItemType = currentFormItem2.getType();
currentFormItemValue = currentFormItem2.getValue();
...
System.out.println("The decimal is (from set)" + currentFormItemValue.toString() );

=================

The console then shows ---->

The decimal is (from set)50.987656

===================

Finally, although I believe this is not relevant to this discussion and is probably just a red herring, but we actually have a POJO class on the CLIENT side that is used to populate the dynamic form; that is, the dynamic form does NOT directly get data from the MySQL table. (However, one key part of this POJO class -- the part for which we need a dynamic form -- is persisted in the MySQL tables, so we just used the Visual Builder as a quick/efficient way to describe the fields, and then we edit the datasource generated to remove references to MySQL tables etc.) Again, I do not believe this affects the issues described above.

[gsaxena]

Additionally, I noticed something that may also be related to the SmartGWT-Visual Buider and/or MySQL JDBC driver issue described above:

When using Visual Builder to build a datasource from a MySQL table, it does not seem to:

1) read the "nullable" property in the MySQL table to construct the "required" field
2) read the "enum" definitions in the MySQL table
3) presumably, it would not read the "set" definitions in the MySQL table either (though I didn't test that).

So, is Visual Builder supposed to support the above 3 features?

Alternatively, I could have VB point to an EJB; however, I don't think VB will automatically read the JPA annotations (e.g., whether a column is nullable; it's precision and scale; its lenght etc.). (Also, based purely on memory which may be INCORRECT, I think VB worked fine for simple EJBs, but if the EJB used inheritance, then it would not read the EJB...but my memory could be wrong.)

[Isomorphic]

1) these do not have the same meaning. Required means non-empty input, null string would be valid for a nullable column, but not valid for a required field.

2 & 3) correct, we don't yet support these - we don't generally try too hard to capture DB-specific features like this, since it's easy to add this information to your .ds.xml file, without repeating anything else

We do read JPA annotations, including turning Enum fields into a valueMap. Not sure about precision and scale.

Generally speaking, you will end up with some UI-specific properties in your .ds.xml file because there are no SQL or JPA concepts for many of the things that can be set in a .ds.xml file to influence UI components (eg, editorType).

If you have a huge number of entities, it can make sense to extend the Batch DataSource Generator to extend the generation process with custom annotations or heuristics that allow the entire .ds.xml definition to be generated.

However for a smaller number of entities, the pattern of deriving most of the structure from the model and putting UI-specific properties in your .ds.xml is the best approach.

[Isomorphic]

About a MySQL decimal ending up as a "number" field - we've fixed this, it will be treated as a "float" field in future nightlies.

On the truncation - from what we can tell, you're running into some kind of core GWT (not SmartGWT) bug with how Object.toString() works. If you were to saveData() on the DynamicForm, you should see the originally typed-in value faithfully represented in the request sent to the server, visible in the RPC tab of the Developer Console. Please confirm.

[gsaxena]

Great!

Regarding the Object.toString(), we'll test this out; however, what are your thoughts that SmartGWT may need a proper "decimal" field, as float is (at least in Java) imprecise (by design). In our specific scenario, we're designing a system for a scientific application where it is common to have fields that are precise to 13 or more decimal places, and various downstream systems would give completely erroneous results that would be very hard to trace if we "approximated/truncated/changed" what the user keyed into those fields.

That said, we've been easily able to resolve the problem (and remain very impressed by SmartGWT) by simply changing the ds field defintion to "any" and then doing a conversion to BigDecimal in the DynamicForm code, though it did require a reasonable amount of extra code and testing, and for those SmartGWT developers that are trying to use SmartGWT in an "automated" fashion (ie talk directly to the rdbms without any DMIs etc), they would not be able to rely on that (really nice) SmartGWT feature since "decimal fields" in the db get translated to Java/Javascript float fields of unknown/approximate-only precision in SmartGWT.

[Isomorphic]

The precision limit of our "float" field is actually JavaScript's Number type: more than Java's float, less than Java's double (simplifying here - there are nuances). This covers most scientific applications, with possible decimal point shifting required in edge cases.

To go beyond this precision, you can build a reusable SimpleType where the number value is internally stored as a String or BigDecimal.

Having an out-of-the box implementation of this approach would be a good feature addition - consider Feature Sponsorship if it's important for you.