I have successfully migrated my database from DBISAM, but decided to
complicate matters by doing so long delayed table and column name changes.

This is what I did amongst other things:-
--------------------------------------
SCRIPT ()
BEGIN
 ...
  EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners';
  EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode';
 ...
END
--------------------------------------

Which is OK as far as it goes.

BUT LandlordCode was the Primary Key of the Landlords Table (now Owners
Table) and when I look at the Primary Key of the Owners Table with EDBMgr, I
see that the (now non-existent) LandlordCode column is still the primary
Key.  Gosh, what do I do now?

My first thought was to change the primary key using my script but I can't
get to grips with the syntax ...

ALTER TABLE Owners ALTER CONSTRAINT ..... something ...

I didn't have a "next thought"

Can anyone point me at an example? - can't find anything in the manual nor
by searching the newsgroups.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

On 15/08/2013 3:01 PM, Jeff Cook wrote:

> --------------------------------------
> SCRIPT ()
> BEGIN
>    ...
>     EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners';
>     EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode';
>    ...
> END
> --------------------------------------

Something like :-

EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners';

EXECUTE IMMEDIATE 'ALTER TABLE OWNERS
  DROP CONSTRAINT LandlordCode ...'

EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode';

EXECUTE IMMEDIATE 'ALTER TABLE OWNERS
  ADD CONSTRAINT OwnerCode ...  ...'

You could spend a whole lotta time coding these changes.


However there is an easier way. Please see my post "How to generate a
script for altering objects in a database?"

This talks about the COMPARE DATABASE statement.

Unfortunately I do not know how to get the script and then run it. When
I do I will be able to solve my own migration problems.

Regards,
  Peter Evans

On 15/08/2013 6:13 PM, Peter Evans wrote:

>
> However there is an easier way. Please see my post "How to generate a
> script for altering objects in a database?"

OK, here is a partial solution, I hope. I say hope as I won't be able to
test this until tomorrow.

Here is the broad outline :-

Step 1. Perform your migration from DBISAM 3 remembering to migrate the
data.

Step 2. Within ElevateDB Manager choose to generate a script using
Create, then on next tab sheet remembering to check Include Rows.

Step 3. Save the script.

Step 4. Create another database in a separate folder. Call the database
<MigratedDatabaseName>Altered.

Step 5. Switch to <MigratedDatabaseName>Altered

Step 6. Run Reverse Engineer Database. Set Type = Upgrade.

Step 7. Backup the database <MigratedDatabaseName>

Step 8. Run the script against <MigratedDatabaseName>.

Step 9. Run reverse engineer.

Step 10. Using a text file difference editor to compare the two scripts.
They should be the same.

Note - I may have got source and target databases confused. I am not
running the ElevateDB Manager on this machine...

Unfortunately in Step 10 they won't be the same! Why, because there will
be a difference in the date/time.

Here is another suggestion - an option to suppress the date. Or the
like. So that when automatic methods are used to compare scripts a false
positive (or false negative?) won't arise.

Regards,
  Peter Evans

Hi Peter

"Peter Evans" <pgevans@melbpc.org.au> wrote in message
news:95A38765-9FD2-4916-96CA-0B9466B71DDB@news.elevatesoft.com...
>
> EXECUTE IMMEDIATE 'ALTER TABLE OWNERS
>   DROP CONSTRAINT LandlordCode ...'
>

Unless I've misunderstood something, I can't drop the constraint - I
(perhaps mistakenly) believe that you can't have table without a PRIMARY
KEY.  If I can't drop it I can't recreate it.

> You could spend a whole lotta time coding these changes.

Not worried about lots of coding as the migration has been otherwise
successful and although I have 50+ databases to migrate, they all have the
same schema.

It's late here now so I'll have to have another look in the morning - I'll
play around with dropping the primary key ...

Thanks

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Peter Evans" <pgevans@melbpc.org.au> wrote in message
news:652A6C6E-071A-4AE5-85E1-8A000167BF4D@news.elevatesoft.com...
> On 15/08/2013 6:13 PM, Peter Evans wrote:
>
>>
>> However there is an easier way. Please see my post "How to generate a
>> script for altering objects in a database?"
>
> OK, here is a partial solution, I hope. I say hope as I won't be able to
> test this until tomorrow.
>
> Here is the broad outline :-
>

Thanks for the effort you've put into answering my post - appreciated but
....

I'm just writing a "quick and dirty" so the customer support guy can migrate
50 odd databases without getting too technical. I have a simple UI, (Select
the DBISAM data folder, enter a databasename, press [Start] button).

All the databases have the same schema and apart from fixing this table with
the dodgy primary key all is ready to go ...

.... except for the minor matter of fixing the code in 100+ modules, but that
is another story

Hopefully others can gain from your experience.

Bright and early tomorrow I'll take another look at the d*mn thing

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


Quick answer: example

ALTER TABLE "Adam"
ALTER CONSTRAINT "PK" AS PRIMARY KEY ("Test2","Test4","Test3","Society")

Teaching to fish answer:

What I do (and just did) is to carry out the activity, whatever it is, in EDBManager and look in Explorer | SQL History

This will tell you what you need


Roy Lambert [Team Elevate]

Hi Peter and Roy

After pondering overnight I went back to my script and spotted an error - I had statement that just said:-

EXECUTE IMMEDIATE 'ALTER TABLE Owners ';

... this was straight after the renaming of the primary key field.  Take out that line and the renaming worked and the primary key changed too.

Very weird ... wish I could deleted this thread and take an early weekend  

Thanks to you both for your assistance.

Cheers

Jeff

Roy Lambert wrote:

> What I do (and just did) is to carry out the activity, whatever it is, in EDBManager and look in Explorer | SQL History

What a great tip, Roy!  I didn't know about the SQL History - that is going to be so useful in the future.

Cheers

Jeff

Jeff,

<< BUT LandlordCode was the Primary Key of the Landlords Table (now Owners
Table) and when I look at the Primary Key of the Owners Table with EDBMgr, I
see that the (now non-existent) LandlordCode column is still the primary
Key. >>

When you manually execute SQL via the SQL/Script windows, any DDL changes
will not automatically be reflected in the current SQL metadata windows or
the treeview.  Just hit F5 to refresh them both.

Tim Young
Elevate Software
www.elevatesoft.com