Board index » delphi » Alter Table

Alter Table

The SQL documentation implies that it is possible to alter a column within a
table.  I've done that much (changed a column name), but it also implies
that it is possible to alter the column type.

Syntax ALTER TABLE table <operation> [, <operation> ];

<operation> = {ADD <col_def>
 | ADD <tconstraint>
 | ALTER [COLUMN] column_name <alt_col_clause>
 | DROP col
 | DROP CONSTRAINT constraint}

<alt_col_clause> = {TO new_col_name
 | TYPE new_col_datatype
 | POSITION new_col_position}

I can't get this to work.  Any inclusion of type information is rejected.
(Token unknown  ... )  Has anyone succeeded in changing a varchar to an
integer?  (The field in question is null at the moment.)

Thanks,

Christopher Weaver

 

Re:Alter Table


Hi Christopher,

You cannot rename an existing varChar column to a integer with :
ALTER TABLE testing ALTER COLUMN id int

If you're sure all values in the column are NULL or not important.
Than you can drop the column an add again with the good type !

First:
ALTER TABLE <tablename>
DROP <fieldname>

Second:
ALTER TABLE <tablename>
ADD <fieldname> INT

Greetz,
Arno.

Quote
Christopher Weaver wrote:
>The SQL documentation implies that it is possible to alter a column within a
>table.  I've done that much (changed a column name), but it also implies
>that it is possible to alter the column type.

>Syntax ALTER TABLE table <operation> [, <operation> ...];

><operation> = {ADD <col_def>
> | ADD <tconstraint>
> | ALTER [COLUMN] column_name <alt_col_clause>
> | DROP col
> | DROP CONSTRAINT constraint}

><alt_col_clause> = {TO new_col_name
> | TYPE new_col_datatype
> | POSITION new_col_position}

>I can't get this to work.  Any inclusion of type information is rejected.
>(Token unknown  ... )  Has anyone succeeded in changing a varchar to an
>integer?  (The field in question is null at the moment.)

>Thanks,

>Christopher Weaver

Re:Alter Table


Thanks Arno.

But am I missing something here?  The help text captioned below in my
original post seems to indicate that changing the datatype is provided for.
(I'll have to admit I thought it amazing because I've not seen it in other
engines.)  Perhaps I've just missread it.   Anyone with insight in that
regard is welcome to jump in here.

Anyway, I'll chalk this up to ambiguous docs and remove and replace the
field.

Christopher.

Quote
>Hi Christopher,

>You cannot rename an existing varChar column to a integer with :
>ALTER TABLE testing ALTER COLUMN id int

>If you're sure all values in the column are NULL or not important.
>Than you can drop the column an add again with the good type !

>First:
>ALTER TABLE <tablename>
>DROP <fieldname>

>Second:
>ALTER TABLE <tablename>
>ADD <fieldname> INT

>Greetz,
>Arno.

>Christopher Weaver wrote:

>>The SQL documentation implies that it is possible to alter a column within
a
>>table.  I've done that much (changed a column name), but it also implies
>>that it is possible to alter the column type.

>>Syntax ALTER TABLE table <operation> [, <operation> ...];

>><operation> = {ADD <col_def>
>> | ADD <tconstraint>
>> | ALTER [COLUMN] column_name <alt_col_clause>
>> | DROP col
>> | DROP CONSTRAINT constraint}

>><alt_col_clause> = {TO new_col_name
>> | TYPE new_col_datatype
>> | POSITION new_col_position}

>>I can't get this to work.  Any inclusion of type information is rejected.
>>(Token unknown  ... )  Has anyone succeeded in changing a varchar to an
>>integer?  (The field in question is null at the moment.)

>>Thanks,

>>Christopher Weaver

Re:Alter Table


Quote
"Christopher Weaver" <we.a...@verizon.net> wrote in message

news:3c06abbf$1_2@dnews...

Quote

> But am I missing something here?  The help text captioned below in my
> original post seems to indicate that changing the datatype is provided
for.
> (I'll have to admit I thought it amazing because I've not seen it in other
> engines.)  Perhaps I've just missread it.   Anyone with insight in that
> regard is welcome to jump in here.

Yes you can change the column type, but there are limits - IB has to do
something with the data in that column. The purpose of the feature is, where
possible, change types without destroying data. If the data cannot be
changed to the new type then IB cannot allow it.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

Re:Alter Table


Basically, there are two methods how to change datatype
-by ALTER TABLE command (official method)
-by direct updating system tables

ALTER TABLE checks whether old and new types are assignment compatible.
E.g. you can change from VARCHAR(10) to VARCHAR(20),
but you can't from VARCHAR(20) to VARCHAR(10), because rows with longer string
would became unreadable.

With direct update of system tables there is no such check,
by you do it on your own risk; e.g. change from DATE to INTEGER
on non-empty column will cause you problems.

--
Ivan
http://www.volny.cz/iprenosil/interbase

Re:Alter Table


Quote
>-by direct updating system tables

OK!  So how do I find these system tables?  I've looked through all the
tables within my database within IBConsole I find none of the fields that
I've created.

My need is not so urgent anymore.  So if you're busy with something else, no
worries.  I went ahead and did it the old fashioned way -- I removed and
replaced the field.  Would like to know how to do it through the system
tables though.

Christopher Weaver

Re:Alter Table


The system tables are described in detail in the back of the Launguage
Guide.

--
Bill
(TeamB cannot answer questions received via email)

Re:Alter Table


Quote
> >-by direct updating system tables

> OK!  So how do I find these system tables?  I've looked through all the
> tables within my database within IBConsole I find none of the fields that
> I've created.

To show system tables in IBConsole, in menu use
  View / System Data

Quote
> My need is not so urgent anymore.  So if you're busy with something else, no
> worries.  I went ahead and did it the old fashioned way -- I removed and
> replaced the field.  Would like to know how to do it through the system
> tables though.

Look at tabls RDB$RELATION_FIELDS and RDB$FIELDS.

--
Ivan
http://www.volny.cz/iprenosil/interbase

Re:Alter Table


Quote
>To show system tables in IBConsole, in menu use
>  View / System Data

Thanks Ivan.  I've been wondering how to get rid of all those tables with
the weird names.

Quote
>Look at tabls RDB$RELATION_FIELDS and RDB$FIELDS.

Bingo!  Just what I needed.

Now how about stored procs.  Does this product do any of its business
through stored procedures that we can look at the way MS SQL Server does?

Christopher Weaver

Re:Alter Table


Quote
>The system tables are described in detail in the back of the Launguage
>Guide.

Thank you very much!

Hey wait a minute.  That's not on line!  That's a book!
        Oh well...  I can handle it.

Christopher Weaver

Re:Alter Table


Sure its on-line. You can download the entire manual set in PDF format from
www.borland.com/techpubs/interbase.

--
Bill
(TeamB cannot answer questions received via email)

Other Threads