Description:
During migration, the scale of Access singles are being set to 1/3 the field length with no concern to what is set within Access. Upon import, the data is then rounded causing loss of decimal precision.
This is basically the same as Bug #17880 which is currently languishing in a "No Feedback" status.
How to repeat:
Create an Access table with three fields:
Create field 1
Field Name: name
Data Type: text
Field Size: 50 (default)
Create field 2
Field Name: auto
Data Type: number
Field Size: Single
Decimal Places: Auto (default)
Create field 3
Field Name: fixed
Data Type: number
Field Size: Single
Decimal Places: 3
Populate table with sample data:
0 1 1
1 1.1 1.1
2 1.12 1.12
3 1.123 1.123
4 1.1234 1.1234
5 1.12345 1.12345
6 1.123456 1.123456
Run Migration (run with default options)
Examine resultant table:
Database structure:
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| auto | decimal(7,2) | YES | | NULL | |
| fixed | decimal(7,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
Database values:
+-------+-------+--------+
| name | auto | fixed |
+-------+-------+--------+
| 0 | 1.00 | 1.00 |
| 1 | 1.10 | 1.10 |
| 2 | 1.12 | 1.12 |
| 3 | 1.12 | 1.12 |
| 4 | 1.12 | 1.12 |
| 5 | 1.12 | 1.12 |
| 6 | 1.12 | 1.12 |
+-------+-------+--------+
Both numeric columns got changed to a fixed scale of 2 and the decimal portion of the numbers got rounded off. Also notice how within Access, the Decimal places setting has not real effect. Even though, in Access, I specified a fixed number of decimal places for the 'fixed' column, Access still allowed more than three digits right of the decimal. This behavior should be reflected in the migration in order to ensure all data is transferred accurately.
Suggested fix:
From what I can see, it seems the problem comes from the code in MigrationAccess.java which attempts to translate the field structure:
187 } else if (sourceDatatypeName.equalsIgnoreCase("REAL")
188 || sourceDatatypeName.equalsIgnoreCase("DOUBLE")) {
189 targetColumn.setDatatypeName("DOUBLE");
190
191 if ((targetColumn.getScale() == 0)
192 && (migrationParams != null)
193 && (migrationParams.get("autoDecimalDigits")
194 .equalsIgnoreCase("yes"))) {
195 targetColumn.setScale(targetColumn.getPrecision() / 3);
196 }
I believe line 195 is forcing the scale for these fields to be 1/3 of precision. To me, this does not seem like an accurate translation when Access allows for total of 7 digits and variable scale on single type fields. In my tests, it seems the better option would be to set the precision and scale
to -1 during the translation as is done for FLOAT values from Oracle in MigrationOracle.java.
To implement this change would mean replacing the above line 195 with this code:
195 targetColumn.setScale( -1 ); targetColumn.setPrecision( -1 );
My tests show this results in a target field of DOUBLE with no length or scale. The major difference being the resulting field holds more digits than the original Access field. However, it does properly allow for variable scale.
