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 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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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);}
}
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
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
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
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);}
}
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
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
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
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