Java Code Examples for org.pentaho.di.core.database.Database#getCreateTableStatement()

The following examples show how to use org.pentaho.di.core.database.Database#getCreateTableStatement() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: XulDatabaseExplorerController.java    From pentaho-kettle with Apache License 2.0 6 votes vote down vote up 
public void getDDL() {
  if ( model.getTable() == null ) {
    return;
  }
  Database db = new Database( null, this.model.getDatabaseMeta() );
  try {
    db.connect();
    String tableName = getSchemaAndTable( this.model );
    RowMetaInterface r = db.getTableFields( tableName );
    String sql = db.getCreateTableStatement( tableName, r, null, false, null, true );
    SQLEditor se =
      new SQLEditor( this.getDatabaseMeta(), this.dbExplorerDialog.getShell(), SWT.NONE, this.model
        .getDatabaseMeta(), this.dbcache, sql );
    se.open();
  } catch ( KettleDatabaseException dbe ) {
    new ErrorDialog(
      this.dbExplorerDialog.getShell(), BaseMessages.getString( PKG, "Dialog.Error.Header" ), BaseMessages
        .getString( PKG, "DatabaseExplorerDialog.Error.RetrieveLayout" ), dbe );
  } finally {
    db.disconnect();
  }
}
Example 2
Source File: DataSetDatabaseGroup.java    From pentaho-pdi-dataset with Apache License 2.0 5 votes vote down vote up 
public static final void writeDataSetData( LoggingObjectInterface loggingObject, DataSetGroup dataSetGroup, String tableName,
                                           RowMetaInterface rowMeta, List<Object[]> rows ) throws KettleException {

  Database database = new Database( loggingObject, dataSetGroup.getDatabaseMeta() );
  try {
    database.connect();

    String schemaTable = dataSetGroup.getDatabaseMeta().getQuotedSchemaTableCombination( dataSetGroup.getSchemaName(), tableName );

    String sql;
    if ( database.checkTableExists( schemaTable ) ) {
      // Clean out old junk, allow for rollback
      //
      database.truncateTable( schemaTable );
      sql = database.getAlterTableStatement( schemaTable, rowMeta, null, false, null, true );
    } else {
      sql = database.getCreateTableStatement( schemaTable, rowMeta, null, false, null, true );
    }
    if ( !StringUtil.isEmpty( sql ) ) {
      database.execStatements( sql );
    }
    database.prepareInsert( rowMeta, schemaTable );
    for ( Object[] row : rows ) {
      database.setValuesInsert( new RowMetaAndData( rowMeta, row ) );
      database.insertRow();
    }

    database.commit();
  } finally {
    database.disconnect();
  }

}
Example 3
Source File: ExecSQLRowIT.java    From pentaho-kettle with Apache License 2.0 5 votes vote down vote up 
/**
 * Create source table.
 */
public static void createTables( Database db ) throws Exception {
  String source =
    db.getCreateTableStatement( execsqlrow_testtable, createSourceRowMetaInterface(), null, false, null, true );
  try {
    db.execStatement( source );
  } catch ( KettleException ex ) {
    fail( "failure while creating table " + execsqlrow_testtable + ": " + ex.getMessage() );
  }
}
Example 4
Source File: DatabaseLookupIT.java    From pentaho-kettle with Apache License 2.0 5 votes vote down vote up 
/**
 * Create source table.
 */
public static void createTables( Database db ) throws Exception {
  String source =
    db.getCreateTableStatement( lookup_table, createSourceRowMetaInterface(), null, false, null, true );
  try {
    db.execStatement( source );
  } catch ( KettleException ex ) {
    fail( "failure while creating table " + lookup_table + ": " + ex.getMessage() );
  }
}
Example 5
Source File: TableOutputIT.java    From pentaho-kettle with Apache License 2.0 5 votes vote down vote up 
/**
 * Create table for the normal case.
 */
public void createTable( Database db, String tableName, RowMetaInterface rm ) throws Exception {
  String source = db.getCreateTableStatement( tableName, rm, null, false, null, true );
  try {
    db.execStatement( source );
  } catch ( KettleException ex ) {
    fail( "failure while creating table " + tableName + ": " + ex.getMessage() );
  }
}
Example 6
Source File: TableInputIT.java    From pentaho-kettle with Apache License 2.0 5 votes vote down vote up 
/**
 * Create source table.
 */
