Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #2121 > unrolled thread

VO2ADO Add Colum To Existing Table

Started byIan <ithomson@mikroaid.co.uk>
First post2011-10-01 09:19 -0700
Last post2011-10-01 20:39 -0500
Articles 5 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  VO2ADO Add Colum To Existing Table Ian <ithomson@mikroaid.co.uk> - 2011-10-01 09:19 -0700
    Re: VO2ADO Add Colum To Existing Table "Ginny Caughey" <ginny.online@hotmail.com> - 2011-10-01 15:49 -0400
      Re: VO2ADO Add Colum To Existing Table "Willie Moore" <williem@wmconsulting.com> - 2011-10-01 20:44 -0500
        Re: VO2ADO Add Colum To Existing Table Ian <ithomson@mikroaid.co.uk> - 2011-10-02 04:21 -0700
    Re: VO2ADO Add Colum To Existing Table "Willie Moore" <williem@wmconsulting.com> - 2011-10-01 20:39 -0500

#2121 — VO2ADO Add Colum To Existing Table

FromIan <ithomson@mikroaid.co.uk>
Date2011-10-01 09:19 -0700
SubjectVO2ADO Add Colum To Existing Table
Message-ID<a2259ad4-7b9f-443a-985c-5503010d71e6@dd6g2000vbb.googlegroups.com>
Hi,

I am trying to add a column to an existing sql server 2008 table, if
that column name does not already exist. But I am getting error
messages. The code I am using is as follows;

aSqlArray :={}
cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN
WHERE TABLE_NAME ;
= [Whstocks] AND COLUMN_NAME = [TOPVALUE])"
cNewSql +=" BEGIN"
cNewSql +=" ALTER TABLE WHSTOCKS"
cNewSql +=" ADD TOPVALUE [int] NULL"
cNewSql +=" END"
AAdd(aSqlArray,cNewSql)
lResult :=CreateSqlTables(aSqlArray,"",oDataConn)

The code between BEGIN and END works ok, the error is in the IF NOT
EXISTS line. Error reports problem next to keywork COLUMN. Could
someone show me some code that checks to see if a column exists in an
sql server table, and if not it adds the column to the table.

Regards,
Ian

[toc] | [next] | [standalone]


#2122

From"Ginny Caughey" <ginny.online@hotmail.com>
Date2011-10-01 15:49 -0400
Message-ID<4e876e00$0$32167$c3e8da3$40d4fd75@news.astraweb.com>
In reply to#2121
Here's a line from my production app:

s :=    "  if not exists(select * from information_schema.columns where 
column_name = 'CashAmt' and table_name = 'History')"+;
        "    alter table History add CashAmt decimal(12,2)  NOT NULL 
DEFAULT(0) "
gConn:Execute(s)

Ginny

"Ian"  wrote in message 
news:a2259ad4-7b9f-443a-985c-5503010d71e6@dd6g2000vbb.googlegroups.com...

Hi,

I am trying to add a column to an existing sql server 2008 table, if
that column name does not already exist. But I am getting error
messages. The code I am using is as follows;

aSqlArray :={}
cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN
WHERE TABLE_NAME ;
= [Whstocks] AND COLUMN_NAME = [TOPVALUE])"
cNewSql +=" BEGIN"
cNewSql +=" ALTER TABLE WHSTOCKS"
cNewSql +=" ADD TOPVALUE [int] NULL"
cNewSql +=" END"
AAdd(aSqlArray,cNewSql)
lResult :=CreateSqlTables(aSqlArray,"",oDataConn)

The code between BEGIN and END works ok, the error is in the IF NOT
EXISTS line. Error reports problem next to keywork COLUMN. Could
someone show me some code that checks to see if a column exists in an
sql server table, and if not it adds the column to the table.

Regards,
Ian 

[toc] | [prev] | [next] | [standalone]


#2124

From"Willie Moore" <williem@wmconsulting.com>
Date2011-10-01 20:44 -0500
Message-ID<j68fl2$4cp$1@speranza.aioe.org>
In reply to#2122
Ginny,

I use almost the same exact code in my apps to handle changes in the 
structure. I don’t trust most system admins to properly run my update 
scripts.

Regards,
Willie

"Ginny Caughey"  wrote in message 
news:4e876e00$0$32167$c3e8da3$40d4fd75@news.astraweb.com...

Here's a line from my production app:

s :=    "  if not exists(select * from information_schema.columns where
column_name = 'CashAmt' and table_name = 'History')"+;
        "    alter table History add CashAmt decimal(12,2)  NOT NULL
DEFAULT(0) "
gConn:Execute(s)

Ginny

"Ian"  wrote in message
news:a2259ad4-7b9f-443a-985c-5503010d71e6@dd6g2000vbb.googlegroups.com...

Hi,

I am trying to add a column to an existing sql server 2008 table, if
that column name does not already exist. But I am getting error
messages. The code I am using is as follows;

