Menu

Decimal delimiter

Help
2008-10-06
2012-09-15
  • Nicholas Ustinov

    I use ',' as decimal delimiter on both server & client nodes. But during initial load of table with numeric column on client node the next error appeared:

    java.lang.NumberFormatException: For input string: "30,6"
    at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)
    at java.lang.Double.valueOf(Unknown Source)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9106)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8843)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9316)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:162)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:336)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:143)
    at org.springframework.jdbc.core.ArgTypePreparedStatementSetter.setValues(ArgTypePreparedStatementSetter.java:77)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:796)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:854)
    at org.jumpmind.symmetric.load.TableTemplate.execute(TableTemplate.java:329)
    at org.jumpmind.symmetric.load.TableTemplate.insert(TableTemplate.java:151)
    at org.jumpmind.symmetric.load.csv.CsvLoader.insert(CsvLoader.java:233)
    at org.jumpmind.symmetric.load.csv.CsvLoader.load(CsvLoader.java:130)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy14.load(Unknown Source)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$1.doInTransactionWithoutResult(DataLoaderService.java:296)
    at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
    at org.jumpmind.symmetric.service.impl.DataLoaderService.loadBatch(DataLoaderService.java:291)
    at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataAndReturnBatches(DataLoaderService.java:198)
    at org.jumpmind.symmetric.service.impl.DataLoaderService.loadData(DataLoaderService.java:105)
    at org.jumpmind.symmetric.service.impl.PullService.pullData(PullService.java:67)
    at org.jumpmind.symmetric.job.PullJob.doJob(PullJob.java:35)
    at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:78)
    at java.util.TimerThread.mainLoop(Unknown Source)
    at java.util.TimerThread.run(Unknown Source)

    I think it's cause of using java.lang.Double.valueOf method for converting string to double - as i see in docs ( http://java.sun.com/j2se/1.4.2/docs/api/java/lang/Double.html#valueOf(java.lang.String) ) this method doesn't use locale settings for parsing string data.

    Any ideas? )

    Nicholas

     
    • Eric Long

      Eric Long - 2008-10-06

      I haven't been able to duplicate this yet. I have Oracle 10g EE and JDBC driver 10.2.0.2.0.

      I'm confused that OraclePreparedStatement thinks it got passed a String that needs converted to Double. I would expect it to be passed a BigDecimal instead. The TableTemplate puts JDBC types of NUMERIC and DECIMAL into a BigDecimal before sending it to JDBC. I'm testing with Oracle NUMBER(10,2), which gets handled properly this way.

      I'll try to figure out what else I can do to cause it here. Let me know if you have any more information to help debug.

      Eric

       
      • Nicholas Ustinov

        Well, we solved the problem :)

        Problem was consist of 2 parts:
        the first - column type in our oracle database was "float". Yes, oracle supports float cause of ANSI standards. So column.getTypeCode() method returned "FLOAT" (not NUMERIC and not DECIMAL) and your code treats column as string.
        the second - decimal separator. "objectValue = new BigDecimal(value)" raised error cause value should be number in java language format in BigDecimal constructor.

        In execute method of TableTemplate we changed this code:
        else if (type == Types.NUMERIC || type == Types.DECIMAL || type == Types.FLOAT) {
        try{
        objectValue = new BigDecimal(NumberFormat.getInstance().parse(value).toString());
        }
        catch (ParseException e){throw new RuntimeException(e);}
        }

         
        • Eric Long

          Eric Long - 2008-10-07

          Nice work!

          One concern I have with using NumberFormat is that it is uses native double, so you can lose precision, especially with currency. How about this instead:

          NumberFormat format = DecimalFormat.getInstance();
          ((DecimalFormat)format).setParseBigDecimal(true);
          BigDecimal b = new BigDecimal(format.parse(value).toString());

          Let me know if you think that's okay, and I'll get this checked in.

          Thanks,
          Eric

           
          • Nicholas Ustinov

            Yeah, all ok, except one little thing...
            on the source side value of "float" field was, for ex., "-.07476635514018691588785046728971962617"
            but on incoming side it becomes "-.07476635514018691"
            In fact, oracle stores in FLOAT field more than 16bit mantissa, and all is working fine in symDS including new code with parsing and storing digital in bigdecimal type. But somewhere later (in jdbc driver?) digital is converted to jdbc type FLOAT (it's really 16bit mantissa format)

            when i'm changing field type in table to "number" (on my test base) all becomes ok - without any precision reducing.

            Maybe we need to change column description for jdbc driver manually? (by setting column type in jdbc metadata to the most precise number type for any numeric types?)

            Nicholas

             
            • Eric Long

              Eric Long - 2008-10-09

              I see what you mean. It's because the type is being specified as FLOAT even though a BigDecimal is being passed. We can override the type to be DECIMAL in the buildTypes() method of org.jumpmind.symmetric.load.StatementBuilder:

              else if (type == Types.FLOAT || type == Types.DOUBLE) {
              type = Types.DECIMAL;
              }

              That seems to work on Oracle. Some database platforms won't accept that (I think MySQL, for example), so in the final code it will need to check for an override variable, set only for Oracle, before changing the type.

              Here are the bugs you helped find and fix:

              Decimal separator locale (1.4.0):
              http://sourceforge.net/tracker/index.php?func=detail&aid=2154061&group_id=206470&atid=997724

              Loss of precison with Float type on Oracle (1.4.1):
              http://sourceforge.net/tracker/index.php?func=detail&aid=2155736&group_id=206470&atid=997724

              I appreciate your help!

              Thanks,
              Eric

               
              • Eric Long

                Eric Long - 2008-10-20

                I've got the fix for the loss of precision (bug 2155736) in the snapshot for 1.4.1:

                http://symmetricds.org//repo/org/jumpmind/symmetric/symmetric/1.4.1-SNAPSHOT/symmetric-ds.jar

                In your lib directory, remove the old symmetric-ds-1.4.0.jar and replace it with this one. We will probably officially release 1.4.1 this week.

                Thanks,
                Eric

                 

Log in to post a comment.