Hi

Have an existing system (27 tables - some master detail tables also) in delphi written for dbisam.

I now need to migrate the whole thing to edb database/tables.

1. Is there a recommended way to do this for both efficiency and correctness.

Any pointers, guidance or links to help would be appreciated.

Thank you


Kamran

There is a migrator modul you can use, but it doesn't do well what I expected for me, but it helped to start with.

I have two problems with it:
1. The migrator converts DBISAM MONEY datatype to FLOAT and this is not what I want. I want DECIMAL(19,4) instead.

2. It has troubles with Primary Key which inlcudes NULL values. You can specify that it builds UNIQUE KEYS instead, but this would be a global setting and affects than all your tables. As such, we need first to separate all tables with PK which includes NULL value and other tables which don't. Than you must run the migrator two times, once with UNIQUE KEY generation and once without for the rest of the tables.

After that I did a "Reverse Engineer Database" from inside the ElevatDB Manager and created a Create script. In this script I changed all MONEY fields to DECIMAL(19,4) and changed other things like collations per fields as needed.

After that was done, I created a new database and ran the modified Create script to get an emtpy database. Now I have wroten a small Delphi application witch copied all data from every DBISAM table to ElevateDB in this fashion.

while not DBISAM.Eof do
begin
 EDB.Insert;
 for i := 0 to DBSIAM.Fields.Count - 1 do
   if DBISAM.Fields[i].AsString <> '' then
     EDB.Fields[i].AsString := DBISAM.Fields[i].AsString
   else
     EDB.Fields[i].Clear;  // not realy needed here
 end;
 EBD.Post;
 DBISAM.Next;
end;

In additon to that I derived all EDB components and override some methodes to fixe some EDB behaviours I don't like. They are:

1. Empty string gets not saved as NULL.
2. There is no MONEY type which gives us the display of the currency without to uses persistent fields and set there Currency property to True. IMO, this is very annoying.
3. I want to prevent any open Datasets to get automaticly open at runtime too. I hate that annoying Delphi behaviour for years.

So I have wroten my own EDB derived components to fix this issues:
1. It does now check for empty strings and sets this field to NULL, as it was in DBISAM. As I never want emtpy strings in my database, I overriden the InternalPost methode of TEDBTable/TEDBQuery/... to do this atomaticly.

2. I have changed/forced the default property values of Active, Connected and all that properties so their state gets not saved on running/saving the forms. So any open Dataset in the IDE is atomaticly closed at runtime.

3. I use the Attributes of the tables to to specify which fields are in fact currency fields. So I added a "CURRENCY=field1;field2;field" Attribute to tables with such fields. Now I have changed (overriden) the EDB Componnets to read this Attribute on opening a table/query/procedure and set the currency property of the specified fields to true. Now all money fields are fine displayed in Delphi as currency fields without the need to make persistent fields or set it all at runtime in the source.

I planing to post this components here in the binaries sometime in the next days so any one else can use it.

Rolf,

<< 1. The migrator converts DBISAM MONEY datatype to FLOAT and this is not what I want. I want DECIMAL(19,4) instead. >>

DBISAM money fields *are* floating-point fields, so there's nothing odd going on here.  We cannot just assume that it's okay to truncate any fractions at a specific scale.  Some customers may *want* more than 2 decimal places for fractional currency values.

<< 2. I have changed/forced the default property values of Active, Connected and all that properties so their state gets not saved on running/saving the forms. So any open Dataset in the IDE is atomaticly closed at runtime. >>

You can use this property for that purpose:

http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=rsdelphiwin32&version=10T&comp=TEDBEngine&prop=StoreActive

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks for pointing me to StoreActive! I was not aware of it.

AFAIK DBISAM uses the Delphi Currency datatype internally for handling money datatatype on the component site of DBISAM. The Currency datatype has 4 decimals so we would never have more than 4 decimal places. The main issue for me is, that EDB dosn't have a Money type at all and so our delphi application don't show money fields as such and we get now TFloatField instead TCurrencyField. This means we must use persistent fields, what I will prevent as far as possible, or write code to set the Currency property it in every AfterOpen event.

