Hi All-
I need to add constraints to a number of tables after they've been created by a Migrator.  Since the Migration process will be executed at least a few more times, I need to automate it.  I was able to fairly easily create a list of the tables needing a unique constraint on an autoinc index.  But I'm having a difficult time creating a functional script in Manager.

Here's a couple of queries that work fine in the Manager, executed individually:
Alter Table "AppInstance"
ADD CONSTRAINT "RecID" UNIQUE ("RecID")

Alter Table "AppliedPrepayItem"
ADD CONSTRAINT "RecID" UNIQUE ("RecID")

Creating a new script, and putting just one of them in the script results in this:
SCRIPT
BEGIN
Alter Table "AppliedPrepayItem"
ADD CONSTRAINT "RecID" UNIQUE ("RecID")
END

Copying from a Database generated  script, I converted it to this:
SCRIPT
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "CoCStockTrans"
(
ADD CONSTRAINT "ndxRecID" UNIQUE ("RecID")
)';
END

Neither works.  SCRIPT() also doesn't work.  Neither do any number of wild guesses I've made.  Can anyone point me in the right directions?  
The last example Prepares ok, but gives the following error:
ElevateDB Error #700 An error was found in the statement at line 5 and column 19 (Expected end of expression but instead found ()
The error points to the apostrophe before ALTER:    'ALTER

Any help will as always be appreciated.
Thanks,
Judd

Perhaps something like this?
{sql}
EXECUTE IMMEDIATE
'ALTER TABLE "CoCStockTrans"
ADD CONSTRAINT "ndxRecID" UNIQUE ("RecID")';
END
{sql}

jkr


Ian's suggestion should be right - if you add a constraint using the dialogs and then look at the sql from Explorer | SQL History in EDBManager - no brackets. I'm guessing you've put those in because they're there in the CREATE TABLE sql it shows.


If you want to make all the "autoinc" columns into primary key constraints its not to difficult using a mix of SQL & delphi.

1. create a query QRY, set the sql to

select * from information.tablecolumns where identity = true and generated = true and type = 'integer'

ElevateDB is catalog based and there's a table holding information on all the columns in all the tables. The query above will get all of those that have been set up as an "autoinc"

2. loop round the result set creating primary keys as you go

qry.First;
while not qry.Eof do begin
database.execute('ALTER TABLE "'+qry.FieldByName('TableName').AsString+'" ADD CONSTRAINT "qry.FieldByName('Name').AsString" PRIMARY KEY ("'+qry.FieldByName('Name').AsString + ")');
qry.next;
end;

That should do it. database is whatever database component you've used for the migration. If there isn't one set up a session & database pointing at the tables and use that.

If the routine isn't run clean each time add a

try
database.execute('ALTER TABLE "'+qry.FieldByName('TableName').AsString+'" DROP CONSTRAINT "qry.FieldByName('Name').AsString"');
except
end;

as the first part of the loop.


The above has been typed in blind with zero (or negative) testing so may be slightly or totally wrong - use at your own risk!

Roy Lambert

ps remember this

Explorer | SQL History in EDBManager


write on a post-it and stick to your monitor - it was one of my nost valuable learning tools.

Its also worth looking up

Information Schema
and
Configuration Database

in the OLH

Ian- That worked perfectly.  And is endlessly repeatable

Roy- "SQL History in EDBManage" is a godsend.  I thought I had stumbled on something like this, but gave up trying to find it..I won't need it here, at least right now, but I'll be using this regularly. And yes to the brackets messing me up.  

The table loop will be invaluable.  I've used information.tables, but I've just looked and experimented and there's a lot of utility there.  I thought I'd code something toward that effect, but wasn't sure how to proceed. For the script purpose, I wrote something to read in the reverse engineered script and list all tables and constraints.  I then focused on the ones without constraints.  With the basic script and the tables needing constraints I can just put in the lines I need.  Pretty simple, and if difficulties arise I can comment out the ones that are done.

Thanks to both of you.





If you want to make all the "autoinc" columns into primary key constraints its not to difficult using a mix of SQL & delphi.

1. create a query QRY, set the sql to

select * from information.tablecolumns where identity = true and generated = true and type = 'integer'

ElevateDB is catalog based and there's a table holding information on all the columns in all the tables. The query above will get all of those that have been set up as an "autoinc"

2. loop round the result set creating primary keys as you go

qry.First;
while not qry.Eof do begin
database.execute('ALTER TABLE "'+qry.FieldByName('TableName').AsString+'" ADD CONSTRAINT "qry.FieldByName('Name').AsString" PRIMARY KEY ("'+qry.FieldByName('Name').AsString + ")');
qry.next;
end;

That should do it. database is whatever database component you've used for the migration. If there isn't one set up a session & database pointing at the tables and use that.

If the routine isn't run clean each time add a

try
database.execute('ALTER TABLE "'+qry.FieldByName('TableName').AsString+'" DROP CONSTRAINT "qry.FieldByName('Name').AsString"');
except
end;

as the first part of the loop.


The above has been typed in blind with zero (or negative) testing so may be slightly or totally wrong - use at your own risk!

Roy Lambert

ps remember this

Explorer | SQL History in EDBManager


write on a post-it and stick to your monitor - it was one of my nost valuable learning tools.

Its also worth looking up

Information Schema
and
Configuration Database

in the OLH