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

public boolean getInputData() {
  // Get some data...
  RipDatabaseWizardPage1 page1 = (RipDatabaseWizardPage1) getPreviousPage();

  Database sourceDb = new Database( RipDatabaseWizard.loggingObject, page1.getSourceDatabase() );
  try {
    sourceDb.connect();
    input = sourceDb.getTablenames( false ); // Don't include the schema since it can cause invalid syntax
  } catch ( KettleDatabaseException dbe ) {
    new ErrorDialog( shell, "Error getting tables", "Error obtaining table list from database!", dbe );
    input = null;
    return false;
  } finally {
    sourceDb.disconnect();
  }
  return true;
}
 

/**
 * Writes step information to a step logging table (if one has been configured).
 *
 * @throws KettleException if any errors occur during logging
 */
protected void writeStepLogInformation() throws KettleException {
  Database db = null;
  StepLogTable stepLogTable = getTransMeta().getStepLogTable();
  try {
    db = createDataBase( stepLogTable.getDatabaseMeta() );
    db.shareVariablesWith( this );
    db.connect();
    db.setCommit( logCommitSize );

    for ( StepMetaDataCombi combi : getSteps() ) {
      db.writeLogRecord( stepLogTable, LogStatus.START, combi, null );
    }

    db.cleanupLogRecords( stepLogTable );
  } catch ( Exception e ) {
    throw new KettleException( BaseMessages.getString( PKG,
      "Trans.Exception.UnableToWriteStepInformationToLogTable" ), e );
  } finally {
    disconnectDb( db );
  }

}
 

public boolean getInputData() {
  // Get some data...
  CopyTableWizardPage1 page1 = (CopyTableWizardPage1) getPreviousPage();

  Database sourceDb = new Database( CopyTableWizard.loggingObject, page1.getSourceDatabase() );
  try {
    sourceDb.connect();
    input = sourceDb.getTablenames();
  } catch ( KettleDatabaseException dbe ) {
    new ErrorDialog(
      shell, BaseMessages.getString( PKG, "CopyTableWizardPage2.ErrorGettingTables.DialogTitle" ),
      BaseMessages.getString( PKG, "CopyTableWizardPage2.ErrorGettingTables.DialogMessage" ), dbe );
    input = null;
    return false;
  } finally {
    sourceDb.disconnect();
  }
  return true;
}
 

private void getSchemaNames() {
  DatabaseMeta databaseMeta = transMeta.findDatabase( wConnection.getText() );
  if ( databaseMeta != null ) {
    Database database = new Database( loggingObject, databaseMeta );
    try {
      database.connect();
      String[] schemas = database.getSchemas();

      if ( null != schemas && schemas.length > 0 ) {
        schemas = Const.sortStrings( schemas );
        EnterSelectionDialog dialog =
          new EnterSelectionDialog( shell, schemas, BaseMessages.getString(
            PKG, "DatabaseLookupDialog.AvailableSchemas.Title", wConnection.getText() ), BaseMessages
            .getString( PKG, "DatabaseLookupDialog.AvailableSchemas.Message", wConnection.getText() ) );
        String d = dialog.open();
        if ( d != null ) {
          wSchema.setText( Const.NVL( d, "" ) );
          setTableFieldCombo();
        }

      } else {
        MessageBox mb = new MessageBox( shell, SWT.OK | SWT.ICON_ERROR );
        mb.setMessage( BaseMessages.getString( PKG, "DatabaseLookupDialog.NoSchema.Error" ) );
        mb.setText( BaseMessages.getString( PKG, "DatabaseLookupDialog.GetSchemas.Error" ) );
        mb.open();
      }
    } catch ( Exception e ) {
      new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
        .getString( PKG, "DatabaseLookupDialog.ErrorGettingSchemas" ), e );
    } finally {
      database.disconnect();
    }
  }
}
 

/**
 * Get the fields from the table in the database and use them as lookup keys. Only get the the fields which are not
 * yet in use as key, or in the field table. Also ignore technical key, version, fromdate, todate.
 */