* thank you ** Rolf and Tim

that's very helpful


Rolf Frei wrote:

Tim

Thanks for pointing me to StoreActive! I was not aware of it.

AFAIK DBISAM uses the Delphi Currency datatype internally for handling money datatatype on the component site of DBISAM. The Currency datatype has 4 decimals so we would never have more than 4 decimal places. The main issue for me is, that EDB dosn't have a Money type at all and so our delphi application don't show money fields as such and we get now TFloatField instead TCurrencyField. This means we must use persistent fields, what I will prevent as far as possible, or write code to set the Currency property it in every AfterOpen event.

kamran

>Have an existing system (27 tables - some master detail tables also) in delphi written for dbisam.
>
>I now need to migrate the whole thing to edb database/tables.
>
>1. Is there a recommended way to do this for both efficiency and correctness.
>
>Any pointers, guidance or links to help would be appreciated.

Its difficult without knowing your application. Once your data is converted the main differences are in the syntax of SQL. SO here's an outline. Once you get started post questions as needed

1. Have a read of the manual and a play with the demo app so you understand the new config / catalog approach vs DBISAM's directory based one
2. Make a backup of your data
3. Use EDBManager to convert (migrate) your data to the new format. There may be some problems such as NULLs in the primary key, if so fix and repeat, review table structures - the migrators will generally be right but its possible you may want/need something different
4. Review any sql you use. A quick way is to cut'n'paste the code into EDBManager and try Preparing it. It won't guarantee things but its a good first check
5. find a global string replace tool (I have one I wrote) to change all DBISAMxxx to EDBxxx components
6. Compile & test
7. Fix bugs or GOTO 9
8. GOTO 6
9. Start looking at queries individually, profile in EDBManager and add necessary indices, alter SQL for speed

Gotchas I can remember (its been a long time)

a) NULL is no longer the same as '' (ie an empty string). You need to decide what you're doing about this.
b) all table structure management is now carried out via sql there are no longer any options for doing this via table methods
c) data is no longer right trimmed when you post it
d) insensitive (canned) result sets can no longer be edited
e) the sql now follows the 2003 standard - there's quite a few changes, one that got me was the JOIN no longer being used in DELETEs (subselects are used instead)

I'm sure others can add to the list.

Roy Lambert

Tim

><< 2. I have changed/forced the default property values of Active, Connected and all that properties so their state gets not saved on running/saving the forms. So any open Dataset in the IDE is atomaticly closed at runtime. >>
>
>You can use this property for that purpose:
>
>http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=rsdelphiwin32&version=10T&comp=TEDBEngine&prop=StoreActive

I've been using GExperts for that for ages. Now I know about it I may set this property as well on a belt & braces approach.

Roy

Rolf


If its not a commercial secret can you post you subclassed components to the extensions newsgroup. Mine are already in there.

Roy Lambert

Rolf


When I converted my main app, because I was doing a lot of database structure changes, I did a lot of work in DBISAM, creating an interim database which is the one that was then migrated. This gave me a chance to sort out nulls in primary keys, money fields etc. Until we get real AI a mechanical translation (which is all the migrators do) requires a review (as I say in step 3) by a human being (or reasonable facsimile thereof)

It must have taken 20 repetitions and adjustment of structure change code before I was happy.

Roy Lambert

Hi Roy

I will make a start ...

thanks for that !

cheers

kamran

Roy Lambert wrote:

Rolf


When I converted my main app, because I was doing a lot of database structure changes, I did a lot of work in DBISAM, creating an interim database which is the one that was then migrated. This gave me a chance to sort out nulls in primary keys, money fields etc. Until we get real AI a mechanical translation (which is all the migrators do) requires a review (as I say in step 3) by a human being (or reasonable facsimile thereof)

It must have taken 20 repetitions and adjustment of structure change code before I was happy.

Roy Lambert