howto modify database structure ?

[Alvin chew]

hi, does anyone have idea to modify structure od existing database, say example, i have a table in mysql, and this table contains 5 fields/columns now, we now allow user to add existing fields/columns in the existing table, can we do that ? thank you !

[Adeel Ansari]

i think you mean allow user to add new fields/columns in existing table. yeah we can.
[ September 28, 2004: Message edited by: adeel ansari ]

[Adeel Ansari]

make two tables for example,

USER_DEFINED_COLUMN
COLUMN_ID, TABLE_ID, STATUS_ID, COLUMN_NAME, COLUMN_LENGTH, LABLE, DATATYPE

DYNAMIC_TABLE
TABLE_ID, TABLE_NAME

now you can manipulate the new fields with the help of these tables. you can:

delete added columns
modify added columns
add new columns

these table just keep the record of the added columns. the real columns are gonna add in the real table.

if you need some code i think i can provide you some.

[Alvin chew]

adeel, kindly please provide me some sample code on it if possible , thank you very much for your help !!

i'm sorry since i'm not clear on your idea, do you mind to specify it ? thank you again !!

[Adeel Ansari]

Ok this is my DAO. I hope you would be able to get logic atleast.

method for adding fields in the existing table

public void addColumn(ColumnBean bean, String sessionId) throws DBException{
Connection conn = DbUtil.getConnection();
PreparedStatement pStmt = null;
Statement stmt = null;

ResultSet rs = null;

String tableName = "USER_DEFINED_COLUMN";

String query = "insert into "+tableName+" (COLUMN_ID, TABLE_ID, STATUS_ID," +
" COLUMN_NAME, COLUMN_LENGTH, LABLE, DATATYPE) values(?, ?, ?, ?," +
" ?, ?, ?)";

String alterQry = "alter table "+getTableName(bean.getTableId())+
" add "+bean.getColumnName()+" "+bean.getDatatype()+"("+bean.getLength()+")";

String columnId = "";

try{
columnId = Util.generatePKey();

pStmt = conn.prepareStatement(query); 
pStmt.clearParameters();

pStmt.setString(1, columnId);
pStmt.setString(2, bean.getTableId());
pStmt.setString(3, bean.getStatusId());
pStmt.setString(4, bean.getColumnName());
pStmt.setString(5, bean.getLength());
pStmt.setString(6, bean.getLable());
pStmt.setString(7, bean.getDatatype());
 
rs = pStmt.executeQuery();

stmt = conn.createStatement();
stmt.execute(alterQry);

conn = DbUtil.closeConnection(conn);
pStmt.close();

}catch(SQLException e){
e.printStackTrace();
throw new DBException(e.getMessage());
}catch(Exception e){
e.printStackTrace();
throw new DBException(e.getMessage());
}

}
 

method for deleting fields (previously added) in the existing table

public void deleteColumns(String[] ids, String sessionId) throws DBException{

Connection conn = DbUtil.getConnection();
PreparedStatement pStmt = null;
Statement stmt = null;
ResultSet rs = null;
ColumnBean bean = null;
String tableName = "USER_DEFINED_COLUMN";

String delQuery = "delete from "+tableName+" where COLUMN_ID = ?";

String alterQry = null;
 

try{
 
pStmt = conn.prepareStatement(delQuery); 

for (int i=0; i<ids.length; i++){
 
pStmt.clearParameters();
pStmt.setString(1, ids[i]);

bean = getColumnById(ids[i]);

alterQry = "alter table "+getTableName(bean.getTableId())+
" drop column "+bean.getColumnName();


rs = pStmt.executeQuery();

stmt = conn.createStatement();
stmt.execute(alterQry);
 
 
}

conn = DbUtil.closeConnection(conn);
pStmt.close();


}catch(SQLException e){
throw new DBException(e.getMessage());
}

}
 

method for modifying fields (previously added) in the existing table

public void editColumn(ColumnBean bean, String sessionId) throws DBException{

Connection conn = DbUtil.getConnection();
PreparedStatement pStmt = null;
ResultSet rs = null;
Statement stmt = null;

String tableName = "USER_DEFINED_COLUMN";

String query = "update "+tableName+" set TABLE_ID=?, STATUS_ID=?," +
" COLUMN_NAME=?, COLUMN_LENGTH=?, LABLE=?, DATATYPE=? where" +
" COLUMN_ID=?";
 
String alterQry = "alter table "+getTableName(bean.getTableId())+
" modify "+bean.getColumnName()+" "+bean.getDatatype()+"("+bean.getLength()+")";

try{

pStmt = conn.prepareStatement(query); 
pStmt.clearParameters();

pStmt.setString(1, bean.getTableId());
pStmt.setString(2, bean.getStatusId());
pStmt.setString(3, bean.getColumnName());
pStmt.setString(4, bean.getLength());
pStmt.setString(5, bean.getLable());
pStmt.setString(6, bean.getDatatype());
pStmt.setString(7, bean.getColumnId());
 
rs = pStmt.executeQuery();

stmt = conn.createStatement();
stmt.execute(alterQry);
 
conn = DbUtil.closeConnection(conn);
pStmt.close();

}catch(SQLException e){
e.printStackTrace();
throw new DBException(e.getMessage());
}catch(Exception e){
e.printStackTrace();
throw new DBException(e.getMessage());
}
 
}
 

[ September 28, 2004: Message edited by: adeel ansari ]

[Alvin chew]

adeel, thanks for helping !!!

[Alvin chew]

adeel, any reason why you put "?" in your query string ?

[Adeel Ansari]

we should use ? when using prepared statement. afterwards by doing

pStmt.setString(1, "example");

the first ? of your string gets changed by the value. in this case ? would be changed into "example".

- it will allow you to set the value on the fly
- it will save you from sql injection
- easy in writing. dont really have to bother with string concatenations
- prepared statements are precompiled unlike statement

[Alvin chew]

thanks for answering , adeel !!