private void getLookup() {
  DatabaseMeta databaseMeta = transMeta.findDatabase( wConnection.getText() );
  if ( databaseMeta != null ) {
    Database db = new Database( loggingObject, databaseMeta );
    db.shareVariablesWith( transMeta );
    try {
      db.connect();
      RowMetaInterface r = db.getTableFieldsMeta( wSchema.getText(), wTable.getText() );
      if ( r != null && !r.isEmpty() ) {
        BaseStepDialog.getFieldsFromPrevious(
          r, wUpIns, 2, new int[] { 1, 2 }, new int[] { 3 }, -1, -1, new TableItemInsertListener() {
            public boolean tableItemInserted( TableItem tableItem, ValueMetaInterface v ) {
              int idx = wKey.indexOfString( v.getName(), 2 );
              return idx < 0
                && !v.getName().equalsIgnoreCase( wTk.getText() )
                && !v.getName().equalsIgnoreCase( wVersion.getText() )
                && !v.getName().equalsIgnoreCase( wFromdate.getText() )
                && !v.getName().equalsIgnoreCase( wTodate.getText() );
            }
          } );
      }
    } catch ( KettleException e ) {
      MessageBox mb = new MessageBox( shell, SWT.OK | SWT.ICON_ERROR );
      mb.setText( BaseMessages.getString( PKG, "DimensionLookupDialog.ErrorOccurred.DialogTitle" ) );
      mb.setMessage( BaseMessages.getString( PKG, "DimensionLookupDialog.ErrorOccurred.DialogMessage" )
        + Const.CR + e.getMessage() );
      mb.open();
    } finally {
      db.disconnect();
    }
  }
}
 

private void getSchemaNames() {
  DatabaseMeta databaseMeta = transMeta.findDatabase( wConnection.getText() );
  if ( databaseMeta != null ) {
    Database database = new Database( loggingObject, databaseMeta );
    try {
      database.connect();
      String[] schemas = database.getSchemas();

      if ( null != schemas && schemas.length > 0 ) {
        schemas = Const.sortStrings( schemas );
        EnterSelectionDialog dialog =
          new EnterSelectionDialog( shell, schemas, BaseMessages.getString(
            PKG, "TableOutputDialog.AvailableSchemas.Title", wConnection.getText() ), BaseMessages
            .getString( PKG, "TableOutputDialog.AvailableSchemas.Message", wConnection.getText() ) );
        String d = dialog.open();
        if ( d != null ) {
          wSchema.setText( Const.NVL( d, "" ) );
          setTableFieldCombo();
        }

      } else {
        MessageBox mb = new MessageBox( shell, SWT.OK | SWT.ICON_ERROR );
        mb.setMessage( BaseMessages.getString( PKG, "TableOutputDialog.NoSchema.Error" ) );
        mb.setText( BaseMessages.getString( PKG, "TableOutputDialog.GetSchemas.Error" ) );
        mb.open();
      }
    } catch ( Exception e ) {
      new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
        .getString( PKG, "TableOutputDialog.ErrorGettingSchemas" ), e );
    } finally {
      database.disconnect();
    }
  }
}
 

/**
 * User requested to clear the log table.<br>
 * Better ask confirmation
 */
