Neven
I just put down what I thought. The rule here I guess is - ignore it if you
do not like it.
Regards
Alex
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Thursday, 12 October 2000 11:55
Subject: Re: [DUG]: [Q] Table Structures....
> 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"
>
---------------------------------------------------------------------------
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"
