MSSQL conversion, error on small decimal values, being set to 0

[DavidUnderdown]

Description: I'm working on an MSSQL database which (across several tables) has fields of type decimal(20,12). For values x in the range 1E6<x<-1E6 (ie 0.000001<x<-0.000001) an error is reported and the log states that the value has been changed to 0 (ie data loss).

I won't be able to provide the full database, but could provide a sample table if it would be useful for analysis

Steps required to reproduce the bug:

1. Microsoft Sql Server database (compatibility level SQL Server 2005, but running on SQL Server 2016) with fields of type decimal(20,12)
2. Run export with params (dummy values for some shown by names with %% around them) --import microsoft-sql-server --import-server-name=%servername% --import-port-number=4333 --import-database=%databasename% --import-username="%_username%" --import-password="%_password%" -ide --export siard-2 --export-file=%exportlocation% -ep -eel --export-meta-archiver="Digital Preservation Department, The National Archives"
3. Reported erro is in logs

Attach the dbptk-app.log.txt file below: Extract from log given

2018-11-20 09:41:39,267 [main] DEBUG (c.d.m.j.i.JDBCImportModule) could not parse `-8.70000` as integer
java.lang.NumberFormatException: For input string: "-8.70000"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.rawToCellSimpleTypeNumericExact(JDBCImportModule.java:1314)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToCell(JDBCImportModule.java:1240)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToRow(JDBCImportModule.java:1183)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:1628)
	at com.databasepreservation.Main.run(Main.java:161)
	at com.databasepreservation.Main.internal_main(Main.java:108)
	at com.databasepreservation.Main.main(Main.java:86)
2018-11-20 09:41:39,268 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-8.70000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
2018-11-20 09:41:39,268 [main] DEBUG (c.d.m.j.i.JDBCImportModule) could not parse `-7.0000` as integer
java.lang.NumberFormatException: For input string: "-7.0000"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.rawToCellSimpleTypeNumericExact(JDBCImportModule.java:1314)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToCell(JDBCImportModule.java:1240)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToRow(JDBCImportModule.java:1183)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:1628)
	at com.databasepreservation.Main.run(Main.java:161)
	at com.databasepreservation.Main.internal_main(Main.java:108)
	at com.databasepreservation.Main.main(Main.java:86)
2018-11-20 09:41:39,269 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-7.0000E-8` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
2018-11-20 09:41:39,270 [main] DEBUG (c.d.m.j.i.JDBCImportModule) could not parse `-2.10000` as integer
java.lang.NumberFormatException: For input string: "-2.10000"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.rawToCellSimpleTypeNumericExact(JDBCImportModule.java:1314)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToCell(JDBCImportModule.java:1240)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.convertRawToRow(JDBCImportModule.java:1183)
	at com.databasepreservation.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:1628)
	at com.databasepreservation.Main.run(Main.java:161)
	at com.databasepreservation.Main.internal_main(Main.java:108)
	at com.databasepreservation.Main.main(Main.java:86)
2018-11-20 09:41:39,271 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-2.10000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED

[chalkos]

This might be fixed by #337, since this looks like the number and its representation is distorted before DBPTK can do anything about it (ie: the driver is not correctly handling the value)

[DavidUnderdown]

That may help - but the representation given is actually exact, just not in the format currently expected.

[chalkos]

Yes, that's what I meant. The number is there at the start of the process but it is poorly handled somewhere to the point that it becomes unreadable (by the code) as an exact number later on.

[DavidUnderdown]

What I was trying to say is that it is still an exact number, even though the code is at present not recognising it as such. It appears to be the assumption in the code that it will get an integer to handle that is wrong, not necessarily what is being done to it previously.

In the examples cited -8.70000E-7 is an exact representation of the original decimal data (-0.00000087) as is -7.0000E-8 (-0.00000007) and -2.10000E-7 (-0.00000021). In no case that I've examined in detail is there any additional rounding or anything which makes this version of the number differ in value from the original decimal field value in the database. It is not clear why the type of representation returned by the JDBC changes at this order of magnitude but I can't see any particular reason why it could not be handled in code.

I've since also created a SIARD file for this database using the Swiss Federal Archives' SIARDGui tool, no errors are reported. That tool also seems to be using JDBC 4.0 but seems to be handling this case correctly.

[luis100]

@DavidUnderdown do you have an example so we can check if this bug still exists?

[DavidUnderdown]

@luis100 - as I said when I raised the issue, unfortunately I can't share this database. However, if you have a beta release or similar that I can use I'm happy to retest here.

[luis100]

If you are available to test it again, that would suffice for us, please test with the latest release.

[DavidUnderdown]

Great - I will try and fit that in this week. I'm just back off holiday, so I've got rather a lot of catching up to do.

[DavidUnderdown]

I'm still seeing the issue I'm afraid

Line 21565: 2019-10-25 10:40:01,677 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `1.02860E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column MARKET_VALUE
Line 21603: 2019-10-25 10:40:01,952 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-8.70000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21617: 2019-10-25 10:40:01,956 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-7.0000E-8` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21631: 2019-10-25 10:40:01,963 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-2.10000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21645: 2019-10-25 10:40:01,967 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-6.70000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21659: 2019-10-25 10:40:01,972 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-6.40000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21673: 2019-10-25 10:40:01,980 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-7.20000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21687: 2019-10-25 10:40:01,986 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-4.50000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21701: 2019-10-25 10:40:01,995 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-2.50000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21715: 2019-10-25 10:40:02,003 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-1.0000E-8` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21729: 2019-10-25 10:40:02,009 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-4.00000E-7` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED
Line 21743: 2019-10-25 10:40:02,015 [main] DEBUG (c.d.m.Reporter) something failed, message: - Warning: `-5.0000E-8` changed to `0` because  exact numeric values can not have exponent (`E`)  in column NOMINAL_ISSUED

[hmiguim]

Hello @DavidUnderdown,

We update the version of DBPTK and we include a fix to this issue. Could you run the migration again and see if the data is being correctly converted?

[DavidUnderdown]

Thanks - I saw the release. I'll test later in the week (we're about to have a scheduled network outage for an upgrade).

[DavidUnderdown]

From some initial testing that seems to have resolved it. I'd like to do a bit more digging before I absolutely confirm that though.