/** Stop the running query */
public void stopRunning(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
meta = (DatabaseLookupMeta) smi;
data = (DatabaseLookupData) sdi;
if (data.db != null && !data.isCanceled) {
synchronized (data.db) {
data.db.cancelQuery();
}
data.isCanceled = true;
}
}
public void dispose(StepMetaInterface smi, StepDataInterface sdi) {
meta = (DatabaseLookupMeta) smi;
data = (DatabaseLookupData) sdi;
if (data.db != null) {
data.db.disconnect();
}
// Recover memory immediately, allow in-memory data to be garbage collected
//
data.look = null;
super.dispose(smi, sdi);
}
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
meta = (DatabaseLookupMeta) smi;
data = (DatabaseLookupData) sdi;
if (super.init(smi, sdi)) {
if (meta.getDatabaseMeta() == null) {
logError(
BaseMessages.getString(PKG, "DatabaseLookup.Init.ConnectionMissing", getStepname()));
return false;
}
data.db = new Database(this, meta.getDatabaseMeta());
data.db.shareVariablesWith(this);
try {
if (getTransMeta().isUsingUniqueConnections()) {
synchronized (getTrans()) {
data.db.connect(getTrans().getTransactionId(), getPartitionID());
}
} else {
data.db.connect(getPartitionID());
}
data.db.setCommit(100); // we never get a commit, but it just turns off auto-commit.
if (log.isDetailed()) {
logDetailed(BaseMessages.getString(PKG, "DatabaseLookup.Log.ConnectedToDatabase"));
}
// See if all the lookup conditions are "equal"
// This might speed up things in the case when we load all data in the cache
//
data.allEquals = true;
data.hasDBCondition = false;
data.conditions = new int[meta.getKeyCondition().length];
for (int i = 0; i < meta.getKeyCondition().length; i++) {
data.conditions[i] =
Const.indexOfString(meta.getKeyCondition()[i], DatabaseLookupMeta.conditionStrings);
if (!("=".equals(meta.getKeyCondition()[i]))) {
data.allEquals = false;
}
if (data.conditions[i] == DatabaseLookupMeta.CONDITION_LIKE) {
data.hasDBCondition = true;
}
}
return true;
} catch (Exception e) {
logError(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR0004.UnexpectedErrorDuringInit")
+ e.toString());
if (data.db != null) {
data.db.disconnect();
}
}
}
return false;
}
public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
meta = (DatabaseLookupMeta) smi;
data = (DatabaseLookupData) sdi;
boolean sendToErrorRow = false;
String errorMessage = null;
Object[] r = getRow(); // Get row from input rowset & set row busy!
if (r == null) { // no more input to be expected...
setOutputDone();
return false;
}
if (first) {
first = false;
// create the output metadata
data.outputRowMeta = getInputRowMeta().clone();
meta.getFields(data.outputRowMeta, getStepname(), null, null, this, repository, metaStore);
if (meta.isCached()) {
if (meta.getCacheSize() > 0) {
data.look = new Hashtable<RowMetaAndData, TimedRow>((int) (meta.getCacheSize() * 1.5));
} else {
data.look = new Hashtable<RowMetaAndData, TimedRow>();
}
}
data.db.setLookup(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTablename()),
meta.getTableKeyField(),
meta.getKeyCondition(),
meta.getReturnValueField(),
meta.getReturnValueNewName(),
meta.getOrderByClause(),
meta.isFailingOnMultipleResults());
// lookup the values!
if (log.isDetailed()) {
logDetailed(
BaseMessages.getString(PKG, "DatabaseLookup.Log.CheckingRow")
+ getInputRowMeta().getString(r));
}
data.keynrs = new int[meta.getStreamKeyField1().length];
data.keynrs2 = new int[meta.getStreamKeyField1().length];
for (int i = 0; i < meta.getStreamKeyField1().length; i++) {
data.keynrs[i] = getInputRowMeta().indexOfValue(meta.getStreamKeyField1()[i]);
if (data.keynrs[i] < 0
&& // couldn't find field!
!"IS NULL".equalsIgnoreCase(meta.getKeyCondition()[i])
&& // No field needed!
!"IS NOT NULL".equalsIgnoreCase(meta.getKeyCondition()[i]) // No field needed!
) {
throw new KettleStepException(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR0001.FieldRequired1.Exception")
+ meta.getStreamKeyField1()[i]
+ BaseMessages.getString(
PKG, "DatabaseLookup.ERROR0001.FieldRequired2.Exception"));
}
data.keynrs2[i] = getInputRowMeta().indexOfValue(meta.getStreamKeyField2()[i]);
if (data.keynrs2[i] < 0
&& // couldn't find field!
"BETWEEN".equalsIgnoreCase(meta.getKeyCondition()[i]) // 2 fields needed!
) {
throw new KettleStepException(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR0001.FieldRequired3.Exception")
+ meta.getStreamKeyField2()[i]
+ BaseMessages.getString(
PKG, "DatabaseLookup.ERROR0001.FieldRequired4.Exception"));
}
if (log.isDebug()) {
logDebug(
BaseMessages.getString(PKG, "DatabaseLookup.Log.FieldHasIndex1")
+ meta.getStreamKeyField1()[i]
+ BaseMessages.getString(PKG, "DatabaseLookup.Log.FieldHasIndex2")
+ data.keynrs[i]);
}
}
data.nullif = new Object[meta.getReturnValueField().length];
for (int i = 0; i < meta.getReturnValueField().length; i++) {
ValueMetaInterface stringMeta = new ValueMeta("string", ValueMetaInterface.TYPE_STRING);
ValueMetaInterface returnMeta =
data.outputRowMeta.getValueMeta(i + getInputRowMeta().size());
if (!Const.isEmpty(meta.getReturnValueDefault()[i])) {
data.nullif[i] = returnMeta.convertData(stringMeta, meta.getReturnValueDefault()[i]);
} else {
data.nullif[i] = null;
}
}
// Determine the types...
data.keytypes = new int[meta.getTableKeyField().length];
String schemaTable =
meta.getDatabaseMeta()
.getQuotedSchemaTableCombination(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTablename()));
RowMetaInterface fields = data.db.getTableFields(schemaTable);
if (fields != null) {
// Fill in the types...
for (int i = 0; i < meta.getTableKeyField().length; i++) {
ValueMetaInterface key = fields.searchValueMeta(meta.getTableKeyField()[i]);
if (key != null) {
data.keytypes[i] = key.getType();
} else {
throw new KettleStepException(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR0001.FieldRequired5.Exception")
+ meta.getTableKeyField()[i]
+ BaseMessages.getString(
PKG, "DatabaseLookup.ERROR0001.FieldRequired6.Exception"));
}
}
} else {
throw new KettleStepException(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR0002.UnableToDetermineFieldsOfTable")
+ schemaTable
+ "]");
}
// Count the number of values in the lookup as well as the metadata to send along with it.
//
data.lookupMeta = new RowMeta();
for (int i = 0; i < meta.getStreamKeyField1().length; i++) {
if (data.keynrs[i] >= 0) {
ValueMetaInterface inputValueMeta = getInputRowMeta().getValueMeta(data.keynrs[i]);
// Try to convert type if needed in a clone, we don't want to
// change the type in the original row
//
ValueMetaInterface value =
ValueMetaFactory.cloneValueMeta(inputValueMeta, data.keytypes[i]);
data.lookupMeta.addValueMeta(value);
}
if (data.keynrs2[i] >= 0) {
ValueMetaInterface inputValueMeta = getInputRowMeta().getValueMeta(data.keynrs2[i]);
// Try to convert type if needed in a clone, we don't want to
// change the type in the original row
//
ValueMetaInterface value =
ValueMetaFactory.cloneValueMeta(inputValueMeta, data.keytypes[i]);
data.lookupMeta.addValueMeta(value);
}
}
// We also want to know the metadata of the return values beforehand (null handling)
data.returnMeta = new RowMeta();
for (int i = 0; i < meta.getReturnValueField().length; i++) {
ValueMetaInterface v =
data.outputRowMeta.getValueMeta(getInputRowMeta().size() + i).clone();
data.returnMeta.addValueMeta(v);
}
// If the user selected to load all data into the cache at startup, that's what we do now...
//
if (meta.isCached() && meta.isLoadingAllDataInCache()) {
loadAllTableDataIntoTheCache();
}
}
if (log.isRowLevel()) {
logRowlevel(
BaseMessages.getString(PKG, "DatabaseLookup.Log.GotRowFromPreviousStep")
+ getInputRowMeta().getString(r));
}
try {
// add new lookup values to the row
Object[] outputRow = lookupValues(getInputRowMeta(), r);
if (outputRow != null) {
// copy row to output rowset(s);
putRow(data.outputRowMeta, outputRow);
if (log.isRowLevel()) {
logRowlevel(
BaseMessages.getString(PKG, "DatabaseLookup.Log.WroteRowToNextStep")
+ getInputRowMeta().getString(r));
}
if (checkFeedback(getLinesRead())) {
logBasic("linenr " + getLinesRead());
}
}
} catch (KettleException e) {
if (getStepMeta().isDoingErrorHandling()) {
sendToErrorRow = true;
errorMessage = e.toString();
} else {
logError(
BaseMessages.getString(PKG, "DatabaseLookup.ERROR003.UnexpectedErrorDuringProcessing")
+ e.getMessage());
setErrors(1);
stopAll();
setOutputDone(); // signal end to receiver(s)
return false;
}
if (sendToErrorRow) {
// Simply add this row to the error row
putError(getInputRowMeta(), r, 1, errorMessage, null, "DBLOOKUPD001");
}
}
return true;
}
private Object[] getRowFromCache(RowMetaInterface lookupMeta, Object[] lookupRow)
throws KettleException {
if (data.allEquals) {
// only do the hashtable lookup when all equals otherwise conditions >, <, <> will give wrong
// results
TimedRow timedRow = data.look.get(new RowMetaAndData(data.lookupMeta, lookupRow));
if (timedRow != null) {
return timedRow.getRow();
}
} else { // special handling of conditions <,>, <> etc.
if (!data.hasDBCondition) { // e.g. LIKE not handled by this routine, yet
// TODO: find an alternative way to look up the data based on the condition.
// Not all conditions are "=" so we are going to have to evaluate row by row
// A sorted list or index might be a good solution here...
//
Enumeration<RowMetaAndData> keys = data.look.keys();
while (keys.hasMoreElements()) {
RowMetaAndData key = keys.nextElement();
// Now verify that the key is matching our conditions...
//
boolean match = true;
int lookupIndex = 0;
for (int i = 0; i < data.conditions.length && match; i++) {
ValueMetaInterface cmpMeta = lookupMeta.getValueMeta(lookupIndex);
Object cmpData = lookupRow[lookupIndex];
ValueMetaInterface keyMeta = key.getValueMeta(i);
Object keyData = key.getData()[i];
switch (data.conditions[i]) {
case DatabaseLookupMeta.CONDITION_EQ:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) == 0);
break;
case DatabaseLookupMeta.CONDITION_NE:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) != 0);
break;
case DatabaseLookupMeta.CONDITION_LT:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) > 0);
break;
case DatabaseLookupMeta.CONDITION_LE:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) >= 0);
break;
case DatabaseLookupMeta.CONDITION_GT:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) < 0);
break;
case DatabaseLookupMeta.CONDITION_GE:
match = (cmpMeta.compare(cmpData, keyMeta, keyData) <= 0);
break;
case DatabaseLookupMeta.CONDITION_IS_NULL:
match = keyMeta.isNull(keyData);
break;
case DatabaseLookupMeta.CONDITION_IS_NOT_NULL:
match = !keyMeta.isNull(keyData);
break;
case DatabaseLookupMeta.CONDITION_BETWEEN:
// Between key >= cmp && key <= cmp2
ValueMetaInterface cmpMeta2 = lookupMeta.getValueMeta(lookupIndex + 1);
Object cmpData2 = lookupRow[lookupIndex + 1];
match = (keyMeta.compare(keyData, cmpMeta, cmpData) >= 0);
if (match) {
match = (keyMeta.compare(keyData, cmpMeta2, cmpData2) <= 0);
}
lookupIndex++;
break;
// TODO: add LIKE operator (think of changing the hasDBCondition logic then)
default:
match = false;
data.hasDBCondition =
true; // avoid looping in here the next time, also safety when a new condition
// will be introduced
break;
}
lookupIndex++;
}
if (match) {
TimedRow timedRow = data.look.get(key);
if (timedRow != null) {
return timedRow.getRow();
}
}
}
}
}
return null;
}