private void clearLogTable( int index ) {
  JobHistoryLogTab model = models[index];
  LogTableInterface logTable = model.logTable;

  if ( logTable.isDefined() ) {
    DatabaseMeta databaseMeta = logTable.getDatabaseMeta();

    MessageBox mb = new MessageBox( jobGraph.getShell(), SWT.YES | SWT.NO | SWT.ICON_QUESTION );
    mb.setMessage( BaseMessages.getString( PKG, "JobGraph.Dialog.AreYouSureYouWantToRemoveAllLogEntries.Message",
      logTable.getQuotedSchemaTableCombination() ) );
    mb.setText( BaseMessages.getString( PKG, "JobGraph.Dialog.AreYouSureYouWantToRemoveAllLogEntries.Title" ) );
    if ( mb.open() == SWT.YES ) {
      Database database = new Database( loggingObject, databaseMeta );
      try {
        database.connect();
        database.truncateTable( logTable.getSchemaName(), logTable.getTableName() );
      } catch ( Exception e ) {
        new ErrorDialog( jobGraph.getShell(),
          BaseMessages.getString( PKG, "JobGraph.Dialog.ErrorClearningLoggingTable.Title" ),
          BaseMessages.getString( PKG, "JobGraph.Dialog.AreYouSureYouWantToRemoveAllLogEntries.Message" ), e );
      } finally {
        database.disconnect();

        refreshHistory();
        if ( model.logDisplayText != null ) {
          model.logDisplayText.setText( "" );
        }
      }
    }
  }
}
 

private void executeSqlPrecondition( String sqlFile ) throws IOException, KettleDatabaseException {
  String path = PKG + sqlFile;
  InputStream input = TableCompareIT.class.getClassLoader().getResourceAsStream( PKG + sqlFile );
  if ( input == null ) {
    throw new IOException( "Resource not found in classpath: " + path );
  }
  String sql = TestUtilities.getStringFromInput( input );
  Database db = new Database( log, databaseMeta );
  db.connect();
  db.execStatements( sql );
  db.commit( true );
  db.disconnect();
}
 

private void getSchemaNames() {
  if ( wSchemaname.isDisposed() ) {
    return;
  }
  DatabaseMeta databaseMeta = jobMeta.findDatabase( wConnection.getText() );
  if ( databaseMeta != null ) {
    Database database = new Database( loggingObject, databaseMeta );
    database.shareVariablesWith( jobMeta );
    try {
      database.connect();
      String[] schemas = database.getSchemas();

      if ( null != schemas && schemas.length > 0 ) {
        schemas = Const.sortStrings( schemas );
        EnterSelectionDialog dialog = new EnterSelectionDialog( shell, schemas,
          BaseMessages.getString( PKG, "System.Dialog.AvailableSchemas.Title", wConnection.getText() ),
          BaseMessages.getString( PKG, "System.Dialog.AvailableSchemas.Message" ) );
        String d = dialog.open();
        if ( d != null ) {
          wSchemaname.setText( Const.NVL( d.toString(), "" ) );
        }

      } else {
        MessageBox mb = new MessageBox( shell, SWT.OK | SWT.ICON_ERROR );
        mb.setMessage( BaseMessages.getString( PKG, "System.Dialog.AvailableSchemas.Empty.Message" ) );
        mb.setText( BaseMessages.getString( PKG, "System.Dialog.AvailableSchemas.Empty.Title" ) );
        mb.open();
      }
    } catch ( Exception e ) {
      new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ),
        BaseMessages.getString( PKG, "System.Dialog.AvailableSchemas.ConnectionError" ), e );
    } finally {
      if ( database != null ) {
        database.disconnect();
        database = null;
      }
    }
  }
}
 

public RowMetaInterface getRequiredFields( VariableSpace space ) throws KettleException {
  String realTableName = space.environmentSubstitute( tableName );
  String realSchemaName = space.environmentSubstitute( schemaName );

  if ( databaseMeta != null ) {
    Database db = new Database( loggingObject, databaseMeta );
    try {
      db.connect();

      if ( !Utils.isEmpty( realTableName ) ) {
        String schemaTable = databaseMeta.getQuotedSchemaTableCombination( realSchemaName, realTableName );

        // Check if this table exists...
        if ( db.checkTableExists( schemaTable ) ) {
          return db.getTableFields( schemaTable );
        } else {
          throw new KettleException( BaseMessages.getString( PKG, "GPLoadMeta.Exception.TableNotFound" ) );
        }
      } else {
        throw new KettleException( BaseMessages.getString( PKG, "GPLoadMeta.Exception.TableNotSpecified" ) );
      }
    } catch ( Exception e ) {
      throw new KettleException( BaseMessages.getString( PKG, "GPLoadMeta.Exception.ErrorGettingFields" ), e );
    } finally {
      db.disconnect();
    }
  } else {
    throw new KettleException( BaseMessages.getString( PKG, "GPLoadMeta.Exception.ConnectionNotDefined" ) );
  }
}
 

