I am migrating from DBISAM and there I have a routine to change the database structure according with FieldDefs. In ElevateDB there is no AlterTable method and I am changing my DBISAM code to pure SQL.

In a scenario of adding new fields, delete other fields and change the position of other fields what should be the correct way of doing this ?

1) Analize fields to be removed
2) Analize fields to be changed
3) Analize fields to be inserted

I know how to use ALTER TABLE statement but the order of using it is the key of table structure. I will have an array of field information to deal with and one or more alter tables to change the structure.

I would like some opinions about it.

Hi Jose,

> In a scenario of adding new fields, delete other fields and change the
> position of other fields what should be the correct way of doing this ?

I'm not sure if this is the "correct" way of doing it, but I use the Reverse
Engineering function built into EDB.

1. I create a in memory database using the same session as the database that
I need to update.

2. Build the in memory database to look like the final structure of the
database should be.

3. Use the Reverse Engineering function to compare the two databases and
build a upgrade script.

4. Run the upgrade script against the real database.

5. Drop the in memory database.

Hope this helps.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
---------------------------------------

Jose


I like Terry's idea, but to ask a question - what are you actually asking for?

Do you need guidance in how to build the sql statement, how to order the columns in the final table or something different?

Roy Lambert

First of all, thanks Terry for your contribution.

Roy

I need guidance on how to order the columns in the final table but I think I have already got what I need. Just creating a brand new table with new structure, read the old one analizing what changed, data type conversions, fields dropped, etc. and build a simple "INSERT INTO NEWTABLE (f1,f2,f3) SELECT (f1,f2,f3) FROM OLDTABLE

Thanks anyway

Eduardo

Jose


I thought that would be it. If the column order isn't what you want finally have a look at the ALTER TABLE ALTER COLUMN MOVE TO command.

Roy Lambert

Jose

Do be sure to spend a bit of time reviewing the capabilities of EDB's "COMPARE DATABASE" SQL commands before you try to do too much manually.

Also the UPGRADE feature of EDBMgr. If you have 2 databases this can greatly assist in generating ALTER SQL, especially if the TABLE names are the same between different versions. In case you haven't discovered the somewhat hidden feature of EDB for DB upgrading:

1. Create your 2 databases under a single session.
2. Pick DB1, Select Reverse Engineer Database, Select "Upgrade", in the Upgrade Opions tab which appears select DB2.
3. Click OK.