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

/**
 * 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() );
  }
}
 

/**
 * 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() );
  }
}
 

/**
 * 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() );
  }
}
 

/**
 * Drop table
 */
public void dropTable( Database db, String tableName ) throws Exception {
  String source = "DROP TABLE " + tableName + ";";
  try {
    db.execStatement( source );
  } catch ( KettleException ex ) {
    fail( "failure while dropping table " + tableName + ": " + ex.getMessage() );
  }
}
 

/**
 * 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() );
  }
}
 

@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 );

}
 

@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 );

}
 

/**
 * This test retrieves next values from a slave sequence.<br>
 */
public void testSlaveSequenceRetrieval_Specifed() throws Exception {
  init();

  String SLAVE_SEQUENCE_NAME = "test";

  SlaveServerConfig slaveConfig = new SlaveServerConfig( "localhost", 8282, false );
  slaveConfig.getSlaveServer().setUsername( "cluster" );
  slaveConfig.getSlaveServer().setPassword( "cluster" );

  String dbDir =
    System.getProperty( "java.io.tmpdir" ) + "/" + UUID.randomUUID().toString() + "-slaveSeqTest-H2-DB";
  DatabaseMeta databaseMeta = new DatabaseMeta( "H2", "H2", "Native", null, dbDir, null, null, null );
  slaveConfig.getDatabases().add( databaseMeta );

  String table = "SLAVE_SEQUENCE";
  String nameField = "SEQ_NAME";
  String valueField = "SEQ_VALUE";

  SlaveSequence slaveSequence =
    new SlaveSequence( SLAVE_SEQUENCE_NAME, 1L, databaseMeta, null, table, nameField, valueField );
  slaveConfig.getSlaveSequences().add( slaveSequence );

  Database db = new Database( loggingObject, databaseMeta );
  db.connect();
  db.execStatement( "CREATE TABLE SLAVE_SEQUENCE(SEQ_NAME VARCHAR(100), SEQ_VALUE INTEGER);" );
  db.disconnect();

  // Start the Carte launcher
  CarteLauncher carteLauncher = new CarteLauncher( slaveConfig );
  Thread thread = new Thread( carteLauncher );
  thread.start();

  // Wait until the carte object is available...
  //
  while ( carteLauncher.getCarte() == null && !carteLauncher.isFailure() ) {
    Thread.sleep( 100 );
  }

  long value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( SLAVE_SEQUENCE_NAME, 1000 );
  assertEquals( 1L, value );
  value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( SLAVE_SEQUENCE_NAME, 1000 );
  assertEquals( 1001L, value );

  try {
    slaveConfig.getSlaveServer().getNextSlaveSequenceValue( "Unknown sequence", 1000 );
    fail( "No error was thrown for retrieval of an unknown sequence" );
  } catch ( Exception e ) {
    // OK!
  }

  // After the test, stop the server
  //
  carteLauncher.getCarte().getWebServer().stopServer();

  // Remove the database + table in the temporary directory
  //
  File dir = new File( dbDir );
  if ( dir.exists() ) {
    for ( File child : dir.listFiles() ) {
      if ( child.isFile() ) {
        child.delete();
      }
    }
    dir.delete();
  }
}
 

/**
 * This test retrieves next values from a slave sequence.<br>
 */
public void testSlaveSequenceRetrieval_AutoCreation() throws Exception {
  init();

  String SLAVE_SEQUENCE_NAME = "test";

  SlaveServerConfig slaveConfig = new SlaveServerConfig( "localhost", 8282, false );
  slaveConfig.getSlaveServer().setUsername( "cluster" );
  slaveConfig.getSlaveServer().setPassword( "cluster" );

  String dbDir =
    System.getProperty( "java.io.tmpdir" ) + "/" + UUID.randomUUID().toString() + "-slaveSeqTest-H2-DB";
  DatabaseMeta databaseMeta = new DatabaseMeta( "H2", "H2", "Native", null, dbDir, null, null, null );
  slaveConfig.getDatabases().add( databaseMeta );

  String table = "SLAVE_SEQUENCE";
  String nameField = "SEQ_NAME";
  String valueField = "SEQ_VALUE";

  SlaveSequence slaveSequence =
    new SlaveSequence( SLAVE_SEQUENCE_NAME, 1L, databaseMeta, null, table, nameField, valueField );
  slaveConfig.setAutomaticCreationAllowed( true );
  slaveConfig.setAutoSequence( slaveSequence );

  Database db = new Database( loggingObject, databaseMeta );
  db.connect();
  db.execStatement( "CREATE TABLE SLAVE_SEQUENCE(SEQ_NAME VARCHAR(100), SEQ_VALUE INTEGER);" );
  db.disconnect();

  // Start the Carte launcher
  CarteLauncher carteLauncher = new CarteLauncher( slaveConfig );
  Thread thread = new Thread( carteLauncher );
  thread.start();

  // Wait until the carte object is available...
  //
  while ( carteLauncher.getCarte() == null && !carteLauncher.isFailure() ) {
    Thread.sleep( 100 );
  }

  try {
    long value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( SLAVE_SEQUENCE_NAME, 1000 );
    assertEquals( 1L, value );
    value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( SLAVE_SEQUENCE_NAME, 1000 );
    assertEquals( 1001L, value );
    value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( "new sequence", 1000 );
    assertEquals( 1L, value );
    value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( "new sequence", 1000 );
    assertEquals( 1001L, value );
    value = slaveConfig.getSlaveServer().getNextSlaveSequenceValue( SLAVE_SEQUENCE_NAME, 1000 );
    assertEquals( 2001L, value );
  } catch ( Exception e ) {
    fail( "And error was thrown for retrieval of an unknown sequence, auto-creation expected" );
  }

  // After the test, stop the server
  //
  carteLauncher.getCarte().getWebServer().stopServer();

  // Remove the database + table in the temporary directory
  //
  File dir = new File( dbDir );
  if ( dir.exists() ) {
    for ( File child : dir.listFiles() ) {
      if ( child.isFile() ) {
        child.delete();
      }
    }
    dir.delete();
  }
}
 

