Hi,

Here is what happened:

A few days ago I created a migrator routine to convert a DBISAM4 database to EDB.  It
DROPS all the tables in the DBISAM db (except for reporting tables) and then migrates
those tables.  It worked fine.  Today I try it and I get multiple errors.  But never the
same error twice.  And after running it multiple times (getting a little further each
time), it's all working again.  

Some examples of the errors:
ElevateDB Error #1004 The primary key constraint PrimaryKey has been violated (Duplicate
key 42 found).
ElevateDB Error #100 There is an error in the metadata for the table Tools (Signature,
password, character set (ANSI/Unicode), or version number mismatch).
ElevateDB Error #1004 The primary key constraint PrimaryKey has been violated (Duplicate
key 13 found).

Here is the routine:
procedure TdmEDB.DoMigrate;
begin
 try
 with Session do
    Execute('DROP MIGRATOR "DBISAM4"');
 except
 end; // try
 with Session do
    Execute('CREATE MIGRATOR "DBISAM4" '+
            'MODULE "edbmigratedbisam4" '+
            'DESCRIPTION ''DBISAM 4 Migrator''');

 with qry do
 begin
   SQL.Text := 'select Name from Information.Tables WHERE Name NOT LIKE ''rb%''';
   Active := True;
   First;
   while not Eof do
   begin
     db.Execute('DROP TABLE "'+FieldByName('Name').AsString+'"');
     Next;
   end; // while
   qry.Close;
 end; // with

 if ExitAfterDrop then Exit;

 with db do
   begin
   //DatabaseName:='MyDatabase';
   //Database:='MyDatabase';
   Execute('MIGRATE DATABASE FROM "DBISAM4" '+
           'USING DatabaseDirectory = '''+SourceDir+''''+
           'WITH DATA');
   end;
end;


To me, it seems that something is not getting cleared when a table is DROPed and then
reCREATEed.  Of course this is in a large project with private data, so it won't be easy
to send a project to recreate it.

I've also had a similar problem when trying to restore a backup (the 100 error).

Any idea what might be causing this?

Andrew

Andrew


Unless you need to retain some of the data in the catalog take my approach and delete the catalog files prior to the migration.


Roy Lambert

Roy,

Thanks for the tip.  I've done that in the past.  (Although, I've had
problems restoring a database from a backup, even when using the option

In this case, I need to leave some tables, and re-import the others on
demand.

Thanks,
Andrew

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:89D5F7E9-9819-4FE6-83A6-7622F33A7039@news.elevatesoft.com...
> Andrew
>
>
> Unless you need to retain some of the data in the catalog take my approach
> and delete the catalog files prior to the migration.
>
>
> Roy Lambert

That last post wasn't done...

Roy,

Thanks for the tip.  I've done that in the past.  (Although, I've had
problems restoring a database from a backup, even when using the "Restore
Catalog" option.  I think it's related...but it's difficult to set up an
example.)

In this case, I need to leave some tables, and re-import the others on
demand.

Thanks,
Andrew
"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:89D5F7E9-9819-4FE6-83A6-7622F33A7039@news.elevatesoft.com...
> Andrew
>
>
> Unless you need to retain some of the data in the catalog take my approach
> and delete the catalog files prior to the migration.
>
>
> Roy Lambert

Andrew,

<< A few days ago I created a migrator routine to convert a DBISAM4 database
to EDB.  It DROPS all the tables in the DBISAM db (except for reporting
tables) and then migrates those tables.  It worked fine.  Today I try it and
I get multiple errors.  But never the same error twice.  And after running
it multiple times (getting a little further each
time), it's all working again. >>

What version of EDB are you using ?  Also, are you using the latest migrator
DLL version (1.06) ?  If you use an older version of the migrator, you will
run into issues when it tries to update the catalog during object creation.
EDB does not allow an older version of itself to access a configuration or
catalog file after it has been updated by a newer version of the product.
In such a case it will issue a #100 "version mismatch" error for the
configuration file or catalog file.

However, your error indicates that the version mismatch is occurring for a
table.  Are you copying around table files at all ?  This type of error
occurs when you copy or restore an older or newer version of table files
without also restoring or copying over the corresponding catalog file.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I'm using 1.06 (Sorry I forgot to mention that.)

I may be using the migrator from 1.05.  When I look at the dll, the file
version is 1.0.0.0.  How do I tell the difference?

Ignore the backup/restore issue I mentioned, for a moment.  All the errors I
received were on an existing database where I dropped and then recreated the
tables (using the migrator).

It may just boil down to using the wrong migrator dll.  (May I suggest
adding a version number and then checking it when the migrator is created?)

Thanks,
Andrew

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:02865B8C-AE4C-4386-82D3-EF51DB6E0353@news.elevatesoft.com...
> Andrew,
>
> << A few days ago I created a migrator routine to convert a DBISAM4
> database to EDB.  It DROPS all the tables in the DBISAM db (except for
> reporting tables) and then migrates those tables.  It worked fine.  Today
> I try it and I get multiple errors.  But never the same error twice.  And
> after running it multiple times (getting a little further each
> time), it's all working again. >>
>
> What version of EDB are you using ?  Also, are you using the latest
> migrator DLL version (1.06) ?  If you use an older version of the
> migrator, you will run into issues when it tries to update the catalog
> during object creation. EDB does not allow an older version of itself to
> access a configuration or catalog file after it has been updated by a
> newer version of the product. In such a case it will issue a #100 "version
> mismatch" error for the configuration file or catalog file.
>
> However, your error indicates that the version mismatch is occurring for a
> table.  Are you copying around table files at all ?  This type of error
> occurs when you copy or restore an older or newer version of table files
> without also restoring or copying over the corresponding catalog file.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Andrew,

<< I may be using the migrator from 1.05.  When I look at the dll, the file
version is 1.0.0.0.  How do I tell the difference? >>

That's a bug - it should be showing 1.05 or 1.06.   Look at the date - it
should be November 10th for 1.06.

<< Ignore the backup/restore issue I mentioned, for a moment.  All the
errors I received were on an existing database where I dropped and then
recreated the tables (using the migrator). >>

I was just using that as an example.  Based upon your code, there is no
restore taking place.

<< It may just boil down to using the wrong migrator dll.  (May I suggest
adding a version number and then checking it when the migrator is created?)
>>

I will do so, thanks.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:9C35DF40-F704-4515-9962-411AA593D603@news.elevatesoft.com...
> Andrew,
>
> << I may be using the migrator from 1.05.  When I look at the dll, the
> file version is 1.0.0.0.  How do I tell the difference? >>
>
> That's a bug - it should be showing 1.05 or 1.06.   Look at the date - it
> should be November 10th for 1.06.

Ok, that's the one I just changed it to.  (I must have been using 1.05).

I just tried the conversion process again (with the correct dll), and I am
getting the same results:
Errors that are not repeatable, but I think only once per table.  Each time
I try the import again, it gets furter...And now it's done, with no errors.
Maybe it's something left over from using the wrong migrator?

It seems like something is still there, even after dropping the table...and
when you recreate it it uses that record again with some bad data in it.
(Just what it seems like to me, as I don't have the source.)

> << Ignore the backup/restore issue I mentioned, for a moment.  All the
> errors I received were on an existing database where I dropped and then
> recreated the tables (using the migrator). >>
>
> I was just using that as an example.  Based upon your code, there is no
> restore taking place.

Ok, I just didn't want that to confuse the issue.

> << It may just boil down to using the wrong migrator dll.  (May I suggest
> adding a version number and then checking it when the migrator is
> created?)
> >>
>
> I will do so, thanks.

Great.

> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Thanks again,
Andrew

Andrew,

<< I just tried the conversion process again (with the correct dll), and I
am getting the same results:
Errors that are not repeatable, but I think only once per table.  Each time
I try the import again, it gets furter...And now it's done, with no errors.
Maybe it's something left over from using the wrong migrator? >>

Actually I was incorrect.  The version of the migrator won't have anything
to do with it because the migrator simply returns SQL and the calling EDB
engine is what actually performs the table creation, etc.

Could you possibly send me the source DBISAM database that you're trying
this with ?  I've tried this several times like this:

procedure TForm1.Button1Click(Sender: TObject);
begin
 with EDBQuery1 do
 begin
   SQL.Text := 'select Name from Information.Tables';
   Active := True;
   First;
   while not Eof do
   begin
     EDBDatabase1.Execute('DROP TABLE "'+FieldByName('Name').AsString+'"');
     Next;
   end; // while
   Close;
 end; // with

 with EDBDatabase1 do
   begin
   Execute('MIGRATE DATABASE FROM "DBISAM 4" '+
           'USING DatabaseDirectory = ''c:\migrate'''+
           'WITH DATA');
   end;
  ShowMessage('Done');
