This example demonstrates how to change table definitions. In this scenario, an Invoice table is modified to expand a column length from 50 characters to 100 characters.
package ianywhere.ultralitej.demo; import ianywhere.ultralitej.*; /** Reorganize the Invoice table to have a name with an increased size * * <p>This shows a possible strategy which can be used to reorganize * tables, since UltraLiteJ has no table-altering API. * <p>The (contrived) example expands the name column to 100 characters. * */ public class Reorg { /** * mainline for program. * * @param args command-line arguments * */ public static void main ( String[] args ) { try { Configuration config = DatabaseManager.createConfigurationFile( "Sales.ulj" ); Connection conn = DatabaseManager.connect( config ); createNewInvoiceTable( conn ); copyInvoicesToNewTable( conn ); deleteOldInvoicesTable( conn ); renameNewInvoicesTable( conn ); enableSynchronizationForNewTable( conn ); conn.release(); } catch( ULjException exc ) { Demo.displayExceptionFull( exc ); } } private static void createNewInvoiceTable( Connection conn ) throws ULjException { conn.schemaCreateBegin(); TableSchema table_schema = conn.createTable( "NewInvoice" ); table_schema.createColumn( "inv_no", Domain.INTEGER ); table_schema.createColumn( "name", Domain.VARCHAR, 100 ); // was 50 in old table table_schema.createColumn( "date", Domain.DATE ); IndexSchema index_schema = table_schema.createPrimaryIndex( "prime_keys" ); index_schema.addColumn( "inv_no", IndexSchema.ASCENDING ); table_schema.setNoSync( true ); // we don't want to sync inserts yet conn.schemaCreateComplete(); } private static void copyInvoicesToNewTable( Connection conn ) throws ULjException { PreparedStatement inserter = conn.prepareStatement( "INSERT INTO NewInvoice( inv_no, name, \"date\" ) VALUES( ?, ?, ? )" ); int ordinal_inv_no = 1; int ordinal_inv_name = 2; int ordinal_inv_date = 3; PreparedStatement stmt = conn.prepareStatement( "SELECT inv_no, name, \"date\" FROM Invoice" ); ResultSet cursor = stmt.executeQuery(); for( ; cursor.next(); ) { inserter.set( ordinal_inv_no, cursor.getInt( ordinal_inv_no ) ); inserter.set( ordinal_inv_name, cursor.getString( ordinal_inv_name ) ); inserter.set( ordinal_inv_date, cursor.getString( ordinal_inv_date ) ); inserter.execute(); // in memory-low conditions, we could delete the row from the old table (specify // stopSynchronizationDelete, so these deletes would not synchronize. } inserter.close(); cursor.close(); stmt.close conn.commit(); } private static void deleteOldInvoicesTable( Connection conn ) throws ULjException { conn.dropTable( "Invoice" ); } private static void renameNewInvoicesTable( Connection conn ) throws ULjException { conn.renameTable( "NewInvoice", "Invoice" ); } private static void enableSynchronizationForNewTable( Connection conn ) throws ULjException { conn.enableSynchronization( "Invoice" ); } } |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |