File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Can we add a Field(column) in a table dynamically? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can we add a Field(column) in a table dynamically?" Watch "Can we add a Field(column) in a table dynamically?" New topic
Author

Can we add a Field(column) in a table dynamically?

vaibhav punekar
Ranch Hand

Joined: Jan 20, 2001
Posts: 134
Hello,
I have uptil now updated table by adding rows dynamically.I would like to add a field name(ie new column)dynamically.
The scenario is like this.
I have a administrator account who should be able to edit the table by adding new column.We have no. of premises and each premise has its own database.We wish to transfer database from one premises to other as well as admin should be able to add new premise altogether.Thanks in advance.How can I achieve this?


VAIBHAV <BR>SCJP
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Its not advisable to have users adding columns
That said....
alter table add colname integer
Dan
Anil Vupputuri
Ranch Hand

Joined: Oct 31, 2000
Posts: 527
Yeah there is only one option to add column is using above stmt. alter table [table_name] add [column_name] [data_type].


SCJP 1.5, SCEA, ICED (287,484,486)
vaibhav punekar
Ranch Hand

Joined: Jan 20, 2001
Posts: 134
thanks a lot.I thought there would be some other way.is there any really?
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by vaibhav punekar:
We wish to transfer database from one premises to other as well as admin should be able to add new premise altogether. [...] How can I achieve this?

The syntax is
  Oracle: ALTER TABLE table ADD (column datatype)
  SQL Server: ALTER TABLE table ADD column datatype
  Others: something else again
But having said that... re-think your database design!!! It is bad practice to design a schema in such a way that you have to modify it on a regular basis. If you have a variable number of premises, model them as such in your schema.
This is actually a general point: if you have multiple copies of something, say a telephone number associated with a customer, do not be tempted to model it like

but always model it like

unless you are very, very sure that there absolutely never will be more than a certain number of copies. But in most cases, when you hold multiple copies of something, someone will ask you for fewer or more copies.
- Peter

[This message has been edited by Peter den Haan (edited June 14, 2001).]
vaibhav punekar
Ranch Hand

Joined: Jan 20, 2001
Posts: 134
Thanks Peter,
Do you mean that for each "ORDER" there would be unique "NUMBER"?
I got the point that the multiple record field should be separated.But then again in the second table same thing will arise that is updating it from the outside.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by vaibhav punekar:
Thanks Peter,
Do you mean that for each "ORDER" there would be unique "NUMBER"?

That name was not happily chosen. I was thinking of a specific ordering that telephone numbers for a customer might have: 1, 2, 3... The primary key is then the combination of the customer ID and the telephone order number.
I got the point that the multiple record field should be separated.But then again in the second table same thing will arise that is updating it from the outside.

I'm not sure I understand you. The point of the example was that the second schema would be able to accomodate more or fewer telephone numbers simply through DML (Data Manipulation Language) rather than DDL (Data Definition Language) statements. Similarly, it sounded like your database should really be able to accomodate new premises using DML only.
- Peter

[This message has been edited by Peter den Haan (edited June 14, 2001).]
ZhiningZhang
Greenhorn

Joined: Jun 13, 2001
Posts: 18
good point, Peter!
-Zhining
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can we add a Field(column) in a table dynamically?