private void getSchemaNames() {
  DatabaseMeta databaseMeta = transMeta.findDatabase( wConnection.getText() );
  if ( databaseMeta != null ) {
    Database database = new Database( loggingObject, databaseMeta );
    try {
      database.connect();
      String[] schemas = database.getSchemas();

      if ( null != schemas && schemas.length > 0 ) {
        schemas = Const.sortStrings( schemas );
        EnterSelectionDialog dialog =
          new EnterSelectionDialog( shell, schemas,
            BaseMessages.getString( PKG, "DeleteDialog.AvailableSchemas.Title", wConnection.getText() ),
            BaseMessages.getString( PKG, "DeleteDialog.AvailableSchemas.Message", wConnection.getText() ) );
        String d = dialog.open();
        if ( d != null ) {
          wSchema.setText( Const.NVL( d, "" ) );
          setTableFieldCombo();
        }

      } else {
        MessageBox mb = new MessageBox( shell, SWT.OK | SWT.ICON_ERROR );
        mb.setMessage( BaseMessages.getString( PKG, "DeleteDialog.NoSchema.Error" ) );
        mb.setText( BaseMessages.getString( PKG, "DeleteDialog.GetSchemas.Error" ) );
        mb.open();
      }
    } catch ( Exception e ) {
      new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
        .getString( PKG, "DeleteDialog.ErrorGettingSchemas" ), e );
    } finally {
      database.disconnect();
    }
  }
}
 

public Result execute( Result previousResult, int nr ) {
  Result result = previousResult;
  List<RowMetaAndData> rows = result.getRows();
  RowMetaAndData resultRow = null;

  result.setResult( true );
  nrErrors = 0;
  continueProcess = true;
  nrSuccess = 0;

  if ( argFromPrevious ) {
    if ( log.isDetailed() ) {
      logDetailed( BaseMessages.getString( PKG, "JobEntryTruncateTables.FoundPreviousRows", String
        .valueOf( ( rows != null ? rows.size() : 0 ) ) ) );
    }
    if ( rows.size() == 0 ) {
      return result;
    }
  }
  if ( connection != null ) {
    Database db = new Database( this, connection );
    db.shareVariablesWith( this );
    try {
      db.connect( parentJob.getTransactionId(), null );
      if ( argFromPrevious && rows != null ) { // Copy the input row to the (command line) arguments

        for ( int iteration = 0; iteration < rows.size() && !parentJob.isStopped() && continueProcess; iteration++ ) {
          resultRow = rows.get( iteration );

          // Get values from previous result
          String tablename_previous = resultRow.getString( 0, null );
          String schemaname_previous = resultRow.getString( 1, null );

          if ( !Utils.isEmpty( tablename_previous ) ) {
            if ( log.isDetailed() ) {
              logDetailed( BaseMessages.getString(
                PKG, "JobEntryTruncateTables.ProcessingRow", tablename_previous, schemaname_previous ) );
            }

            // let's truncate table
            if ( truncateTables( tablename_previous, schemaname_previous, db ) ) {
              updateSuccess();
            } else {
              updateErrors();
            }
          } else {
            logError( BaseMessages.getString( PKG, "JobEntryTruncateTables.RowEmpty" ) );
          }
        }

      } else if ( arguments != null ) {
        for ( int i = 0; i < arguments.length && !parentJob.isStopped() && continueProcess; i++ ) {
          String realTablename = environmentSubstitute( arguments[i] );
          String realSchemaname = environmentSubstitute( schemaname[i] );
          if ( !Utils.isEmpty( realTablename ) ) {
            if ( log.isDetailed() ) {
              logDetailed( BaseMessages.getString(
                PKG, "JobEntryTruncateTables.ProcessingArg", arguments[i], schemaname[i] ) );
            }

            // let's truncate table
            if ( truncateTables( realTablename, realSchemaname, db ) ) {
              updateSuccess();
            } else {
              updateErrors();
            }
          } else {
            logError( BaseMessages.getString(
              PKG, "JobEntryTruncateTables.ArgEmpty", arguments[i], schemaname[i] ) );
          }
        }
      }
    } catch ( Exception dbe ) {
      result.setNrErrors( 1 );
      logError( BaseMessages.getString( PKG, "JobEntryTruncateTables.Error.RunningEntry", dbe.getMessage() ) );
    } finally {
      if ( db != null ) {
        db.disconnect();
      }
    }
  } else {
    result.setNrErrors( 1 );
    logError( BaseMessages.getString( PKG, "JobEntryTruncateTables.NoDbConnection" ) );
  }

  result.setNrErrors( nrErrors );
  result.setNrLinesDeleted( nrSuccess );
  result.setResult( nrErrors == 0 );
  return result;
}
 

