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; } } } }
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 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 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 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) { 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 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; }
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); } }
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); } }
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; }
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, 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 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; }