Losing precision in query results from JDBC

Comments

4 comments

  • Avatar
    Adrian Williams

    Hi Robert,

    Could you please provide some answers to the questions below:

    • What's the DB you are connecting to?
    • What driver is being used?
    • What's the data type of the field in the database?
    • Can you provide screenshot of the results you see in SQL editor compared with Dataverse?

     

    When you say you are losing decimals, does this relate solely to how the data appears in the Data Viewer? The Dataverse Data Viewer does not show the decimal point and subsequent '0' character for an integer value in a double type field.

    For instance, using the default data in the Create Data node. The 'IOU' field contains a number of double values that have a zero decimal component (e.g. the values in the 7th - 10th records):

    The 10th record has a value of 3100.0 and is displayed in the Data Viewer as 3100. If you cast the IOU values to a string in the Transform node then you can see that it still retains its decimal value within the actual data:

    Regards,

    Adrian

    0
    Comment actions Permalink
  • Avatar
    Robert Eddy

    Hi Adrian, 

    My job was simply querying db then writing to file.  The decimals are gone in the file too. 

    But I applied the code from your example above and confirmed that the value does not have decimals.  That was a really helpful example though, thanks.  Will help me get started with the new Transform node.

    To answer your questions:

    • What's the DB you are connecting to?  Oracle
    • What driver is being used?  ojdbc6.jar / oracle.jdbc.driver.OracleDriver
    • What's the data type of the field in the database? NUMBER
    • Can you provide screenshot of the results you see in SQL editor compared with Dataverse?  

     

     

     

    0
    Comment actions Permalink
  • Avatar
    Tim Segall

    Robert,

    One of the Engineers had a look at this issue and there is potentially more detail here than you want to know.

    The core issue is that the information available from the JDBC driver is limited.  In the information returned from the JDBC driver the metadata tells us that the calculated field is numeric, but that is has a 0 scale.  A 0 scale can mean either that there should be no decimals, and that we should map to a long (which we do), or that the scale is unknown. The JDBC ResultSetMetadata API does not differentiate between these cases, and in the case of calculated fields, the scale is essentially going to be reported as "unknown".

    See the following for more details (if you are interested) - https://stackoverflow.com/questions/11567099/resultsetmetadata-getscale-returns-0-when-using-aggregate-functions-like-min-o

    It appears the best option is to add an explicit cast to the query, with SQL like the following:
    select ......., cast(round(1 + one_mon_ror, 5) as NUMBER(6,5)) as ROR

    Regards, Tim.

    0
    Comment actions Permalink
  • Avatar
    Robert Eddy

    that fixed it, thanks!

    0
    Comment actions Permalink

Please sign in to leave a comment.



Powered by Zendesk