/**
 * Get a list of columns, comma separated, allow the user to select from it.
 *
 */
private void getListColumns() {
  if ( !Utils.isEmpty( wTablename.getText() ) ) {
    DatabaseMeta databaseMeta = jobMeta.findDatabase( wConnection.getText() );
    if ( databaseMeta != null ) {
      Database database = new Database( loggingObject, databaseMeta );
      database.shareVariablesWith( jobMeta );
      try {
        database.connect();
        RowMetaInterface row =
          database.getTableFieldsMeta( wSchemaname.getText(), wTablename.getText() );
        String[] available = row.getFieldNames();

        String[] source = wListColumn.getText().split( "," );
        for ( int i = 0; i < source.length; i++ ) {
          source[i] = Const.trim( source[i] );
        }
        int[] idxSource = Const.indexsOfStrings( source, available );
        EnterSelectionDialog dialog = new EnterSelectionDialog( shell, available,
          BaseMessages.getString( PKG, "JobMysqlBulkFile.SelectColumns.Title" ),
          BaseMessages.getString( PKG, "JobMysqlBulkFile.SelectColumns.Message" ) );
        dialog.setMulti( true );
        dialog.setAvoidQuickSearch();
        dialog.setSelectedNrs( idxSource );
        if ( dialog.open() != null ) {
          String columns = "";
          int[] idx = dialog.getSelectionIndeces();
          for ( int i = 0; i < idx.length; i++ ) {
            if ( i > 0 ) {
              columns += ", ";
            }
            columns += available[idx[i]];
          }
          wListColumn.setText( columns );
        }
      } catch ( KettleDatabaseException e ) {
        new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
          .getString( PKG, "JobMysqlBulkFile.ConnectionError2.DialogMessage" ), e );
      } finally {
        database.disconnect();
      }
    }
  }
}
 