public void createTables( Database db ) throws Exception {
  String source =
    db.getCreateTableStatement( source_table, createSourceRowMetaInterface(), null, false, null, true );
  try {
    db.execStatement( source );
  } catch ( KettleException ex ) {
    fail( "failure while creating table " + source_table + ": " + ex.getMessage() );
  }
}
Example 7
Source File: InsertUpdateIT.java    From pentaho-kettle with Apache License 2.0 4 votes vote down vote up 
@Override
@Before
public void setUp() throws Exception {

  KettleEnvironment.init();

  /* SET UP TRANSFORMATION */

  // Create a new transformation...
  TransMeta transMeta = new TransMeta();
  transMeta.setName( "insert/update test" );

  // Add the database connections
  for ( int i = 0; i < databasesXML.length; i++ ) {
    DatabaseMeta databaseMeta = new DatabaseMeta( databasesXML[i] );
    transMeta.addDatabase( databaseMeta );
  }

  DatabaseMeta dbInfo = transMeta.findDatabase( "db" );

  /* SET UP DATABASE */
  // Create target table
  db = new Database( transMeta, dbInfo );
  db.connect();

  String source = db.getCreateTableStatement( TARGET_TABLE, getTargetTableRowMeta(), null, false, null, true );
  db.execStatement( source );

  // populate target table
  for ( String sql : insertStatement ) {
    db.execStatement( sql );
  }

  /* SET UP TRANSFORMATION STEPS */

  PluginRegistry registry = PluginRegistry.getInstance();

  // create an injector step...
  String injectorStepName = "injector step";
  InjectorMeta im = new InjectorMeta();

  // Set the information of the injector.
  String injectorPid = registry.getPluginId( StepPluginType.class, im );
  StepMeta injectorStep = new StepMeta( injectorPid, injectorStepName, im );
  transMeta.addStep( injectorStep );

  // create the update step...
  String updateStepName = "insert/update [" + TARGET_TABLE + "]";
  insupd = new InsertUpdateMeta();
  insupd.setDatabaseMeta( transMeta.findDatabase( "db" ) );
  insupd.setTableName( TARGET_TABLE );

  insupd.setUpdateLookup( new String[] { "VALUE", "ROW_ORDER" } );
  insupd.setUpdateStream( new String[] { "VALUE", "ROW_ORDER" } );
  insupd.setUpdate( new Boolean[] { true, false } );

  String fromid = registry.getPluginId( StepPluginType.class, insupd );
  StepMeta updateStep = new StepMeta( fromid, updateStepName, insupd );
  updateStep.setDescription( "insert/update data in table [" + TARGET_TABLE + "] on database [" + dbInfo + "]" );
  transMeta.addStep( updateStep );

  TransHopMeta hi = new TransHopMeta( injectorStep, updateStep );
  transMeta.addTransHop( hi );

  /* PREPARE TRANSFORMATION EXECUTION */

  trans = new Trans( transMeta );
  trans.prepareExecution( null );

  StepInterface si = trans.getStepInterface( updateStepName, 0 );
  rc = new RowStepCollector();
  si.addRowListener( rc );

  rp = trans.addRowProducer( injectorStepName, 0 );

}
Example 8
Source File: UpdateIT.java    From pentaho-kettle with Apache License 2.0 4 votes vote down vote up 
@Override
@Before
public void setUp() throws Exception {

  KettleEnvironment.init();

  /* SET UP TRANSFORMATION */

  // Create a new transformation...
  TransMeta transMeta = new TransMeta();
  transMeta.setName( "update test" );

  // Add the database connections
  for ( int i = 0; i < databasesXML.length; i++ ) {
    DatabaseMeta databaseMeta = new DatabaseMeta( databasesXML[i] );
    transMeta.addDatabase( databaseMeta );
  }

  DatabaseMeta dbInfo = transMeta.findDatabase( "db" );

  /* SET UP DATABASE */
  // Create target table
  db = new Database( transMeta, dbInfo );
  db.connect();

  String source = db.getCreateTableStatement( TARGET_TABLE, getTargetTableRowMeta(), null, false, null, true );
  db.execStatement( source );

  // populate target table
  for ( String sql : insertStatement ) {
    db.execStatement( sql );
  }

  /* SET UP TRANSFORMATION STEPS */

  PluginRegistry registry = PluginRegistry.getInstance();

  // create an injector step...
  String injectorStepName = "injector step";
  InjectorMeta im = new InjectorMeta();

  // Set the information of the injector.
  String injectorPid = registry.getPluginId( StepPluginType.class, im );
  StepMeta injectorStep = new StepMeta( injectorPid, injectorStepName, im );
  transMeta.addStep( injectorStep );

  // create the update step...
  String updateStepName = "update [" + TARGET_TABLE + "]";
  upd = new UpdateMeta();
  upd.setDatabaseMeta( transMeta.findDatabase( "db" ) );
  upd.setTableName( TARGET_TABLE );
  upd.setUpdateLookup( new String[] { "VALUE" } );
  upd.setUpdateStream( new String[] { "VALUE" } );
  upd.setErrorIgnored( true );

  String fromid = registry.getPluginId( StepPluginType.class, upd );
  StepMeta updateStep = new StepMeta( fromid, updateStepName, upd );
  updateStep.setDescription( "update data in table [" + TARGET_TABLE + "] on database [" + dbInfo + "]" );
  transMeta.addStep( updateStep );

  TransHopMeta hi = new TransHopMeta( injectorStep, updateStep );
  transMeta.addTransHop( hi );

  /* PREPARE TRANSFORMATION EXECUTION */

  trans = new Trans( transMeta );
  trans.prepareExecution( null );

  StepInterface si = trans.getStepInterface( updateStepName, 0 );
  rc = new RowStepCollector();
  si.addRowListener( rc );

  rp = trans.addRowProducer( injectorStepName, 0 );

}
Example 9
Source File: XulDatabaseExplorerController.java    From pentaho-kettle with Apache License 2.0 4 votes vote down vote up 
public void getDDLForOther() {

    if ( databases != null ) {
      try {

        // Now select the other connection...

        // Only take non-SAP ERP connections....
        List<DatabaseMeta> dbs = new ArrayList<DatabaseMeta>();
        for ( int i = 0; i < databases.size(); i++ ) {
          if ( ( ( databases.get( i ) ).getDatabaseInterface().isExplorable() ) ) {
            dbs.add( databases.get( i ) );
          }
        }

        String[] conn = new String[dbs.size()];
        for ( int i = 0; i < conn.length; i++ ) {
          conn[i] = ( dbs.get( i ) ).getName();
        }

        EnterSelectionDialog esd = new EnterSelectionDialog( this.dbExplorerDialog.getShell(), conn,
          BaseMessages.getString( PKG, "DatabaseExplorerDialog.TargetDatabase.Title" ),
          BaseMessages.getString( PKG, "DatabaseExplorerDialog.TargetDatabase.Message" ) );
        String target = esd.open();
        if ( target != null ) {
          DatabaseMeta targetdbi = DatabaseMeta.findDatabase( dbs, target );
          Database targetdb = new Database( null, targetdbi );
          try {
            targetdb.connect();
            String tableName = getSchemaAndTable( model );
            RowMetaInterface r = targetdb.getTableFields( tableName );

            String sql = targetdb.getCreateTableStatement( tableName, r, null, false, null, true );
            SQLEditor se =
              new SQLEditor( this.getDatabaseMeta(), this.dbExplorerDialog.getShell(), SWT.NONE, this.model
                .getDatabaseMeta(), this.dbcache, sql );
            se.open();
          } finally {
            targetdb.disconnect();
          }
        }
      } catch ( KettleDatabaseException dbe ) {
        new ErrorDialog(
          this.dbExplorerDialog.getShell(), BaseMessages.getString( PKG, "Dialog.Error.Header" ), BaseMessages
            .getString( PKG, "DatabaseExplorerDialog.Error.GenDDL" ), dbe );
      }
    } else {
      MessageBox mb = new MessageBox( this.dbExplorerDialog.getShell(), SWT.NONE | SWT.ICON_INFORMATION );
      mb.setMessage( BaseMessages.getString( PKG, "DatabaseExplorerDialog.NoConnectionsKnown.Message" ) );
      mb.setText( BaseMessages.getString( PKG, "DatabaseExplorerDialog.NoConnectionsKnown.Title" ) );
      mb.open();
    }
  }