public boolean init( StepMetaInterface smi, StepDataInterface sdi ) {
  meta = (IngresVectorwiseLoaderMeta) smi;
  data = (IngresVectorwiseLoaderData) sdi;

  if ( super.init( smi, sdi ) ) {

    // Confirming Database Connection is defined.
    try {
      verifyDatabaseConnection();
    } catch ( KettleException ex ) {
      logError( ex.getMessage() );
      return false;
    }

    if ( Utils.isEmpty( meta.getDelimiter() ) ) {
      data.separator = data.getBytes( "|" );
    } else {
      data.separator = data.getBytes( meta.getDelimiter() );
    }

    data.newline = data.getBytes( "\n" );
    data.semicolon = data.getBytes( ";" );
    data.doubleQuote = data.getBytes( "\"" );

    // Schema-table combination...
    data.schemaTable =
      meta.getDatabaseMeta().getQuotedSchemaTableCombination( null, environmentSubstitute( meta.getTableName() ) );

    data.encoding = environmentSubstitute( meta.getEncoding() );
    data.isEncoding = !Utils.isEmpty( environmentSubstitute( meta.getEncoding() ) );

    data.byteBuffer = null;

    String bufferSizeString = environmentSubstitute( meta.getBufferSize() );
    data.bufferSize = Utils.isEmpty( bufferSizeString ) ? 5000 : Const.toInt( bufferSizeString, 5000 );

    if ( meta.isTruncatingTable() && meta.getDatabaseMeta() != null ) {

      // Connect to Vectorwise over standard JDBC and truncate the table
      //
      Database db = new Database( this, meta.getDatabaseMeta() );
      try {
        db.connect();
        db.execStatement( "CALL VECTORWISE( COMBINE '" + data.schemaTable + " - " + data.schemaTable + "' )" );

        // Just to make sure VW gets the message
        //
        db.execStatement( "CALL VECTORWISE( COMBINE '" + data.schemaTable + " - " + data.schemaTable + "' )" );
        log.logDetailed( "Table " + data.schemaTable + " was truncated using a 'combine' statement." );
      } catch ( Exception e ) {
        log.logError( "Error truncating table", e );
        return false;
      } finally {
        db.disconnect();
      }

    }

    return true;
  }
  return false;
}
 

/**
 * Insert data in the source table.
 *
 * @param db
 *          database to use.
 */
private static void createData( Database db ) throws Exception {
  for ( int idx = 0; idx < insertStatement.length; idx++ ) {
    db.execStatement( insertStatement[idx] );
  }
}
 

/**
 * Insert data in the source table.
 *
 * @param db
 *          database to use.
 */
private void createData( Database db ) throws Exception {
  for ( int idx = 0; idx < insertStatement.length; idx++ ) {
    db.execStatement( insertStatement[idx] );
  }
}
 

/**
 * Insert data in the source table.
 *
 * @param db
 *          database to use.
 */
private static void createData( Database db ) throws Exception {
  for ( int idx = 0; idx < insertStatement.length; idx++ ) {
    db.execStatement( insertStatement[idx] );
  }
}
 

/**
 * Insert data in the source table.
 *
 * @param db
 *          database to use.
 */
private void createData( Database db ) throws Exception {
  for ( int idx = 0; idx < insertStatement.length; idx++ ) {
    db.execStatement( insertStatement[idx] );
  }
}