public static String getLastExecutionResult( LogChannelInterface log, LoggingObjectInterface parentObject,
  ReportSubjectLocation filename ) throws KettleException {

  LogTableInterface logTable = null;
  if ( filename.isTransformation() ) {
    TransMeta transMeta = TransformationInformation.getInstance().getTransMeta( filename );
    logTable = transMeta.getTransLogTable();
  } else {
    JobMeta jobMeta = JobInformation.getInstance().getJobMeta( filename );
    logTable = jobMeta.getJobLogTable();
  }
  if ( logTable != null && logTable.isDefined() ) {
    DatabaseMeta dbMeta = logTable.getDatabaseMeta();
    Database database = new Database( parentObject, dbMeta );
    try {
      database.connect();
      String sql = "SELECT ";
      sql += dbMeta.quoteField( logTable.getStatusField().getFieldName() ) + ", ";
      sql += dbMeta.quoteField( logTable.getLogDateField().getFieldName() ) + ", ";
      sql += dbMeta.quoteField( logTable.getErrorsField().getFieldName() ) + "";
      sql += " FROM ";
      sql += dbMeta.getQuotedSchemaTableCombination( logTable.getSchemaName(), logTable.getTableName() );
      sql += " ORDER BY " + dbMeta.quoteField( logTable.getLogDateField().getFieldName() ) + " DESC";

      RowMetaAndData oneRow = database.getOneRow( sql );
      String status = oneRow.getString( 0, "?" );
      Date date = oneRow.getDate( 1, null );
      Long nrErrors = oneRow.getInteger( 2 );

      String evaluation;
      if ( status.equalsIgnoreCase( LogStatus.END.getStatus() ) ) {
        evaluation = "Ended";
      } else if ( status.equalsIgnoreCase( LogStatus.START.getStatus() ) ) {
        evaluation = "Started";
      } else if ( status.equalsIgnoreCase( LogStatus.STOP.getStatus() ) ) {
        evaluation = "Stopped";
      } else if ( status.equalsIgnoreCase( LogStatus.RUNNING.getStatus() ) ) {
        evaluation = "Running";
      } else if ( status.equalsIgnoreCase( LogStatus.PAUSED.getStatus() ) ) {
        evaluation = "Paused";
      } else if ( status.equalsIgnoreCase( LogStatus.ERROR.getStatus() ) ) {
        evaluation = "Failed";
      } else {
        evaluation = "Unknown";
      }
      if ( nrErrors > 0 ) {
        evaluation += " with errors";
      } else {
        evaluation += " with success";
      }

      return evaluation + " at " + XMLHandler.date2string( date );

    } catch ( Exception e ) {
      log.logBasic( "Unable to get logging information from log table" + logTable );
    } finally {
      database.disconnect();
    }
  }
  return null;
}
 

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

}
 

/**
 * Get a list of columns, comma separated, allow the user to select from it.
 */
private void getListColumns() {
  if ( !Utils.isEmpty( wTablename.getText() ) ) {
    DatabaseMeta databaseMeta = jobMeta.findDatabase( wConnection.getText() );
    if ( databaseMeta != null ) {
      Database database = new Database( loggingObject, databaseMeta );
      database.shareVariablesWith( jobMeta );
      try {
        database.connect();
        String schemaTable =
          databaseMeta.getQuotedSchemaTableCombination( wSchemaname.getText(), wTablename.getText() );
        RowMetaInterface row = database.getTableFields( schemaTable );
        String[] available = row.getFieldNames();

        String[] source = wListattribut.getText().split( "," );
        for ( int i = 0; i < source.length; i++ ) {
          source[i] = Const.trim( source[i] );
        }
        int[] idxSource = Const.indexsOfStrings( source, available );
        EnterSelectionDialog dialog = new EnterSelectionDialog( shell, available,
          BaseMessages.getString( PKG, "JobMysqlBulkLoad.SelectColumns.Title" ),
          BaseMessages.getString( PKG, "JobMysqlBulkLoad.SelectColumns.Message" ) );
        dialog.setMulti( true );
        dialog.setAvoidQuickSearch();
        dialog.setSelectedNrs( idxSource );
        if ( dialog.open() != null ) {
          String columns = "";
          int[] idx = dialog.getSelectionIndeces();
          for ( int i = 0; i < idx.length; i++ ) {
            if ( i > 0 ) {
              columns += ", ";
            }
            columns += available[idx[i]];
          }
          wListattribut.setText( columns );
        }
      } catch ( KettleDatabaseException e ) {
        new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
          .getString( PKG, "JobMysqlBulkLoad.ConnectionError2.DialogMessage" ), e );
      } finally {
        database.disconnect();
      }
    }
  }
}
 

/**
 * Test case for table input which is taking its input from a hop. This is a regression test case for JIRA PDI-588.
 *
 * The query in the table input step has one '?' and this parameter is filled by values read from an input hop.
 */
