Issue 115401

Summary: copytablewizard: currency fields from MS Access loose decimal places
Product: Base Reporter: ludob <ludo.brands>
Component: codeAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOO330m13   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
MS Access table containing currency field none

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.