Yes, very difficcult. And all this code is usable only if upgrade software
has all the changes hardcoded.
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.
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 TRanasact-SQL.
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"
