Is there any support for Oracle Number type

[wwarriner]

SmartClient Version: v9.1p_2014-03-18/Pro Deployment (built 2014-03-18)

I have a oracle data source with columns of type NUMBER. In the ds.xml file I define these fields as floats. On the server side I query and the type of the returned associated column value is BigDecimal.

Is it up to application convert the BigDecimal to float or does smartgwt provide some kind of support for this ?

[Blama]

Hi wwarriner,

please read this thread and these linked 5.1d(!) docs.

My guess after reading this is that for values in the Java Integer and Long range you should be getting Java Integers and Longs (@Isomorphic: or always Longs?).

@Isomorphic:
I have to cast from BigInteger also all the time and it would be great if this could be transparently done by SmartGWT (currently I'm using 5.0p). This docs read as small values should never be BigInteger in DMI. Is this true? Also for results from server initiated fetches? If so (and the docs suggest this), I'm pretty sure that the framework is behaving different than it should. I can give you a testcase for Oracle type NOT NULL NUMBER(38) and DSField type="integer" then (using ojdbc6.jar).

Best regards
Blama

[Isomorphic]

When you use these database column types, the JDBC driver returns BigDecimal and BigInteger types because it's possible to store numbers that are bigger than Java's Double or Long types can represent.

The docs (same that Blama linked to) describe what we do when delivering to the browser, which really can't represent such values at all.

To avoid receiving these types in Java, the best fix is to modify your database schema so you are using types that only represent the range you are actually using. This can reduce your storage needs and speed up your database queries too.

[Blama]

Hi Isomorphic

To avoid receiving these types in Java, the best fix is to modify your database schema so you are using types that only represent the range you are actually using. This can reduce your storage needs and speed up your database queries too.

this is very valuable information that should definitely be included somewhere in the docs (perhaps at FieldType.INTEGER, FieldType.FLOAT and in the stringInBrowser-docs).

Is the following correct? Up to...:

• Oracle NUMBER(4,0) -> Java Short
• Oracle NUMBER(9,0) -> Java Integer
• Oracle NUMBER(18,0) -> Java Long
• Oracle NUMBER(38,0) -> Java BigDecimal

Who does this conversion? Is it SmartGWT or the ODBC driver?

Best regards
Blama

[Isomorphic]

The JDBC driver, and we're not sure of that table - Oracle tends to change their JDBC driver's behavior from time to time unfortunately.

[Blama]

These docs might be interesting in this context:

• Oracle 11.2 Database JDBC Developer's Guide: Data Type Mappings
• Information (without reason) that SQL INTEGER not always will result in NUMBER(10,0), but can result in NUMBER(38,0) (when used in a DDL statement). This happens for me.

I'll try in a test what different SQL / Oracle NUMBER types will give me in Java/DMI and decide then how to proceed. The BigDecimal-casting is annoying and it will take long until Java Integer or Long won't fit my primaryKey-needs anymore :)

Best regards
Blama

[Blama]

Hi Isomorphic,

The JDBC driver, and we're not sure of that table - Oracle tends to change their JDBC driver's behavior from time to time unfortunately.

do you know the location of the docs describing the current behaviour? The ones I found don't go that much into detail (in the first link I gave the conversion of SQL Data Types to JDBC Type Codes is missing the precision/scale information).

Best regards
Blama

[Blama]

Information (without reason) that SQL INTEGER not always will result in NUMBER(10,0), but can result in NUMBER(38,0) (when used in a DDL statement). This happens for me.

In 10.2, INTGER in DDL always translated to 38, see here. 11.2 uses NUMBER(p,0), but without defining p.

But this is not related to the JDBC question anymore. I'll try with explicit NUMBER(p,0) in my DDL and see what result I get in Java. If you know of the current reference (see #7), please let me know.

Best regards
Blama

[Blama]

Hi Isomorphic,

These docs might be interesting in this context:

• Oracle 11.2 Database JDBC Developer's Guide: Data Type Mappings

please see the text below the table:

NUMBER Types

For the different type codes that an Oracle NUMBER value can correspond to, call the getter routine that is appropriate for the size of the data for mapping to work properly. For example, call getByte to get a Java tinyint value for an item x, where -128 < x < 128.

Are you sure that the JDCB driver will automatically return smaller Objects (Integer, Long) for smaller columns? Or might this be up to your classes to call the appropriate API? You would need more information in the .ds.xml then, wouldn't you?

If you know that it works it is fine and I'll try to get rid of my BigDecimals, but otherwise I'll wait for your suggestion of a best practice.

Best regards
Blama

[Isomorphic]

We are just calling the standard JDBC API ResultSet.getObject().

Then we apply a huge number of workarounds for various known database bugs. But none of those workarounds involve up-conversion from Long to BigInteger, Double to BigDecimal or anything of the kind - it's the JDBC driver's choice to return those types.