public void testTableInputWithParam() throws Exception {
  KettleEnvironment.init();

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

  // 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" );

  // Execute our setup SQLs in the database.
  Database database = new Database( transMeta, dbInfo );
  database.connect();
  createTables( database );
  createData( database );

  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 source step...
  //
  String fromstepname = "read from [" + source_table + "]";
  TableInputMeta tii = new TableInputMeta();
  tii.setDatabaseMeta( transMeta.findDatabase( "db" ) );
  tii.setLookupFromStep( injectorStep );
  tii.setExecuteEachInputRow( true );
  String selectSQL = "SELECT " + Const.CR;
  selectSQL += "ID, CODE ";
  selectSQL += "FROM " + source_table + " WHERE CODE = ? ORDER BY ID, CODE;";
  tii.setSQL( selectSQL );

  String fromstepid = registry.getPluginId( StepPluginType.class, tii );
  StepMeta fromstep = new StepMeta( fromstepid, fromstepname, tii );
  fromstep.setDescription( "Reads information from table [" + source_table + "] on database [" + dbInfo + "]" );
  transMeta.addStep( fromstep );

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

  // Now execute the transformation...
  Trans trans = new Trans( transMeta );

  trans.prepareExecution( null );

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

  RowProducer rp = trans.addRowProducer( injectorStepname, 0 );
  trans.startThreads();

  // add rows
  List<RowMetaAndData> inputList = createDataRows();
  for ( RowMetaAndData rm : inputList ) {
    rp.putRow( rm.getRowMeta(), rm.getData() );
  }
  rp.finished();

  trans.waitUntilFinished();

  List<RowMetaAndData> resultRows = rc.getRowsWritten();
  List<RowMetaAndData> goldRows = createResultDataRows();
  checkRows( goldRows, resultRows );

}
 

@SuppressWarnings( "unused" )
public static Object fireToDB( ScriptEngine actualContext, Bindings actualObject, Object[] ArgList,
  Object FunctionContext ) {

  Object oRC = new Object();
  if ( ArgList.length == 2 ) {
    try {
      Object scmO = actualObject.get( "_step_" );
      Script scm = (Script) scmO;
      String strDBName = (String) ArgList[0];
      String strSQL = (String) ArgList[1];
      DatabaseMeta ci = DatabaseMeta.findDatabase( scm.getTransMeta().getDatabases(), strDBName );
      if ( ci == null ) {
        throw new RuntimeException( "Database connection not found: " + strDBName );
      }
      ci.shareVariablesWith( scm );

      Database db = new Database( scm, ci );
      db.setQueryLimit( 0 );
      try {
        if ( scm.getTransMeta().isUsingUniqueConnections() ) {
          synchronized ( scm.getTrans() ) {
            db.connect( scm.getTrans().getTransactionId(), scm.getPartitionID() );
          }
        } else {
          db.connect( scm.getPartitionID() );
        }

        ResultSet rs = db.openQuery( strSQL );
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        if ( rs != null ) {
          List<Object[]> list = new ArrayList<Object[]>();
          while ( rs.next() ) {
            Object[] objRow = new Object[columnCount];
            for ( int i = 0; i < columnCount; i++ ) {
              objRow[i] = rs.getObject( i + 1 );
            }
            list.add( objRow );
          }
          Object[][] resultArr = new Object[list.size()][];
          list.toArray( resultArr );
          db.disconnect();
          return resultArr;
        }
      } catch ( Exception er ) {
        throw new RuntimeException( er.toString() );
      }
    } catch ( Exception e ) {
      throw new RuntimeException( e.toString() );
    }
  } else {
    throw new RuntimeException( "The function call fireToDB requires 2 arguments." );
  }
  return oRC;
}
 

