Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 10 of 24 total

migrate DBISAM4 to ElevateDB (can field types be altered)

Fri, May 25 2012 2:33 AM	Permanent Link
IQA	Hi Tim & Team, I'm migrate DBISAM4 to ElevateDB and it works brilliantly. However, the original DBISAM stores its price fields as a Currency which then gets converted to float. This appeared OK, but it seems to bring across a few rounding problems with a few records. Therefore would it be possible to migrate but have the Currency field types go across as Decimal (scale 2) as I'm sure that would correct the problem. Is there an easy way for me to configure the migration to do this? Thanks again, Phil
Fri, May 25 2012 3:39 AM	Permanent Link
IQA	Just as an example to show you what I'm refering to here's a screen capture from both ElevateDB manager and DBISAM Database Utility Now if I convert the migrated Currency field (which is now a float, into a DECIMAL 19,2) and run some reports, a few of the records are out by 2 or 3 cents. Any guidance or help on this would be appreciated. Thanks, Phil. Attachments: example.jpg
Fri, May 25 2012 5:27 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Phil >Now if I convert the migrated Currency field (which is now a float, into >a DECIMAL 19,2) and run some reports, a few of the records are out by 2 >or 3 cents. You say you converted the column to DECIMAL(19,2) but the jpg shows tax1 as a FLOAT. That could explain the problem. Roy Lambert [Team Elevate]
Fri, May 25 2012 5:39 AM	Permanent Link
IQA	Hi Roy, Yes that is showing a float, but that's a screen shot straight after the migration, prior to me then converting the float field type to a DECIMAL (19,2). Once I convert it to DECIMAL (19,2) I then have discrepancies in a few reports. I guess it might be confusing me, cause when I view the original data in DBISAM Database Utility it shows the currency fields to 2 decimal places (i.e $0.91), but then after migration to float it shows lots of decimal places (i.e 0.909090995788575) in the ElevateDB manager. Then when I try to convert it to DECIMAL(19,2) that causes the discrepancies in report totals. Thanks, Phil.
Fri, May 25 2012 6:29 AM	Permanent Link
IQA	Just in addition, some of the original DBISAM(v4) currency fields where $0.00 and after migrating the float shows as things like 2.84494650060196E-15 If anyone else has encountered this or has an explanation I would appreciate your thoughts. Cheers, Phil.
Fri, May 25 2012 6:41 AM	Permanent Link
IQA	Just in addition... Here's another screen shot showing both DBISAM and ElevateDB. After the migration and BEFORE I even convert the float to DECIMAL(19,2) Some of the original $0.00 values after migration are weird. (see screen shot) Attachments: example2.jpg
Fri, May 25 2012 7:57 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Phil Its about what I'd expect. AFAIK in DBISAM the currency type was a float and Delphi took care of the formatting so that things looked right. What you're now seeing is reality - and it is not at fault I just tried a quick experiment in DBSys - created a memory table with two fields one - float, two - currency and populated it with identical values: 1 0.5 0.0001 999.999 They display differently, and SUM differently. If you CAST two as FLOAT then you get exactly what is in one. Floats are a bit of a nightmare and should never have been used for currency. Where and how exactly the rounding in DBISAM is applied I don't know. Working in DECIMAL(0,2) will mean correct results, but there may have been a different rounding applied in the conversion which is leading to different totals. Starting from scratch there would be no problem, but starting with existing data is going to require some manual intervention. Roy Lambert [Team Elevate]
Fri, May 25 2012 8:22 AM	Permanent Link
Jan Ferguson Data Software Solutions, Inc. Team Elevate	Actually, in DBISAM the currency type was BCD. -- Jan Ferguson Roy Lambert wrote: > Phil > > Its about what I'd expect. AFAIK in DBISAM the currency type was a > float and Delphi took care of the formatting so that things looked > right.
Fri, May 25 2012 10:41 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Jan >Actually, in DBISAM the currency type was BCD. I'll take your word for it. So from memory that means stored to 4(?) and converted to 2 decimals (depending on Windows settings I think). Better than float but still liable to produce rounding errors. Roy Lambert [Team Elevate]
Fri, May 25 2012 4:09 PM	Permanent Link
Jan Ferguson Data Software Solutions, Inc. Team Elevate	Roy, I never noticed any rounding errors with the BCD data type however when I converted tables from DBISAM v4.x to EDB, it converted all the BCD data types to floats. It's easy enough to change them but I didn't notice it at first when I began my conversions back when. -- Jan Roy Lambert wrote: > Jan > > > Actually, in DBISAM the currency type was BCD. > > I'll take your word for it. > > > So from memory that means stored to 4(?) and converted to 2 decimals > (depending on Windows settings I think). Better than float but still > liable to produce rounding errors. > > Roy Lambert [Team Elevate] Roy

	Page 1 of 3	Next Page »
Jump to Page: 1 2 3