end;

and it works just fine every time.  I've run it 6 times now.

<< It seems like something is still there, even after dropping the
table...and when you recreate it it uses that record again with some bad
data in it. (Just what it seems like to me, as I don't have the source.) >>

If you are getting a duplicate key error during the initial migrate, then
you will get it again the next time you migrate, and the next time, etc.
Normally this type of error indicates that a) something is different with
respect to the way EDB is treating the data during the migrate, thus causing
the error, or b) the source tables are corrupted.  Usually the reason is a)
and is caused by things like the fact that EDB doesn't permit NULLs in
primary keys and DBISAM did, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:E14AF513-BAFF-4155-9A25-1CE4299552B2@news.elevatesoft.com...
> Andrew,
>
> << I just tried the conversion process again (with the correct dll), and I
> am getting the same results:
> Errors that are not repeatable, but I think only once per table.  Each
> time I try the import again, it gets furter...And now it's done, with no
> errors. Maybe it's something left over from using the wrong migrator? >>
>
> Actually I was incorrect.  The version of the migrator won't have anything
> to do with it because the migrator simply returns SQL and the calling EDB
> engine is what actually performs the table creation, etc.

Ok, interesting.

> Could you possibly send me the source DBISAM database that you're trying
> this with ?  I've tried this several times like this:

I'm sorry, but I can't.  It's private client data.  I havn't figured out
another way to reproduct the problem.

> procedure TForm1.Button1Click(Sender: TObject);
> begin
>  with EDBQuery1 do
>  begin
>    SQL.Text := 'select Name from Information.Tables';
>    Active := True;
>    First;
>    while not Eof do
>    begin
>      EDBDatabase1.Execute('DROP TABLE
> "'+FieldByName('Name').AsString+'"');
>      Next;
>    end; // while
>    Close;
>  end; // with
>
>  with EDBDatabase1 do
>    begin
>    Execute('MIGRATE DATABASE FROM "DBISAM 4" '+
>            'USING DatabaseDirectory = ''c:\migrate'''+
>            'WITH DATA');
>    end;
>   ShowMessage('Done');
> end;
>
> and it works just fine every time.  I've run it 6 times now.

Yes, and after the initial problems, it works fine for me, until some
unknown event.

> << It seems like something is still there, even after dropping the
> table...and when you recreate it it uses that record again with some bad
> data in it. (Just what it seems like to me, as I don't have the source.)
>  >>
>
> If you are getting a duplicate key error during the initial migrate, then
> you will get it again the next time you migrate, and the next time, etc.

That's what I'm trying to say.  I get a duplicate key error once (for each
table). (And less often, the error 100.)  I re-run the conversion and I
don't get it.  No changes were made to the DBISAM (version 4) database.  I
don't even exit out of my conversion program...I just continue after the
error, execute the conversion again, and it makes it "further" into the
conversion.  Eventually, it completes with no errors.  Then I can run it
many times without any errors.  Very wierd.  

> Normally this type of error indicates that a) something is different with
> respect to the way EDB is treating the data during the migrate, thus
> causing the error, or b) the source tables are corrupted.  Usually the
> reason is a) and is caused by things like the fact that EDB doesn't permit
> NULLs in primary keys and DBISAM did, etc.

Yes, that makes sense to me.

> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

A question:  When you drop a table and then re-create it, does it's
definition in the catalog get cleared first?  Could it be re-using that
entry without resetting some defaults?

Anyway, I'll keep trying to figure out a way to reproduce the problem.  If
you have any other suggestions, please let me know.

Thanks,
Andrew