public Domain generateDomain( final ImportStrategy importStrategy ) throws PentahoMetadataException {
  Domain domain = new Domain();
  domain.setId( modelName );

  List<LocaleType> locales = new ArrayList<LocaleType>();
  locales.add( new LocaleType( "en_US", "English (US)" ) ); //$NON-NLS-1$ //$NON-NLS-2$
  domain.setLocales( locales );

  SqlPhysicalModel physicalModel = new SqlPhysicalModel();
  physicalModel.setId( databaseMeta.getName() );
  physicalModel.setDatasource( ThinModelConverter.convertFromLegacy( databaseMeta ) );

  Database database = database();

  try {
    // Add the database connection to the empty schema...
    //
    domain.addPhysicalModel( physicalModel );

    // Also add a model with the same name as the model name...
    //
    String bmID = Util.getLogicalModelIdPrefix() + "_" + modelName.replaceAll( " ", "_" ).toUpperCase(); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    LogicalModel logicalModel = new LogicalModel();
    logicalModel.setId( bmID );
    domain.addLogicalModel( logicalModel );

    // Connect to the database...
    //
    database.connect();

    // clear the cache
    DBCache.getInstance().clear( databaseMeta.getName() );

    for ( int i = 0; i < tableNames.length; i++ ) {
      SchemaTable schemaTable = tableNames[i];

      // Import the specified tables and turn them into PhysicalTable
      // objects...
      //
      SqlPhysicalTable physicalTable =
          PhysicalTableImporter.importTableDefinition( database, schemaTable.getSchemaName(), schemaTable
          .getTableName(), locale, importStrategy );
      physicalModel.addPhysicalTable( physicalTable );

      // At the same time, we will create a business table and add that to the
      // business model...
      //
      LogicalTable businessTable = createBusinessTable( physicalTable, locale );
      logicalModel.addLogicalTable( businessTable );
    }
  } catch ( Exception e ) {
    // For the unexpected stuff, just throw the exception upstairs.
    //
    throw new PentahoMetadataException( e );
  } finally {
    // Make sure to close the connection
    //
    database.disconnect();
  }

  return domain;
}
 

@SuppressWarnings( "unused" )
public static Object fireToDB( Context actualContext, Scriptable actualObject, Object[] ArgList,
  Function FunctionContext ) {

  Object oRC = new Object();
  if ( ArgList.length == 2 ) {
    try {
      Object scmO = actualObject.get( "_step_", actualObject );
      ScriptValuesMod scm = (ScriptValuesMod) Context.jsToJava( scmO, ScriptValuesMod.class );
      String strDBName = Context.toString( ArgList[0] );
      String strSQL = Context.toString( ArgList[1] );
      DatabaseMeta ci = DatabaseMeta.findDatabase( scm.getTransMeta().getDatabases(), strDBName );
      if ( ci == null ) {
        throw Context.reportRuntimeError( "Database connection not found: " + strDBName );
      }
      ci.shareVariablesWith( scm );

      Database db = new Database( scm, ci );
      db.setQueryLimit( 0 );
      try {
        if ( scm.getTransMeta().isUsingUniqueConnections() ) {
          synchronized ( scm.getTrans() ) {
            db.connect( scm.getTrans().getTransactionId(), scm.getPartitionID() );
          }
        } else {
          db.connect( scm.getPartitionID() );
        }

        ResultSet rs = db.openQuery( strSQL );
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        if ( rs != null ) {
          List<Object[]> list = new ArrayList<Object[]>();
          while ( rs.next() ) {
            Object[] objRow = new Object[columnCount];
            for ( int i = 0; i < columnCount; i++ ) {
              objRow[i] = rs.getObject( i + 1 );
            }
            list.add( objRow );
          }
          Object[][] resultArr = new Object[list.size()][];
          list.toArray( resultArr );
          db.disconnect();
          return resultArr;
        }
      } catch ( Exception er ) {
        throw Context.reportRuntimeError( er.toString() );
      }
    } catch ( Exception e ) {
      throw Context.reportRuntimeError( e.toString() );
    }
  } else {
    throw Context.reportRuntimeError( "The function call fireToDB requires 2 arguments." );
  }
  return oRC;
}