public SQLStatement getSQLStatements(
TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) throws KettleStepException {
SQLStatement retval =
new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do!
if (databaseMeta != null) {
if (prev != null && prev.size() > 0) {
// Copy the row
RowMetaInterface tableFields = new RowMeta();
// Now change the field names
for (int i = 0; i < fieldTable.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(fieldStream[i]);
if (v != null) {
ValueMetaInterface tableField = v.clone();
tableField.setName(fieldTable[i]);
tableFields.addValueMeta(tableField);
} else {
throw new KettleStepException(
"Unable to find field [" + fieldStream[i] + "] in the input rows");
}
}
if (!Const.isEmpty(tableName)) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(
transMeta.environmentSubstitute(schemaName),
transMeta.environmentSubstitute(tableName));
String sql = db.getDDL(schemaTable, tableFields, null, false, null, true);
if (sql.length() == 0) retval.setSQL(null);
else retval.setSQL(sql);
} catch (KettleException e) {
retval.setError(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.GetSQL.ErrorOccurred")
+ e.getMessage()); // $NON-NLS-1$
}
} else {
retval.setError(
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.GetSQL.NoTableDefinedOnConnection")); // $NON-NLS-1$
}
} else {
retval.setError(
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.GetSQL.NotReceivingAnyFields")); // $NON-NLS-1$
}
} else {
retval.setError(
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.GetSQL.NoConnectionDefined")); // $NON-NLS-1$
}
return retval;
}
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 (!Const.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, "GPBulkLoaderMeta.Exception.TableNotFound"));
}
} else {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.TableNotSpecified"));
}
} catch (Exception e) {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.ErrorGettingFields"), e);
} finally {
db.disconnect();
}
} else {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.ConnectionNotDefined"));
}
}
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
meta = (IngresVectorwiseLoaderMeta) smi;
data = (IngresVectorwiseLoaderData) sdi;
if (super.init(smi, sdi)) {
if (Const.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 = !Const.isEmpty(environmentSubstitute(meta.getEncoding()));
data.byteBuffer = null;
String bufferSizeString = environmentSubstitute(meta.getBufferSize());
data.bufferSize =
Const.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;
}
/** Get a list of columns, comma separated, allow the user to select from it. */
private void getListColumns() {
if (!Const.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();
}
}
}
}
// Visible for testing purposes
protected void checkConnection() throws KettleDatabaseException {
// check connection
// connect and disconnect
Database dbchecked = null;
try {
dbchecked = new Database(this, connection);
dbchecked.shareVariablesWith(this);
dbchecked.connect(parentJob.getTransactionId(), null);
} finally {
if (dbchecked != null) {
dbchecked.disconnect();
}
}
}
private void getSchemaNames() {
if (wSchemaname.isDisposed()) return;
DatabaseMeta databaseMeta = transMeta.findDatabase(wConnection.getText());
if (databaseMeta != null) {
Database database = new Database(loggingObject, databaseMeta);
database.shareVariablesWith(transMeta);
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;
}
}
}
}
/**
* Given a name of a non-existing table to drop. <br>
* When StagingTransformGenerator is called to drop this table, then it shouldn't execute drop
* statement.
*/
@Test
public void shouldNotDropTableIfNotExists() throws Exception {
String nonExistingTable = "nonExistingTable";
when(database.checkTableExists(nonExistingTable)).thenReturn(false);
when(databaseMeta.getQuotedSchemaTableCombination((String) isNull(), eq(nonExistingTable)))
.thenReturn(nonExistingTable);
stagingTransformGenerator.dropTable(nonExistingTable);
verify(database, never()).execStatement(anyString());
}
/**
* Given a name of an existing table to drop. <br>
* When StagingTransformGenerator is called to drop this table, then it should execute drop
* statement.
*/
@Test
public void shouldDropTableIfExists() throws Exception {
String existingTable = "existingTable";
when(database.checkTableExists(existingTable)).thenReturn(true);
when(databaseMeta.getQuotedSchemaTableCombination((String) isNull(), eq(existingTable)))
.thenReturn(existingTable);
stagingTransformGenerator.dropTable(existingTable);
verify(database).execStatement("DROP TABLE existingTable");
}
public RowMetaInterface getTableFields() {
LogWriter log = LogWriter.getInstance();
RowMetaInterface fields = null;
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
databases = new Database[] {db}; // Keep track of this one for cancelQuery
try {
db.connect();
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tablename);
fields = db.getTableFields(schemaTable);
} catch (KettleDatabaseException dbe) {
log.logError(
toString(),
Messages.getString("DatabaseLookupMeta.ERROR0004.ErrorGettingTableFields")
+ dbe.getMessage()); // $NON-NLS-1$
} finally {
db.disconnect();
}
}
return fields;
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepinfo,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
if (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.TablenameOK"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
// Check fields in table
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.VisitTableSuccessfully"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
for (int i = 0; i < keyLookup.length; i++) {
String lufield = keyLookup[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("DeleteMeta.CheckResult.MissingCompareFieldsInTargetTable")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.FoundLookupFields"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DeleteMeta.CheckResult.CouldNotReadTableInfo"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString(
"DeleteMeta.CheckResult.ConnectedStepSuccessfully",
String.valueOf(prev.size())),
stepinfo); //$NON-NLS-1$ //$NON-NLS-2$
remarks.add(cr);
boolean first = true;
error_message = ""; // $NON-NLS-1$
boolean error_found = false;
for (int i = 0; i < keyStream.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(keyStream[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("DeleteMeta.CheckResult.MissingFields")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR; // $NON-NLS-1$
}
}
for (int i = 0; i < keyStream2.length; i++) {
if (keyStream2[i] != null && keyStream2[i].length() > 0) {
ValueMetaInterface v = prev.searchValueMeta(keyStream2[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("DeleteMeta.CheckResult.MissingFields2")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR; // $NON-NLS-1$
}
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.AllFieldsFound"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
// How about the fields to insert/update the table with?
first = true;
error_found = false;
error_message = ""; // $NON-NLS-1$
} else {
error_message =
Messages.getString("DeleteMeta.CheckResult.MissingFields3") + Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
Messages.getString("DeleteMeta.CheckResult.DatabaseError")
+ e.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message = Messages.getString("DeleteMeta.CheckResult.InvalidConnection"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
// See if we have input streams leading to this step!
if (input.length > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.StepReceivingInfo"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
Messages.getString("DeleteMeta.CheckResult.NoInputReceived"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
}
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepinfo,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
db.shareVariablesWith(transMeta);
databases = new Database[] {db}; // Keep track of this one for cancelQuery
try {
db.connect();
if (!Const.isEmpty(tablename)) {
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tablename);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
// Check the keys used to do the lookup...
for (int i = 0; i < tableKeyField.length; i++) {
String lufield = tableKeyField[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString(
"DatabaseLookupMeta.Check.MissingCompareFieldsInLookupTable")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllLookupFieldsFoundInTable"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
// Also check the returned values!
for (int i = 0; i < returnValueField.length; i++) {
String lufield = returnValueField[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString(
"DatabaseLookupMeta.Check.MissingReturnFieldsInLookupTable")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllReturnFieldsFoundInTable"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.CouldNotReadTableInfo"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
boolean first = true;
error_message = ""; // $NON-NLS-1$
boolean error_found = false;
for (int i = 0; i < streamKeyField1.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(streamKeyField1[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("DatabaseLookupMeta.Check.MissingFieldsNotFoundInInput")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + streamKeyField1[i] + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllFieldsFoundInInput"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.CouldNotReadFromPreviousSteps")
+ Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
} catch (KettleDatabaseException dbe) {
error_message =
Messages.getString("DatabaseLookupMeta.Check.DatabaseErrorWhileChecking")
+ dbe.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.MissingConnectionError"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
// See if we have input streams leading to this step!
if (input.length > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.StepIsReceivingInfoFromOtherSteps"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
Messages.getString("DatabaseLookupMeta.Check.NoInputReceivedFromOtherSteps"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
}
}
public SQLStatement getSQLStatements(
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
Repository repository,
IMetaStore metaStore)
throws KettleStepException {
SQLStatement retval =
new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do!
if (databaseMeta != null) {
if (prev != null && prev.size() > 0) {
// Copy the row
RowMetaInterface tableFields = new RowMeta();
// Now change the field names
// the key fields
if (keyLookup != null) {
for (int i = 0; i < keyLookup.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(keyStream[i]);
if (v != null) {
ValueMetaInterface tableField = v.clone();
tableField.setName(keyLookup[i]);
tableFields.addValueMeta(tableField);
} else {
throw new KettleStepException(
"Unable to find field [" + keyStream[i] + "] in the input rows");
}
}
}
// the lookup fields
for (int i = 0; i < updateLookup.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(updateStream[i]);
if (v != null) {
ValueMetaInterface vk = tableFields.searchValueMeta(updateStream[i]);
if (vk == null) { // do not add again when already added as key fields
ValueMetaInterface tableField = v.clone();
tableField.setName(updateLookup[i]);
tableFields.addValueMeta(tableField);
}
} else {
throw new KettleStepException(
"Unable to find field [" + updateStream[i] + "] in the input rows");
}
}
if (!Const.isEmpty(tableName)) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
String cr_table = db.getDDL(schemaTable, tableFields, null, false, null, true);
String cr_index = "";
String[] idx_fields = null;
if (keyLookup != null && keyLookup.length > 0) {
idx_fields = new String[keyLookup.length];
for (int i = 0; i < keyLookup.length; i++) {
idx_fields[i] = keyLookup[i];
}
} else {
retval.setError(
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.MissingKeyFields"));
}
// Key lookup dimensions...
if (idx_fields != null
&& idx_fields.length > 0
&& !db.checkIndexExists(schemaName, tableName, idx_fields)) {
String indexname = "idx_" + tableName + "_lookup";
cr_index =
db.getCreateIndexStatement(
schemaTable, indexname, idx_fields, false, false, false, true);
}
String sql = cr_table + cr_index;
if (sql.length() == 0) {
retval.setSQL(null);
} else {
retval.setSQL(sql);
}
} catch (KettleException e) {
retval.setError(
BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.ErrorOccurred")
+ e.getMessage());
}
} else {
retval.setError(
BaseMessages.getString(
PKG, "InsertUpdateMeta.ReturnValue.NoTableDefinedOnConnection"));
}
} else {
retval.setError(
BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NotReceivingAnyFields"));
}
} else {
retval.setError(
BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NoConnectionDefined"));
}
return retval;
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
String[] input,
String[] output,
RowMetaInterface info,
VariableSpace space,
Repository repository,
IMetaStore metaStore) {
CheckResult cr;
String error_message = "";
if (databaseMeta != null) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
if (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.TableNameOK"),
stepMeta);
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = "";
// Check fields in table
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.TableExists"),
stepMeta);
remarks.add(cr);
for (int i = 0; i < keyLookup.length; i++) {
String lufield = keyLookup[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.MissingCompareFieldsInTargetTable")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllLookupFieldsFound"),
stepMeta);
}
remarks.add(cr);
// How about the fields to insert/update in the table?
first = true;
error_found = false;
error_message = "";
for (int i = 0; i < updateLookup.length; i++) {
String lufield = updateLookup[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG,
"InsertUpdateMeta.CheckResult.MissingFieldsToUpdateInTargetTable")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllFieldsToUpdateFoundInTargetTable"),
stepMeta);
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.CouldNotReadTableInfo");
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.StepReceivingDatas", prev.size() + ""),
stepMeta);
remarks.add(cr);
boolean first = true;
error_message = "";
boolean error_found = false;
for (int i = 0; i < keyStream.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(keyStream[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.MissingFieldsInInput")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR;
}
}
for (int i = 0; i < keyStream2.length; i++) {
if (keyStream2[i] != null && keyStream2[i].length() > 0) {
ValueMetaInterface v = prev.searchValueMeta(keyStream2[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.MissingFieldsInInput")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR;
}
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllFieldsFoundInInput"),
stepMeta);
}
remarks.add(cr);
// How about the fields to insert/update the table with?
first = true;
error_found = false;
error_message = "";
for (int i = 0; i < updateStream.length; i++) {
String lufield = updateStream[i];
ValueMetaInterface v = prev.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.MissingInputStreamFields")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllFieldsFoundInInput2"),
stepMeta);
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.MissingFieldsInInput3")
+ Const.CR;
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.DatabaseErrorOccurred")
+ e.getMessage();
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message = BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.InvalidConnection");
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
// See if we have input streams leading to this step!
if (input.length > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.StepReceivingInfoFromOtherSteps"),
stepMeta);
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.NoInputError"),
stepMeta);
remarks.add(cr);
}
}
public SQLStatement getSQLStatements(
TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) {
SQLStatement retval =
new SQLStatement(stepMeta.getName(), databaseWriteMeta, null); // default: nothing to do!
int i;
if (databaseWriteMeta != null) {
if (prev != null && prev.size() > 0) {
if (!Const.isEmpty(tablename)) {
String schemaTable =
databaseWriteMeta.getQuotedSchemaTableCombination(schemaName, tablename);
Database db = new Database(databaseWriteMeta);
try {
boolean doHash = false;
String cr_table = null;
db.connect();
// OK, what do we put in the new table??
RowMetaInterface fields = new RowMeta();
ValueMetaInterface vkeyfield = null;
if (!Const.isEmpty(technicalKeyField)) {
// First, the new technical key...
vkeyfield = new ValueMeta(technicalKeyField, ValueMetaInterface.TYPE_INTEGER);
vkeyfield.setLength(10);
vkeyfield.setPrecision(0);
}
// Then the hashcode (optional)
ValueMetaInterface vhashfield = null;
if (useHash && !Const.isEmpty(hashField)) {
vhashfield = new ValueMeta(hashField, ValueMetaInterface.TYPE_INTEGER);
vhashfield.setLength(15);
vhashfield.setPrecision(0);
doHash = true;
}
// Then the last update field (optional)
ValueMetaInterface vLastUpdateField = null;
if (!Const.isEmpty(lastUpdateField)) {
vLastUpdateField = new ValueMeta(lastUpdateField, ValueMetaInterface.TYPE_DATE);
}
if (!db.checkTableExists(schemaTable)) {
if (vkeyfield != null) {
// Add technical key field.
fields.addValueMeta(vkeyfield);
}
// Add the keys only to the table
if (keyField != null && keyLookup != null) {
int cnt = keyField.length;
for (i = 0; i < cnt; i++) {
String error_field = ""; // $NON-NLS-1$
// Find the value in the stream
ValueMetaInterface v = prev.searchValueMeta(keyField[i]);
if (v != null) {
String name = keyLookup[i];
ValueMetaInterface newValue = v.clone();
newValue.setName(name);
if (vkeyfield != null) {
if (name.equals(vkeyfield.getName())
|| (doHash == true && name.equals(vhashfield.getName()))) {
error_field += name;
}
}
if (error_field.length() > 0) {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NameCollision",
error_field)); //$NON-NLS-1$
} else {
fields.addValueMeta(newValue);
}
}
}
}
if (doHash == true) {
fields.addValueMeta(vhashfield);
}
if (vLastUpdateField != null) {
fields.addValueMeta(vLastUpdateField);
}
} else {
// Table already exists
// Get the fields that are in the table now:
RowMetaInterface tabFields = db.getTableFields(schemaTable);
// Don't forget to quote these as well...
databaseWriteMeta.quoteReservedWords(tabFields);
if (vkeyfield != null && tabFields.searchValueMeta(vkeyfield.getName()) == null) {
// Add technical key field if it didn't exist yet
fields.addValueMeta(vkeyfield);
}
// Add the already existing fields
int cnt = tabFields.size();
for (i = 0; i < cnt; i++) {
ValueMetaInterface v = tabFields.getValueMeta(i);
fields.addValueMeta(v);
}
// Find the missing fields in the real table
String keyLookup[] = getKeyLookup();
String keyField[] = getKeyField();
if (keyField != null && keyLookup != null) {
cnt = keyField.length;
for (i = 0; i < cnt; i++) {
// Find the value in the stream
ValueMetaInterface v = prev.searchValueMeta(keyField[i]);
if (v != null) {
ValueMetaInterface newValue = v.clone();
newValue.setName(keyLookup[i]);
// Does the corresponding name exist in the table
if (tabFields.searchValueMeta(newValue.getName()) == null) {
fields.addValueMeta(newValue); // nope --> add
}
}
}
}
if (doHash == true && tabFields.searchValueMeta(vhashfield.getName()) == null) {
// Add hash field
fields.addValueMeta(vhashfield);
}
if (vLastUpdateField != null
&& tabFields.searchValueMeta(vLastUpdateField.getName()) == null) {
fields.addValueMeta(vLastUpdateField);
}
}
cr_table =
db.getDDL(
schemaTable,
fields,
(CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation())
&& sequenceFrom != null
&& sequenceFrom.length() != 0)
? null
: technicalKeyField,
CREATION_METHOD_AUTOINC.equals(getTechKeyCreation()),
null,
true);
//
// OK, now let's build the index
//
// What fields do we put int the index?
// Only the hashcode or all fields?
String cr_index = ""; // $NON-NLS-1$
String cr_uniq_index = ""; // $NON-NLS-1$
String idx_fields[] = null;
if (useHash) {
if (hashField != null && hashField.length() > 0) {
idx_fields = new String[] {hashField};
} else {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NotHashFieldSpecified")); //$NON-NLS-1$
}
} else // index on all key fields...
{
if (!Const.isEmpty(keyLookup)) {
int nrfields = keyLookup.length;
if (nrfields > 32
&& databaseWriteMeta.getDatabaseType() == DatabaseMeta.TYPE_DATABASE_ORACLE) {
nrfields = 32; // Oracle indexes are limited to 32 fields...
}
idx_fields = new String[nrfields];
for (i = 0; i < nrfields; i++) idx_fields[i] = keyLookup[i];
} else {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NotFieldsSpecified")); //$NON-NLS-1$
}
}
// OK, now get the create index statement...
if (!Const.isEmpty(technicalKeyField)) {
String techKeyArr[] = new String[] {technicalKeyField};
if (!db.checkIndexExists(schemaName, tablename, techKeyArr)) {
String indexname = "idx_" + tablename + "_pk"; // $NON-NLS-1$ //$NON-NLS-2$
cr_uniq_index =
db.getCreateIndexStatement(
schemaName, tablename, indexname, techKeyArr, true, true, false, true);
cr_uniq_index += Const.CR;
}
}
// OK, now get the create lookup index statement...
if (!Const.isEmpty(idx_fields)
&& !db.checkIndexExists(schemaName, tablename, idx_fields)) {
String indexname = "idx_" + tablename + "_lookup"; // $NON-NLS-1$ //$NON-NLS-2$
cr_index =
db.getCreateIndexStatement(
schemaName, tablename, indexname, idx_fields, false, false, false, true);
cr_index += Const.CR;
}
//
// Don't forget the sequence (optional)
//
String cr_seq = ""; // $NON-NLS-1$
if (databaseWriteMeta.supportsSequences() && !Const.isEmpty(sequenceFrom)) {
if (!db.checkSequenceExists(schemaName, sequenceFrom)) {
cr_seq +=
db.getCreateSequenceStatement(schemaName, sequenceFrom, 1L, 1L, -1L, true);
cr_seq += Const.CR;
}
}
retval.setSQL(cr_table + cr_uniq_index + cr_index + cr_seq);
} catch (KettleException e) {
retval.setError(
Messages.getString("ConcurrentCombinationLookupMeta.ReturnValue.ErrorOccurred")
+ Const.CR
+ e.getMessage()); // $NON-NLS-1$
}
} else {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NotTableDefined")); //$NON-NLS-1$
}
} else {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NotReceivingField")); //$NON-NLS-1$
}
} else {
retval.setError(
Messages.getString(
"ConcurrentCombinationLookupMeta.ReturnValue.NotConnectionDefined")); //$NON-NLS-1$
}
return retval;
}
protected boolean SQLDataOK(
Result result,
long nrRowsLimit,
String realSchemaName,
String realTableName,
String customSQL)
throws KettleException {
String countStatement = null;
long rowsCount = 0;
boolean successOK = false;
List<Object[]> ar = null;
RowMetaInterface rowMeta = null;
Database db = new Database(this, connection);
db.shareVariablesWith(this);
try {
db.connect(parentJob.getTransactionId(), null);
if (iscustomSQL) {
countStatement = customSQL;
} else {
if (!Const.isEmpty(realSchemaName)) {
countStatement =
selectCount
+ db.getDatabaseMeta()
.getQuotedSchemaTableCombination(realSchemaName, realTableName);
} else {
countStatement = selectCount + db.getDatabaseMeta().quoteField(realTableName);
}
}
if (countStatement != null) {
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(
PKG, "JobEntryWaitForSQL.Log.RunSQLStatement", countStatement));
}
if (iscustomSQL) {
ar = db.getRows(countStatement, 0);
if (ar != null) {
rowsCount = ar.size();
} else {
if (log.isDebug()) {
logDebug(
BaseMessages.getString(
PKG, "JobEntryWaitForSQL.Log.customSQLreturnedNothing", countStatement));
}
}
} else {
RowMetaAndData row = db.getOneRow(countStatement);
if (row != null) {
rowsCount = row.getInteger(0);
}
}
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(PKG, "JobEntryWaitForSQL.Log.NrRowsReturned", "" + rowsCount));
}
switch (successCondition) {
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_EQUAL:
successOK = (rowsCount == nrRowsLimit);
break;
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_DIFFERENT:
successOK = (rowsCount != nrRowsLimit);
break;
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_SMALLER:
successOK = (rowsCount < nrRowsLimit);
break;
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_SMALLER_EQUAL:
successOK = (rowsCount <= nrRowsLimit);
break;
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_GREATER:
successOK = (rowsCount > nrRowsLimit);
break;
case JobEntryWaitForSQL.SUCCESS_CONDITION_ROWS_COUNT_GREATER_EQUAL:
successOK = (rowsCount >= nrRowsLimit);
break;
default:
break;
}
} // end if countStatement!=null
} catch (KettleDatabaseException dbe) {
logError(
BaseMessages.getString(PKG, "JobEntryWaitForSQL.Error.RunningEntry", dbe.getMessage()));
} finally {
if (db != null) {
if (isAddRowsResult && iscustomSQL && ar != null) {
rowMeta = db.getQueryFields(countStatement, false);
}
db.disconnect();
}
}
if (successOK) {
// ad rows to result
if (isAddRowsResult && iscustomSQL && ar != null) {
List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
for (int i = 0; i < ar.size(); i++) {
rows.add(new RowMetaAndData(rowMeta, ar.get(i)));
}
if (rows != null) {
result.getRows().addAll(rows);
}
}
}
return successOK;
}
public SQLStatement getSQLStatements(
TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) {
SQLStatement retval =
new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do!
if (databaseMeta != null) {
if (prev != null && prev.size() > 0) {
if (!Const.isEmpty(tableName)) {
Database db = new Database(databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
String cr_table = db.getDDL(schemaTable, prev, null, false, null, true);
String cr_index = ""; // $NON-NLS-1$
String idx_fields[] = null;
if (keyLookup != null && keyLookup.length > 0) {
idx_fields = new String[keyLookup.length];
for (int i = 0; i < keyLookup.length; i++) idx_fields[i] = keyLookup[i];
} else {
retval.setError(
Messages.getString("DeleteMeta.CheckResult.KeyFieldsRequired")); // $NON-NLS-1$
}
// Key lookup dimensions...
if (idx_fields != null
&& idx_fields.length > 0
&& !db.checkIndexExists(schemaTable, idx_fields)) {
String indexname = "idx_" + tableName + "_lookup"; // $NON-NLS-1$ //$NON-NLS-2$
cr_index =
db.getCreateIndexStatement(
schemaName, tableName, indexname, idx_fields, false, false, false, true);
}
String sql = cr_table + cr_index;
if (sql.length() == 0) retval.setSQL(null);
else retval.setSQL(sql);
} catch (KettleException e) {
retval.setError(
Messages.getString("DeleteMeta.Returnvalue.ErrorOccurred")
+ e.getMessage()); // $NON-NLS-1$
}
} else {
retval.setError(
Messages.getString(
"DeleteMeta.Returnvalue.NoTableDefinedOnConnection")); //$NON-NLS-1$
}
} else {
retval.setError(
Messages.getString("DeleteMeta.Returnvalue.NoReceivingAnyFields")); // $NON-NLS-1$
}
} else {
retval.setError(
Messages.getString("DeleteMeta.Returnvalue.NoConnectionDefined")); // $NON-NLS-1$
}
return retval;
}
public Result execute(Result previousResult, int nr) {
Result result = previousResult;
result.setResult(false);
// see PDI-10270, PDI-10644 for details
boolean oldBehavior =
"Y"
.equalsIgnoreCase(
getVariable(Const.KETTLE_COMPATIBILITY_SET_ERROR_ON_SPECIFIC_JOB_ENTRIES, "N"));
String countSQLStatement = null;
long rowsCount = 0;
long errCount = 0;
boolean successOK = false;
int nrRowsLimit = Const.toInt(environmentSubstitute(limit), 0);
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(
PKG, "JobEntryEvalTableContent.Log.nrRowsLimit", "" + nrRowsLimit));
}
if (connection != null) {
Database db = new Database(this, connection);
db.shareVariablesWith(this);
try {
db.connect(parentJob.getTransactionId(), null);
if (iscustomSQL) {
String realCustomSQL = customSQL;
if (isUseVars) {
realCustomSQL = environmentSubstitute(realCustomSQL);
}
if (log.isDebug()) {
logDebug(
BaseMessages.getString(
PKG, "JobEntryEvalTableContent.Log.EnteredCustomSQL", realCustomSQL));
}
if (!Const.isEmpty(realCustomSQL)) {
countSQLStatement = realCustomSQL;
} else {
errCount++;
logError(BaseMessages.getString(PKG, "JobEntryEvalTableContent.Error.NoCustomSQL"));
}
} else {
String realTablename = environmentSubstitute(tablename);
String realSchemaname = environmentSubstitute(schemaname);
if (!Const.isEmpty(realTablename)) {
if (!Const.isEmpty(realSchemaname)) {
countSQLStatement =
selectCount
+ db.getDatabaseMeta()
.getQuotedSchemaTableCombination(realSchemaname, realTablename);
} else {
countSQLStatement = selectCount + db.getDatabaseMeta().quoteField(realTablename);
}
} else {
errCount++;
logError(BaseMessages.getString(PKG, "JobEntryEvalTableContent.Error.NoTableName"));
}
}
if (countSQLStatement != null) {
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(
PKG, "JobEntryEvalTableContent.Log.RunSQLStatement", countSQLStatement));
}
if (iscustomSQL) {
if (isClearResultList) {
result.getRows().clear();
}
List<Object[]> ar = db.getRows(countSQLStatement, 0);
if (ar != null) {
rowsCount = ar.size();
// ad rows to result
RowMetaInterface rowMeta = db.getQueryFields(countSQLStatement, false);
List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
for (int i = 0; i < ar.size(); i++) {
rows.add(new RowMetaAndData(rowMeta, ar.get(i)));
}
if (isAddRowsResult && iscustomSQL) {
if (rows != null) {
result.getRows().addAll(rows);
}
}
} else {
if (log.isDebug()) {
logDebug(
BaseMessages.getString(
PKG,
"JobEntryEvalTableContent.Log.customSQLreturnedNothing",
countSQLStatement));
}
}
} else {
RowMetaAndData row = db.getOneRow(countSQLStatement);
if (row != null) {
rowsCount = row.getInteger(0);
}
}
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(
PKG, "JobEntryEvalTableContent.Log.NrRowsReturned", "" + rowsCount));
}
switch (successCondition) {
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_EQUAL:
successOK = (rowsCount == nrRowsLimit);
break;
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_DIFFERENT:
successOK = (rowsCount != nrRowsLimit);
break;
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_SMALLER:
successOK = (rowsCount < nrRowsLimit);
break;
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_SMALLER_EQUAL:
successOK = (rowsCount <= nrRowsLimit);
break;
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_GREATER:
successOK = (rowsCount > nrRowsLimit);
break;
case JobEntryEvalTableContent.SUCCESS_CONDITION_ROWS_COUNT_GREATER_EQUAL:
successOK = (rowsCount >= nrRowsLimit);
break;
default:
break;
}
if (!successOK && oldBehavior) {
errCount++;
}
} // end if countSQLStatement!=null
} catch (KettleException dbe) {
errCount++;
logError(
BaseMessages.getString(
PKG, "JobEntryEvalTableContent.Error.RunningEntry", dbe.getMessage()));
} finally {
if (db != null) {
db.disconnect();
}
}
} else {
errCount++;
logError(BaseMessages.getString(PKG, "JobEntryEvalTableContent.NoDbConnection"));
}
result.setResult(successOK);
result.setNrLinesRead(rowsCount);
result.setNrErrors(errCount);
return result;
}
public SQLStatement getSQLStatements(
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
Repository repository,
IMetaStore metaStore) {
SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null); // default:
// nothing
// to
// do!
if (databaseMeta != null) {
if (prev != null && prev.size() > 0) {
if (!Const.isEmpty(tablename)) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(null, tablename);
String cr_table = db.getDDL(schemaTable, prev);
// Squeeze in the VECTORWISE col store clause...
// TODO: move this to the database dialog and make it user
// configurable.
//
String VW_CLAUSE = "WITH STRUCTURE=VECTORWISE";
if (cr_table.toUpperCase().contains("CREATE TABLE")) {
int scIndex = cr_table.indexOf(';');
if (scIndex < 0) {
cr_table += VW_CLAUSE;
} else {
cr_table = cr_table.substring(0, scIndex) + VW_CLAUSE + cr_table.substring(scIndex);
}
}
// Empty string means: nothing to do: set it to null...
if (cr_table == null || cr_table.length() == 0) {
cr_table = null;
}
retval.setSQL(cr_table);
} catch (KettleDatabaseException dbe) {
retval.setError(
BaseMessages.getString(
PKG, "IngresVectorWiseLoaderMeta.Error.ErrorConnecting", dbe.getMessage()));
} finally {
db.disconnect();
}
} else {
retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoTable"));
}
} else {
retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoInput"));
}
} else {
retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoConnection"));
}
return retval;
}
public Result execute(Result previousResult, int nr) {
String LimitNbrLignes = "";
String ListOfColumn = "*";
String strHighPriority = "";
String OutDumpText = "";
String OptionEnclosed = "";
String FieldSeparator = "";
String LinesTerminated = "";
Result result = previousResult;
result.setResult(false);
// Let's check the filename ...
if (filename != null) {
// User has specified a file, We can continue ...
String realFilename = getRealFilename();
File file = new File(realFilename);
if (file.exists() && iffileexists == 2) {
// the file exists and user want to Fail
result.setResult(false);
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
} else if (file.exists() && iffileexists == 1) {
// the file exists and user want to do nothing
result.setResult(true);
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
} else {
if (file.exists() && iffileexists == 0) {
// File exists and user want to renamme it with unique name
// Format Date
// Try to clean filename (without wildcard)
String wildcard =
realFilename.substring(realFilename.length() - 4, realFilename.length());
if (wildcard.substring(0, 1).equals(".")) {
// Find wildcard
realFilename =
realFilename.substring(0, realFilename.length() - 4)
+ "_"
+ StringUtil.getFormattedDateTimeNow(true)
+ wildcard;
} else {
// did not find wildcard
realFilename = realFilename + "_" + StringUtil.getFormattedDateTimeNow(true);
}
logDebug(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileNameChange1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileNameChange1.Label"));
}
// User has specified an existing file, We can continue ...
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
if (connection != null) {
// User has specified a connection, We can continue ...
Database db = new Database(this, connection);
db.shareVariablesWith(this);
try {
db.connect(parentJob.getTransactionId(), null);
// Get schemaname
String realSchemaname = environmentSubstitute(schemaname);
// Get tablename
String realTablename = environmentSubstitute(tablename);
if (db.checkTableExists(realTablename)) {
// The table existe, We can continue ...
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.TableExists1.Label")
+ realTablename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.TableExists2.Label"));
// Add schemaname (Most the time Schemaname.Tablename)
if (schemaname != null) {
realTablename = realSchemaname + "." + realTablename;
}
// Set the Limit lines
if (Const.toInt(getRealLimitlines(), 0) > 0) {
LimitNbrLignes = "LIMIT " + getRealLimitlines();
}
// Set list of Column, if null get all columns (*)
if (getRealListColumn() != null) {
ListOfColumn = MysqlString(getRealListColumn());
}
// Fields separator
if (getRealSeparator() != null && outdumpvalue == 0) {
FieldSeparator =
"FIELDS TERMINATED BY '" + Const.replace(getRealSeparator(), "'", "''") + "'";
}
// Lines Terminated by
if (getRealLineterminated() != null && outdumpvalue == 0) {
LinesTerminated =
"LINES TERMINATED BY '"
+ Const.replace(getRealLineterminated(), "'", "''")
+ "'";
}
// High Priority ?
if (isHighPriority()) {
strHighPriority = "HIGH_PRIORITY";
}
if (getRealEnclosed() != null && outdumpvalue == 0) {
if (isOptionEnclosed()) {
OptionEnclosed = "OPTIONALLY ";
}
OptionEnclosed =
OptionEnclosed
+ "ENCLOSED BY '"
+ Const.replace(getRealEnclosed(), "'", "''")
+ "'";
}
// OutFile or Dumpfile
if (outdumpvalue == 0) {
OutDumpText = "INTO OUTFILE";
} else {
OutDumpText = "INTO DUMPFILE";
}
String FILEBulkFile =
"SELECT "
+ strHighPriority
+ " "
+ ListOfColumn
+ " "
+ OutDumpText
+ " '"
+ realFilename
+ "' "
+ FieldSeparator
+ " "
+ OptionEnclosed
+ " "
+ LinesTerminated
+ " FROM "
+ realTablename
+ " "
+ LimitNbrLignes
+ " LOCK IN SHARE MODE";
try {
if (log.isDetailed()) logDetailed(FILEBulkFile);
// Run the SQL
PreparedStatement ps = db.prepareSQL(FILEBulkFile);
ps.execute();
// Everything is OK...we can disconnect now
db.disconnect();
if (isAddFileToResult()) {
// Add filename to output files
ResultFile resultFile =
new ResultFile(
ResultFile.FILE_TYPE_GENERAL,
KettleVFS.getFileObject(realFilename, this),
parentJob.getJobname(),
toString());
result.getResultFiles().put(resultFile.getFile().toString(), resultFile);
}
result.setResult(true);
} catch (SQLException je) {
db.disconnect();
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label")
+ " "
+ je.getMessage());
} catch (KettleFileException e) {
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label") + e.getMessage());
result.setNrErrors(1);
}
} else {
// Of course, the table should have been created already before the bulk load
// operation
db.disconnect();
result.setNrErrors(1);
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.TableNotExists1.Label")
+ realTablename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.TableNotExists2.Label"));
}
} catch (KettleDatabaseException dbe) {
db.disconnect();
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label")
+ " "
+ dbe.getMessage());
}
} else {
// No database connection is defined
result.setNrErrors(1);
logError(BaseMessages.getString(PKG, "JobMysqlBulkFile.Nodatabase.Label"));
}
}
} else {
// No file was specified
result.setNrErrors(1);
logError(BaseMessages.getString(PKG, "JobMysqlBulkFile.Nofilename.Label"));
}
return result;
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
if (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.TableNameOK"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
// Check fields in table
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(
transMeta.environmentSubstitute(schemaName),
transMeta.environmentSubstitute(tableName));
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.TableExists"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
// How about the fields to insert/dateMask in the table?
first = true;
error_found = false;
error_message = ""; // $NON-NLS-1$
for (int i = 0; i < fieldTable.length; i++) {
String field = fieldTable[i];
ValueMetaInterface v = r.searchValueMeta(field);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsToLoadInTargetTable")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + field + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.AllFieldsFoundInTargetTable"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.CouldNotReadTableInfo"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.StepReceivingDatas", prev.size() + ""),
stepMeta); //$NON-NLS-1$ //$NON-NLS-2$
remarks.add(cr);
boolean first = true;
error_message = ""; // $NON-NLS-1$
boolean error_found = false;
for (int i = 0; i < fieldStream.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(fieldStream[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsInInput")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + fieldStream[i] + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.AllFieldsFoundInInput"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsInInput3")
+ Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.DatabaseErrorOccurred")
+ e.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message =
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.InvalidConnection"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
// See if we have input streams leading to this step!
if (input.length > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.StepReceivingInfoFromOtherSteps"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.NoInputError"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
}
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
String[] input,
String[] output,
RowMetaInterface info,
VariableSpace space,
Repository repository,
IMetaStore metaStore) {
CheckResult cr;
if (databaseMeta != null) {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.ConnectionExists"),
stepMeta);
remarks.add(cr);
Database db = new Database(loggingObject, databaseMeta);
databases = new Database[] {db}; // keep track of it for cancelling purposes...
try {
db.connect();
cr =
new CheckResult(
CheckResult.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.DBConnectionOK"),
stepMeta);
remarks.add(cr);
if (sqlField != null && sqlField.length() != 0) {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.SQLFieldNameEntered"),
stepMeta);
} else {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.SQLFieldNameMissing"),
stepMeta);
}
remarks.add(cr);
} catch (KettleException e) {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.ErrorOccurred")
+ e.getMessage(),
stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.ConnectionNeeded"),
stepMeta);
remarks.add(cr);
}
if (input.length > 0) {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.StepReceivingInfoOK"),
stepMeta);
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResult.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "ExecSQLRowMeta.CheckResult.NoInputReceivedError"),
stepMeta);
remarks.add(cr);
}
}
private void check(
List<CheckResultInterface> remarks,
StepMeta stepMeta,
RowMetaInterface prev,
String[] input,
DatabaseMeta daMeta) {
CheckResult cr;
String error_message;
if (daMeta != null) {
Database db = new Database(daMeta);
try {
db.connect();
if (!Const.isEmpty(tablename)) {
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
String schemaTable = daMeta.getQuotedSchemaTableCombination(schemaName, tablename);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
for (int i = 0; i < keyLookup.length; i++) {
String lufield = keyLookup[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.MissingCompareFields")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.AllFieldsFound"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
/* Also, check the fields: tk, version, from-to, ... */
if (!Const.isEmpty(technicalKeyField) && r.indexOfValue(technicalKeyField) < 0) {
error_message =
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.TechnicalKeyNotFound",
technicalKeyField)
+ Const.CR; // $NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
error_message =
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.TechnicalKeyFound",
technicalKeyField)
+ Const.CR; // $NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_OK, error_message, stepMeta);
}
remarks.add(cr);
} else {
error_message =
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.CouldNotReadTableInfo"); //$NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
boolean first = true;
error_message = ""; // $NON-NLS-1$
boolean error_found = false;
for (int i = 0; i < keyField.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(keyField[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.MissingFields")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + keyField[i] + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.AllFieldsFoundInInputStream"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.CouldNotReadFields")
+ Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
// Check sequence
if (daMeta.supportsSequences() && CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation())) {
if (Const.isEmpty(sequenceFrom)) {
error_message +=
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.ErrorNoSequenceName")
+ "!"; //$NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} else {
// It doesn't make sense to check the sequence name
// if it's not filled in.
if (db.checkSequenceExists(sequenceFrom)) {
error_message =
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.ReadingSequenceOK",
sequenceFrom); //$NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_OK, error_message, stepMeta);
remarks.add(cr);
} else {
error_message +=
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.ErrorReadingSequence")
+ sequenceFrom
+ "!"; //$NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
}
}
if (techKeyCreation != null) {
// post 2.2 version
if (!(CREATION_METHOD_AUTOINC.equals(techKeyCreation)
|| CREATION_METHOD_SEQUENCE.equals(techKeyCreation)
|| CREATION_METHOD_TABLEMAX.equals(techKeyCreation))) {
error_message +=
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.ErrorTechKeyCreation")
+ ": "
+ techKeyCreation
+ "!"; //$NON-NLS-1$ //$NON-NLS-2$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
}
} catch (KettleException e) {
error_message =
Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.ErrorOccurred")
+ e.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message =
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.InvalidConnection"); //$NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
// See if we have input streams leading to this step!
if (input.length > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString(
"ConcurrentCombinationLookupMeta.CheckResult.ReceivingInfoFromOtherSteps"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.NoInputReceived"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
}
}
protected Object[] writeToTable(RowMetaInterface rowMeta, Object[] r) throws KettleException {
if (r == null) { // Stop: last line or error encountered
if (log.isDetailed()) {
logDetailed("Last line inserted: stop");
}
return null;
}
PreparedStatement insertStatement = null;
Object[] insertRowData;
Object[] outputRowData = r;
String tableName = null;
boolean sendToErrorRow = false;
String errorMessage = null;
boolean rowIsSafe = false;
int[] updateCounts = null;
List<Exception> exceptionsList = null;
boolean batchProblem = false;
Object generatedKey = null;
if (meta.isTableNameInField()) {
// Cache the position of the table name field
if (data.indexOfTableNameField < 0) {
String realTablename = environmentSubstitute(meta.getTableNameField());
data.indexOfTableNameField = rowMeta.indexOfValue(realTablename);
if (data.indexOfTableNameField < 0) {
String message = "Unable to find table name field [" + realTablename + "] in input row";
logError(message);
throw new KettleStepException(message);
}
if (!meta.isTableNameInTable() && !meta.specifyFields()) {
data.insertRowMeta.removeValueMeta(data.indexOfTableNameField);
}
}
tableName = rowMeta.getString(r, data.indexOfTableNameField);
if (!meta.isTableNameInTable() && !meta.specifyFields()) {
// If the name of the table should not be inserted itself, remove the table name
// from the input row data as well. This forcibly creates a copy of r
//
insertRowData = RowDataUtil.removeItem(rowMeta.cloneRow(r), data.indexOfTableNameField);
} else {
insertRowData = r;
}
} else if (meta.isPartitioningEnabled()
&& (meta.isPartitioningDaily() || meta.isPartitioningMonthly())
&& (meta.getPartitioningField() != null && meta.getPartitioningField().length() > 0)) {
// Initialize some stuff!
if (data.indexOfPartitioningField < 0) {
data.indexOfPartitioningField =
rowMeta.indexOfValue(environmentSubstitute(meta.getPartitioningField()));
if (data.indexOfPartitioningField < 0) {
throw new KettleStepException(
"Unable to find field [" + meta.getPartitioningField() + "] in the input row!");
}
if (meta.isPartitioningDaily()) {
data.dateFormater = new SimpleDateFormat("yyyyMMdd");
} else {
data.dateFormater = new SimpleDateFormat("yyyyMM");
}
}
ValueMetaInterface partitioningValue = rowMeta.getValueMeta(data.indexOfPartitioningField);
if (!partitioningValue.isDate() || r[data.indexOfPartitioningField] == null) {
throw new KettleStepException(
"Sorry, the partitioning field needs to contain a data value and can't be empty!");
}
Object partitioningValueData = rowMeta.getDate(r, data.indexOfPartitioningField);
tableName =
environmentSubstitute(meta.getTableName())
+ "_"
+ data.dateFormater.format((Date) partitioningValueData);
insertRowData = r;
} else {
tableName = data.tableName;
insertRowData = r;
}
if (meta.specifyFields()) {
//
// The values to insert are those in the fields sections
//
insertRowData = new Object[data.valuenrs.length];
for (int idx = 0; idx < data.valuenrs.length; idx++) {
insertRowData[idx] = r[data.valuenrs[idx]];
}
}
if (Const.isEmpty(tableName)) {
throw new KettleStepException("The tablename is not defined (empty)");
}
insertStatement = data.preparedStatements.get(tableName);
if (insertStatement == null) {
String sql =
data.db.getInsertStatement(
environmentSubstitute(meta.getSchemaName()), tableName, data.insertRowMeta);
if (log.isDetailed()) {
logDetailed("Prepared statement : " + sql);
}
insertStatement = data.db.prepareSQL(sql, meta.isReturningGeneratedKeys());
data.preparedStatements.put(tableName, insertStatement);
}
try {
// For PG & GP, we add a savepoint before the row.
// Then revert to the savepoint afterwards... (not a transaction, so hopefully still fast)
//
if (data.useSafePoints) {
data.savepoint = data.db.setSavepoint();
}
data.db.setValues(data.insertRowMeta, insertRowData, insertStatement);
data.db.insertRow(
insertStatement, data.batchMode, false); // false: no commit, it is handled in this step
// different
if (isRowLevel()) {
logRowlevel("Written row: " + data.insertRowMeta.getString(insertRowData));
}
// Get a commit counter per prepared statement to keep track of separate tables, etc.
//
Integer commitCounter = data.commitCounterMap.get(tableName);
if (commitCounter == null) {
commitCounter = Integer.valueOf(1);
} else {
commitCounter++;
}
data.commitCounterMap.put(tableName, Integer.valueOf(commitCounter.intValue()));
// Release the savepoint if needed
//
if (data.useSafePoints) {
if (data.releaseSavepoint) {
data.db.releaseSavepoint(data.savepoint);
}
}
// Perform a commit if needed
//
if ((data.commitSize > 0) && ((commitCounter % data.commitSize) == 0)) {
if (data.db.getUseBatchInsert(data.batchMode)) {
try {
insertStatement.executeBatch();
data.db.commit();
insertStatement.clearBatch();
} catch (SQLException ex) {
throw Database.createKettleDatabaseBatchException("Error updating batch", ex);
} catch (Exception ex) {
throw new KettleDatabaseException("Unexpected error inserting row", ex);
}
} else {
// insertRow normal commit
data.db.commit();
}
// Clear the batch/commit counter...
//
data.commitCounterMap.put(tableName, Integer.valueOf(0));
rowIsSafe = true;
} else {
rowIsSafe = false;
}
// See if we need to get back the keys as well...
if (meta.isReturningGeneratedKeys()) {
RowMetaAndData extraKeys = data.db.getGeneratedKeys(insertStatement);
if (extraKeys.getRowMeta().size() > 0) {
// Send out the good word!
// Only 1 key at the moment. (should be enough for now :-)
generatedKey = extraKeys.getRowMeta().getInteger(extraKeys.getData(), 0);
} else {
// we have to throw something here, else we don't know what the
// type is of the returned key(s) and we would violate our own rule
// that a hop should always contain rows of the same type.
throw new KettleStepException(
"No generated keys while \"return generated keys\" is active!");
}
}
} catch (KettleDatabaseBatchException be) {
errorMessage = be.toString();
batchProblem = true;
sendToErrorRow = true;
updateCounts = be.getUpdateCounts();
exceptionsList = be.getExceptionsList();
if (getStepMeta().isDoingErrorHandling()) {
data.db.clearBatch(insertStatement);
data.db.commit(true);
} else {
data.db.clearBatch(insertStatement);
data.db.rollback();
StringBuilder msg =
new StringBuilder("Error batch inserting rows into table [" + tableName + "].");
msg.append(Const.CR);
msg.append("Errors encountered (first 10):").append(Const.CR);
for (int x = 0; x < be.getExceptionsList().size() && x < 10; x++) {
Exception exception = be.getExceptionsList().get(x);
if (exception.getMessage() != null) {
msg.append(exception.getMessage()).append(Const.CR);
}
}
throw new KettleException(msg.toString(), be);
}
} catch (KettleDatabaseException dbe) {
if (getStepMeta().isDoingErrorHandling()) {
if (isRowLevel()) {
logRowlevel("Written row to error handling : " + getInputRowMeta().getString(r));
}
if (data.useSafePoints) {
data.db.rollback(data.savepoint);
if (data.releaseSavepoint) {
data.db.releaseSavepoint(data.savepoint);
}
// data.db.commit(true); // force a commit on the connection too.
}
sendToErrorRow = true;
errorMessage = dbe.toString();
} else {
if (meta.ignoreErrors()) {
if (data.warnings < 20) {
if (log.isBasic()) {
logBasic(
"WARNING: Couldn't insert row into table: "
+ rowMeta.getString(r)
+ Const.CR
+ dbe.getMessage());
}
} else if (data.warnings == 20) {
if (log.isBasic()) {
logBasic(
"FINAL WARNING (no more then 20 displayed): Couldn't insert row into table: "
+ rowMeta.getString(r)
+ Const.CR
+ dbe.getMessage());
}
}
data.warnings++;
} else {
setErrors(getErrors() + 1);
data.db.rollback();
throw new KettleException(
"Error inserting row into table ["
+ tableName
+ "] with values: "
+ rowMeta.getString(r),
dbe);
}
}
}
// We need to add a key
if (generatedKey != null) {
outputRowData = RowDataUtil.addValueData(outputRowData, rowMeta.size(), generatedKey);
}
if (data.batchMode) {
if (sendToErrorRow) {
if (batchProblem) {
data.batchBuffer.add(outputRowData);
outputRowData = null;
processBatchException(errorMessage, updateCounts, exceptionsList);
} else {
// Simply add this row to the error row
putError(rowMeta, r, 1L, errorMessage, null, "TOP001");
outputRowData = null;
}
} else {
data.batchBuffer.add(outputRowData);
outputRowData = null;
if (rowIsSafe) { // A commit was done and the rows are all safe (no error)
for (int i = 0; i < data.batchBuffer.size(); i++) {
Object[] row = data.batchBuffer.get(i);
putRow(data.outputRowMeta, row);
incrementLinesOutput();
}
// Clear the buffer
data.batchBuffer.clear();
}
}
} else {
if (sendToErrorRow) {
putError(rowMeta, r, 1, errorMessage, null, "TOP001");
outputRowData = null;
}
}
return outputRowData;
}
