db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From robk99 <ROBEKL...@TX.RR.COM>
Subject Re: Adding a new column in an existing table using the API
Date Sat, 29 Sep 2007 21:59:51 GMT

Still having issues.  When executing alterTables after adding the column to
the table it is trying to recreate other existing tables in the target
database.

The following is code I wrote that compares columns of two tables that both
exist in two different databases, sourceDatabase (contains only 1 table) vs
targetDatabase (contains over 600 tables).  If a column does not exit in the
table of the target database table that exists in the source database table
then it is added to the table.

void updateTables(Database targetDatabase, Database sourceDatabase, String
table) throws Exception
	{
			Platform platform = PlatformFactory.createNewPlatformInstance("oracle");


			//			find table source table	in sourceDatabae				
			Table sourceTable = sourceDatabase.findTable(table,false);			
			int numCols = sourceTable.getColumnCount();						
			
			//			find table in targetDatabase
			Table targetTable = targetDatabase.findTable(table,false);

			//compare source to target (Looks for only additions does not delete
columns)
			for (int i=0; i<numCols; i++){
				Column sourceColumn =sourceTable.getColumn(i);
				Column targetColumn = targetTable.findColumn(sourceColumn.getName());
				if (targetColumn == null){
					System.out.println("adding column");					
					targetTable.addColumn(sourceColumn);					
					System.out.println("added column");
				}
			}
			
			platform.alterTables(conn,null,"schematoupdate",null,targetDatabase,
false);			
			conn.setAutoCommit(true);

}

Any ideas?
Thanks
Rob



Thomas Dudziak wrote:
> 
> On 9/23/07, robk99 <ROBEKLEIN@tx.rr.com> wrote:
> 
>> Is there a method using the API to modify an existing table by adding a
>> column to it without deleting the original table and then recreating the
>> table with the new column?
> 
> That's pretty much what DdlUtils is made for. It involves basically three
> steps:
> 
> * Read the database model from either the live database (via the
> readModelFromDatabase method in the Platform instance that you'll get
> from the PlatformFactory) or an XML file (via the DatabaseIO class).
> 
> * Change the mode which is basically a bunch of objects representing
> tables, columns, foreign keys etc. E.g. in your case, find the table
> in question and simply add a column to it.
> 
> * Write the database model to an XML file (again via the DatabaseIO
> class) or to the live database (using the alterTables method in the
> Platform).
> 
> You could also use a variations of the above to get SQL for the
> alteration that you then could execute manually.
> 
> Tom
> 
> 

-- 
View this message in context: http://www.nabble.com/Adding-a-new-column-in-an-existing-table-using-the-API-tf4505049.html#a12960193
Sent from the Apache DdlUtils - User mailing list archive at Nabble.com.


Mime
View raw message