Hi Tim,

I've gone live with EDB <gulp!> and posted an auto-update to my application
which will upgrade all customers from DBISAM4 to EDB.

I've just had a customer report that they're receiving migration errors. I
got them to email their database (thinking it was something they were doing
wrong) and in fact I can't migrate their database either!

The offending DBISAM table which won't migrate is here :-
http://files.pioneersoftware.co.uk/temp/alloc.7z

I'm trying to migrate it using the UNICODE edbmigratedbisam4.dll (2.02b10).

In my application (using ADO.NET) the Exception thrown during the
migratration process is "Invalid Currency Value".

When I try to migrate this table in EDB Manager, the error reported is
"Invalid floating point operation".

I ran a DBISAM Repair on the table (in case it was corrupted) but no
difference. So far this is the only customer who has reported a problem.
Hopefully there won't be too many others!

Regards,

Hedley

Hedley


Bit of  a cheat but I just tried this in the non-unicode ElevateDB

1. Use DBSys to reverse engineer the table with data
2. Rename the offending table
3. Open the SQL and create a new table
4. Migrate the newly created table - this works whilst the original doesn't (probably has a duff character somewhere that's filtered out in the reverse engineering)

Not a brilliant solution but it works

Roy Lambert [Team Elevate]

Thanks Roy,

Unfortunately, I have just had another customer with the same problem (but a
different table) and I can't really afford the time to start reverse-coding
the table SQL for every customer with this problem!

Is there any chance of a fix please Tim?!

Hedley,

<< I've just had a customer report that they're receiving migration errors.
I got them to email their database (thinking it was something they were
doing wrong) and in fact I can't migrate their database either!

The offending DBISAM table which won't migrate is here :-
http://files.pioneersoftware.co.uk/temp/alloc.7z

I'm trying to migrate it using the UNICODE edbmigratedbisam4.dll (2.02b10).

In my application (using ADO.NET) the Exception thrown during the
migratration process is "Invalid Currency Value". >>

This is a data content issue with that particular table.  For some reason
there is an Amount value, specifically this one:

INSERT INTO "alloc" VALUES (NULL,TIMESTAMP '2008-01-08
12:46:57.0177',NULL,TIMESTAMP '2008-01-08
12:46:57.0177',1000000010000004882,1000000010000000073,1000000010000003349,1000000010000004886,NULL,NULL,-16212957636225.9532)

Whereby the source Amount value appears as 0.00 in the DBISAM table, but
actually contains an invalid currency value.  I'm not sure if they tried to
type in a very large number or what, but somehow invalid data got into the
table.  You can confirm this by doing the following in DBSYS:

1) Open the alloc table.
2) Navigate the record noted above.
3) The Amount value appears as 0.00.
4) Set a filter on the table of "Amount=0.00".
5) You'll notice that the noted record does *not* appear.
6) Clear the filter.
7) Now type in 0.00 over the existing 0.00 for the noted record, and then
save the edit.
8) Set the filter again, and you'll now notice that the noted record now
appears.

Unfortunately there is nothing we can do about source data content issues
like these.  By the time the value gets to EDB it is just a string, and EDB
cannot just ignore the invalid value or set it to 0.00 automatically without
letting you know of the error condition.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ok - thanks for the explanation Tim - so is there a simple workaround?

For example, is there a query I can execute in DBISAM (before the migration)
which will weed out these values - perhaps setting them to 0?

Thanks!

Hedley


I don't have your table any more and it doesn't want to download so I can't check it out but doesn't a simple

field > big number

select them?

Roy Lambert

Unfortunately not. The following query returns two rows both with 0.00
amounts :-

select * from alloc where amount=0

Unfortunately neither of those rows are problematic, the problematic one has
an 'id' of 1000000010000004882 so if you do the following query :-

select * from alloc where id=1000000010000004882
union
select * from alloc where amount=0

....then you now see 3 rows with 0 amounts.

I'm happy to include something in my conversion routine which will delete
these invalid rows - if someone (probably Tim!) can please just tell me how
to identify them!

Many thanks!

Hedley


I've been having a play around (don't know why when I should be working) and I think your problem is a bit bigger than just the one entry

Firstly I could find nothing in SQL to select out just the affected rows. But the good news is that it looks handleable in simple Delphi (at least in D6 - I don't have DBISAM installed in D2006).

A filter alloc.Filter := 'amount > 0 and amount <0.01'; gave some interesting results. The one you know about translates to 0.0005

Trying this as the WHERE clause in DBSys also thows up the same 9 items. This query will show you the problem

select amount, cast(amount as float) from alloc where amount > 0 and amount <0.01

This should at least get you under way

If you're ever up this way you own me at least a pint (probably nearer a gallon) for the way this made my brain ache.

Roy Lambert [Team Elevate]

Hedley Muscroft wrote:

> Ok - thanks for the explanation Tim - so is there a simple
> workaround?
>
> For example, is there a query I can execute in DBISAM (before the
> migration) which will weed out these values - perhaps setting them
> to 0?
>
> Thanks!

No idea if this will work .. but if selecting the value from that
record gives a 'correct' result of 0.00, then I guess you could do a
pre-check along these lines:

1.  select all values into a query or temp table (corrects value ..
maybe)
2.  count number of 0.00 values from 1.
3.  count number of 0.00 values in original table

If the counts are different you have a problem.  
If there is a problem you can proceed to select the records from 1.
with 0.00 which do not have a 0.00 in the original table. Umm...?

--

Hedley,

<< Ok - thanks for the explanation Tim - so is there a simple workaround?

For example, is there a query I can execute in DBISAM (before the
migration) which will weed out these values - perhaps setting them to 0? >>

Well, I'm not really sure if there is.  That's one of the reasons why the
migration actually works in ElevateDB - sometimes the number comes over as
valid, and other times it causes the floating-point issue.  DBISAM stores
the BCD values as TBCD structures, so if they are invalid then the results
can be unpredictable.  I think that it is assumed on Delphi's part that the
invalid values will be caught prior to conversion to a TBCD.

I'm also still trying to figure out how to "corrupt" a BCD value in DBISAM.
Every invalid value that I attempt to enter is trapped properly.  Does the
customer use the DBISAM ODBC Driver to enter/modify data in this table ?

--
Tim Young
Elevate Software
www.elevatesoft.com