Alex
> Yes, very difficcult. And all this code is usable only if upgrade software
> has all the changes hardcoded.
Huh? These procs can be embedded in the DB and can be driven by a script or
in my case the oginals are actually driven off a change request tables and a
continuous procedure
running in the server - To add or drop a col simply requires an entry in
these tables the server does the
rest
> Ideal solution would be to supply new DB and have an upgrade program to
> detect what is new, comparing to older version, and then apply all the
> changes.
All those people that coded the ALTER TABLE statement must have wondered why
they bothered
> I tend to think that the simplest way to go is by copying data between 2
DB.
>
> Eg.
> 1. Copy existing DB to temp one
> 2. Drop existing DB
> 3. Attach new DB in place of existing.
> 4. Pull all the data from temp to new (this includes checking on what
> columns no longer present, eg. dynamicly built SQL has to be used)
> 5. Drop temp
> 6. Truncate log
>
> This is simpler to do from within Delphi code than in Transact-SQL.
>
Your've got to be joking!
And more error prone Alex you are still in ISAM mode
> Regards
> Alex
>
> ----- Original Message -----
> From: "Neven MacEwan" <[EMAIL PROTECTED]>
> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> Sent: Thursday, 12 October 2000 10:27
> Subject: Re: [DUG]: [Q] Table Structures....
>
>
> > D
> >
> > I've done this for MS SQL (prob the same would port to other databases)
> > using stored procs
> > Firstly I tried to do it from a triggers on a change log file but when I
> > finished the code and tested it i got the error message
> > "Cannot execute DDL Statements inside trigger" - darn
> >
> > Add goes like
> >
> > CREATE PROC AddCol
> > @TableName varchar(80),
> > @ColumnName varchar(80),
> > @DataType varchar(80),
> > @DefaultValue varchar(80),
> > @ConstraintText varchar(255)
> > AS
> > BEGIN
> > DECLARE
> > @SQLOptions varchar(255)
> > SET @SQLOptions = ' '
> > IF (NOT @DefaultValue IS NULL) SET @SQLOptions = @SQLOptions+'
DEFAULT
> > '+@DefaultValue
> > IF (NOT @ConstraintText IS NULL) SET @SQLOptions = @SQLOptions+'
> > '+@ConstraintText
> > EXECUTE ( 'ALTER TABLE '+@TableName+' ADD ['+@ColumnName + ']
> > '+@DataType+@SQLOptions)
> > IF @@ERROR <> 0 RETURN 1
> > END
> > -- Sucess
> > RETURN 0
> > END
> >
> >
> > Dropping is a little more complex as you have to drop constraints first
> >
> > CREATE PROC spSMDropPropCol
> > @TableName sysname,
> > @ColName sysname,
> > AS
> > BEGIN
> > DECLARE
> > @DefaultValue sysname,
> > @ErrorText VARCHAR(255),
> > @ErrorCode VARCHAR(255),
> > @SQL VARCHAR(8000),
> > @ConName sysname,
> > @Result INT,
> > @CreateText sysname
> > SET @ErrorText = 'Done'
> > SET @ErrorCode = 0;
> >
> > -- Drop any constraints assoc with this column
> > DECLARE ConstraintCursor CURSOR
> > FOR SELECT ConObj.Name
> > FROM sysobjects ConObj JOIN
> > sysobjects TabObj ON ConObj.parent_obj = TabObj.ID JOIN
> > syscolumns Col ON Col.ID = ConObj.parent_obj and Col.ColID =
> > ConObj.Info
> > WHERE (ConObj.xtype = 'C' or ConObj.xtype = 'PK' or ConObj.xtype =
> > 'D')
> > and TabObj.Name = @TableName
> > OPEN ConstraintCursor
> > FETCH NEXT FROM ConstraintCursor
> > INTO @ConName
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > SET @SQL = 'ALTER TABLE '+@TableName+' DROP ['+@ConName+']'
> > EXEC(@SQL)
> > FETCH NEXT FROM ConstraintCursor
> > INTO @ConName
> > END
> > CLOSE ConstraintCursor
> > DEALLOCATE ConstraintCursor
> >
> > -- Make the alter table script for this column
> > SET @SQL = 'ALTER TABLE '+@TableName+' DROP COLUMN ['+@PropColName+']'
> > EXEC(@SQL)
> > END
> >
> >
> >
> > ----- Original Message -----
> > From: Donovan J. Edye <[EMAIL PROTECTED]>
> > To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> > Sent: Thursday, 12 October 2000 12:36
> > Subject: [DUG]: [Q] Table Structures....
> >
> >
> > > G'Day,
> > >
> > > Given a table definition that is subject to change, has anyone got any
> > code
> > > or advice on how the tables in the database could be dynamically
> > > restructured based on the table definition? All this would be done via
> ADO
> > > and should be back end independent. An example:
> > >
> > > - Initial Release
> > >
> > > CREATE TABLE tblMine
> > > (
> > > A VarChar(20)
> > > )
> > >
> > > - 6 mths down the track say the table may need to be
> > >
> > > CREATE TABLE tblMine
> > > (
> > > A VarChar(25),
> > > B SmallInt
> > > )
> > >
> > >
> >
> ------------------------------------------------------------------------
> > > --Donovan [[EMAIL PROTECTED]]
> > > Donovan J. Edye [www.edye.wattle.id.au]
> > > Namadgi Systems, Delphi Developer [www.namsys.com.au]
> > > Voice: +61 2 6285-3460 Fax: +61 2 6285-3459
> > > TVisualBasic = Class(None);
> > > Heard just before the 'Big Bang': "...Uh Oh...."
> >
> ------------------------------------------------------------------------
> > > GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
> > > replacement. Also includes freeware delphi windows explorer
components.
> >
> ------------------------------------------------------------------------
> > >
> >
>
> --------------------------------------------------------------------------
> > -
> > > New Zealand Delphi Users group - Delphi List -
[EMAIL PROTECTED]
> > > Website: http://www.delphi.org.nz
> > > To UnSub, send email to: [EMAIL PROTECTED]
> > > with body of "unsubscribe delphi"
> > >
> >
>
> --------------------------------------------------------------------------
> -
> > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> > Website: http://www.delphi.org.nz
> > To UnSub, send email to: [EMAIL PROTECTED]
> > with body of "unsubscribe delphi"
> >
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
