Issue 115401 - copytablewizard: currency fields from MS Access loose decimal places
Summary: copytablewizard: currency fields from MS Access loose decimal places
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO330m13
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2010-11-04 11:34 UTC by ludob
Modified: 2013-01-29 22:01 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

MS Access table containing currency field (116.00 KB, application/octet-stream)
2010-11-04 11:35 UTC, ludob
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description ludob 2010-11-04 11:34:22 UTC
Copy MS Access table containing currency fields to ODBC databases or internal
database. The destination table will be created with a NUMERIC (internal
database, mssql, mysql) or DECIMAL (oracle 10) field type, which is fine, but
with zero decimal places. The Access currency format is defined as having 4
decimal places. 
Test file attached.
Comment 1 ludob 2010-11-04 11:35:29 UTC
Created attachment 72853 [details]
MS Access table containing currency field
Comment 2 r4zoli 2010-11-04 14:15:44 UTC
I checked in OOo 3.3 RC3 on win7. 
During the copy table wizard you can set the decimal places, and copy all data
to odb file.
Not use copy table wizard, without checking the settings.

I set it is as invalid. 
Comment 3 r4zoli 2010-11-04 14:16:11 UTC
Invalid closing.
Comment 4 ludob 2010-11-04 14:27:44 UTC
Of course you can change it manually for every currency field... But why would a
wizard propose 0 decimals for a field that has always 4 decimal places? 
I hit this problem when transferring a 20+ table database... Together with issue
115398 which forces to change all integers, copying tables from access becomes
very cumbersome. 
Manually tuning certain odd fields is acceptable, but modifying every integer
and currency field isn't.
Comment 5 ludob 2010-11-05 14:19:18 UTC
I did some further investigation into what ADO reports as NUMERIC_PRECISION and
NUMERIC_SCALE by writing a small c# program that retrieves and prints
Connection.GetSchema(OleDbMetaDataCollectionNames.Columns) data for the test
database attached. They are respectively 19 and 0 for currency fields. This is
probably the reason why the wizard proposes 0 decimal places. 
But... NUMERIC_SCALE has apparently only a meaning for data types that have
settable scales like the numeric and decimal types and not for fixed scale data
types such as currency. 
Microsoft in its integration services (platform to convert other databases to
SQLServer = copytablewizard from MS ;) ) defines currency scale as 4. See, definition of DT_CY. At
the bottom of that page, the JET currency data type is mapped to DT_CY... It
isn't a coincidence that DT_CY has numeric value 6 which is the same as
OleDbType.Currency or adCurrency...
"Hard-wiring" the scale of currency to 4 seems to be the solution to this issue.