aSqlArray :={}
cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN
WHERE TABLE_NAME ;
= [Whstocks] AND COLUMN_NAME = [TOPVALUE])"
cNewSql +=" BEGIN"
cNewSql +=" ALTER TABLE WHSTOCKS"
cNewSql +=" ADD TOPVALUE [int] NULL"
cNewSql +=" END"
AAdd(aSqlArray,cNewSql)
lResult :=CreateSqlTables(aSqlArray,"",oDataConn)

The code between BEGIN and END works ok, the error is in the IF NOT
EXISTS line. Error reports problem next to keywork COLUMN. Could
someone show me some code that checks to see if a column exists in an
sql server table, and if not it adds the column to the table.

Regards,
Ian 

[toc] | [prev] | [next] | [standalone]


#2125

FromIan <ithomson@mikroaid.co.uk>
Date2011-10-02 04:21 -0700
Message-ID<595a33d0-a91a-420d-a67e-4337680b0a6c@u13g2000vbx.googlegroups.com>
In reply to#2124
On Oct 2, 2:44 am, "Willie Moore" <will...@wmconsulting.com> wrote:
> Ginny,
>
> I use almost the same exact code in my apps to handle changes in the
> structure. I don’t trust most system admins to properly run my update
> scripts.
>
> Regards,
> Willie
>
> "Ginny Caughey"  wrote in message
>
> news:4e876e00$0$32167$c3e8da3$40d4fd75@news.astraweb.com...
>
> Here's a line from my production app:
>
> s :=    "  if not exists(select * from information_schema.columns where
> column_name = 'CashAmt' and table_name = 'History')"+;
>         "    alter table History add CashAmt decimal(12,2)  NOT NULL
> DEFAULT(0) "
> gConn:Execute(s)
>
> Ginny
>
> "Ian"  wrote in message
>
> news:a2259ad4-7b9f-443a-985c-5503010d71e6@dd6g2000vbb.googlegroups.com...
>
> Hi,
>
> I am trying to add a column to an existing sql server 2008 table, if
> that column name does not already exist. But I am getting error
> messages. The code I am using is as follows;
>
> aSqlArray :={}
> cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN
> WHERE TABLE_NAME ;
> = [Whstocks] AND COLUMN_NAME = [TOPVALUE])"
> cNewSql +=" BEGIN"
> cNewSql +=" ALTER TABLE WHSTOCKS"
> cNewSql +=" ADD TOPVALUE [int] NULL"
> cNewSql +=" END"
> AAdd(aSqlArray,cNewSql)
> lResult :=CreateSqlTables(aSqlArray,"",oDataConn)
>
> The code between BEGIN and END works ok, the error is in the IF NOT
> EXISTS line. Error reports problem next to keywork COLUMN. Could
> someone show me some code that checks to see if a column exists in an
> sql server table, and if not it adds the column to the table.
>
> Regards,
> Ian

Hi Willie & Ginny,

Sorted It, thanks. I forgot to put an S at the end of statement FROM
INFORMATION_SCHEMA.COLUMN. Changed this to
FROM INFORMATION_SCHEMA.COLUMNS and it worked fine. Maybe getting to
old for this as I'm missing the obvious.

Thanks for your help.

Ian


[toc] | [prev] | [next] | [standalone]


#2123

From"Willie Moore" <williem@wmconsulting.com>
Date2011-10-01 20:39 -0500
Message-ID<j68fc9$3pf$1@speranza.aioe.org>
In reply to#2121
Ian,

When you are putting multible TSQL commands you need to separate the 
different lines by a ";". My SQL classes maintain the Connection 
(self:oConn).

cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN WHERE 
TABLE_NAME= [Whstocks] AND COLUMN_NAME = TOPVALUE])"
cNewSql +=" BEGIN;"
cNewSql +=" ALTER TABLE WHSTOCKS"
cNewSql +=" ADD TOPVALUE [int] NULL"
cNewSql +=" END;"
SELF:oConn:Execute(cNewSQL)

Regards,
Willie

"Ian"  wrote in message 
news:a2259ad4-7b9f-443a-985c-5503010d71e6@dd6g2000vbb.googlegroups.com...

Hi,

I am trying to add a column to an existing sql server 2008 table, if
that column name does not already exist. But I am getting error
messages. The code I am using is as follows;

aSqlArray :={}
cNewSql :=" if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMN
WHERE TABLE_NAME ;
= [Whstocks] AND COLUMN_NAME = [TOPVALUE])"
cNewSql +=" BEGIN"
cNewSql +=" ALTER TABLE WHSTOCKS"
cNewSql +=" ADD TOPVALUE [int] NULL"
cNewSql +=" END"
AAdd(aSqlArray,cNewSql)
lResult :=CreateSqlTables(aSqlArray,"",oDataConn)

The code between BEGIN and END works ok, the error is in the IF NOT
EXISTS line. Error reports problem next to keywork COLUMN. Could
someone show me some code that checks to see if a column exists in an
sql server table, and if not it adds the column to the table.

Regards,
Ian 

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web