Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #2121 > unrolled thread
| Started by | Ian <ithomson@mikroaid.co.uk> |
|---|---|
| First post | 2011-10-01 09:19 -0700 |
| Last post | 2011-10-01 20:39 -0500 |
| Articles | 5 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | Ian <ithomson@mikroaid.co.uk> |
|---|---|
| Date | 2011-10-01 09:19 -0700 |
| Subject | VO2ADO 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]
| From | "Ginny Caughey" <ginny.online@hotmail.com> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | Ian <ithomson@mikroaid.co.uk> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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