Examples with SQLStatement org.pentaho.di.core.SQLStatement used on opensource projects

Example 26 with SQLStatement

use of org.pentaho.di.core.SQLStatement in project pentaho-kettle by pentaho.

the class TableOutputMeta method getSQLStatements.

public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, String tk, boolean use_autoinc, String pk) {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    if (databaseMeta != null) {
        if (prev != null && prev.size() > 0) {
            if (!Utils.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, prev, tk, use_autoinc, pk);
                    // 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, "TableOutputMeta.Error.ErrorConnecting", dbe.getMessage()));
                } finally {
                    db.disconnect();
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "TableOutputMeta.Error.NoTable"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "TableOutputMeta.Error.NoInput"));
        }
    } else {
        retval.setError(BaseMessages.getString(PKG, "TableOutputMeta.Error.NoConnection"));
    }
    return retval;
}

Example 27 with SQLStatement

use of org.pentaho.di.core.SQLStatement in project pentaho-kettle by pentaho.

the class OraBulkLoaderMeta method getSQLStatements.

public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) throws KettleStepException {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    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 (!Utils.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, "OraBulkLoaderMeta.GetSQL.ErrorOccurred") + e.getMessage());
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "OraBulkLoaderMeta.GetSQL.NoTableDefinedOnConnection"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "OraBulkLoaderMeta.GetSQL.NotReceivingAnyFields"));
        }
    } else {
        retval.setError(BaseMessages.getString(PKG, "OraBulkLoaderMeta.GetSQL.NoConnectionDefined"));
    }
    return retval;
}

Example 28 with SQLStatement

use of org.pentaho.di.core.SQLStatement in project pentaho-kettle by pentaho.

the class DimensionLookupMeta method getSQLStatements.

@Override
public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    if (update) {
        // Only bother in case of update, not lookup!
        logDebug(BaseMessages.getString(PKG, "DimensionLookupMeta.Log.Update"));
        if (databaseMeta != null) {
            if (prev != null && prev.size() > 0) {
                String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
                if (!Utils.isEmpty(schemaTable)) {
                    Database db = createDatabaseObject();
                    db.shareVariablesWith(transMeta);
                    try {
                        db.connect();
                        String sql = "";
                        // How does the table look like?
                        // 
                        RowMetaInterface fields = new RowMeta();
                        // First the technical key
                        // 
                        ValueMetaInterface vkeyfield = new ValueMetaInteger(keyField);
                        vkeyfield.setLength(10);
                        fields.addValueMeta(vkeyfield);
                        // The the version
                        // 
                        ValueMetaInterface vversion = new ValueMetaInteger(versionField);
                        vversion.setLength(5);
                        fields.addValueMeta(vversion);
                        // The date from
                        // 
                        ValueMetaInterface vdatefrom = new ValueMetaDate(dateFrom);
                        fields.addValueMeta(vdatefrom);
                        // The date to
                        // 
                        ValueMetaInterface vdateto = new ValueMetaDate(dateTo);
                        fields.addValueMeta(vdateto);
                        String errors = "";
                        // 
                        for (int i = 0; i < keyLookup.length; i++) {
                            ValueMetaInterface vprev = prev.searchValueMeta(keyStream[i]);
                            if (vprev != null) {
                                ValueMetaInterface field = vprev.clone();
                                field.setName(keyLookup[i]);
                                fields.addValueMeta(field);
                            } else {
                                if (errors.length() > 0) {
                                    errors += ", ";
                                }
                                errors += keyStream[i];
                            }
                        }
                        // 
                        for (int i = 0; i < fieldLookup.length; i++) {
                            ValueMetaInterface vprev = prev.searchValueMeta(fieldStream[i]);
                            if (vprev != null) {
                                ValueMetaInterface field = vprev.clone();
                                field.setName(fieldLookup[i]);
                                fields.addValueMeta(field);
                            } else {
                                if (errors.length() > 0) {
                                    errors += ", ";
                                }
                                errors += fieldStream[i];
                            }
                        }
                        // 
                        for (int i = 0; i < fieldUpdate.length; i++) {
                            ValueMetaInterface valueMeta = null;
                            switch(fieldUpdate[i]) {
                                case TYPE_UPDATE_DATE_INSUP:
                                case TYPE_UPDATE_DATE_INSERTED:
                                case TYPE_UPDATE_DATE_UPDATED:
                                    valueMeta = new ValueMetaDate(fieldLookup[i]);
                                    break;
                                case TYPE_UPDATE_LAST_VERSION:
                                    valueMeta = new ValueMetaBoolean(fieldLookup[i]);
                                    break;
                                default:
                                    break;
                            }
                            if (valueMeta != null) {
                                fields.addValueMeta(valueMeta);
                            }
                        }
                        if (errors.length() > 0) {
                            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.UnableToFindFields") + errors);
                        }
                        logDebug(BaseMessages.getString(PKG, "DimensionLookupMeta.Log.GetDDLForTable") + schemaTable + "] : " + fields.toStringMeta());
                        sql += db.getDDL(schemaTable, fields, (sequenceName != null && sequenceName.length() != 0) ? null : keyField, autoIncrement, null, true);
                        logDebug("sql =" + sql);
                        String[] idx_fields = null;
                        // Key lookup dimensions...
                        if (!Utils.isEmpty(keyLookup)) {
                            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, "DimensionLookupMeta.ReturnValue.NoKeyFieldsSpecified"));
                        }
                        if (!Utils.isEmpty(idx_fields) && !db.checkIndexExists(schemaTable, idx_fields)) {
                            String indexname = "idx_" + tableName + "_lookup";
                            sql += db.getCreateIndexStatement(schemaTable, indexname, idx_fields, false, false, false, true);
                        }
                        // (Bitmap) index on technical key
                        idx_fields = new String[] { keyField };
                        if (!Utils.isEmpty(keyField)) {
                            if (!db.checkIndexExists(schemaTable, idx_fields)) {
                                String indexname = "idx_" + tableName + "_tk";
                                sql += db.getCreateIndexStatement(schemaTable, indexname, idx_fields, true, false, true, true);
                            }
                        } else {
                            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.TechnicalKeyFieldRequired"));
                        }
                        // The optional Oracle sequence
                        if (CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation()) && !Utils.isEmpty(sequenceName)) {
                            if (!db.checkSequenceExists(schemaName, sequenceName)) {
                                sql += db.getCreateSequenceStatement(schemaName, sequenceName, 1L, 1L, -1L, true);
                            }
                        }
                        if (sql.length() == 0) {
                            retval.setSQL(null);
                        } else {
                            retval.setSQL(transMeta.environmentSubstitute(sql));
                        }
                    } catch (KettleDatabaseException dbe) {
                        retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.ErrorOccurred") + dbe.getMessage());
                    } finally {
                        db.disconnect();
                    }
                } else {
                    retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NoTableDefinedOnConnection"));
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NotReceivingAnyFields"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NoConnectionDefiendInStep"));
        }
    }
    return retval;
}

Example 29 with SQLStatement

use of org.pentaho.di.core.SQLStatement in project pentaho-kettle by pentaho.

the class MonetDBBulkLoaderMeta method getSQLStatements.

public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, boolean autoSchema, MonetDBBulkLoaderData data, boolean safeMode) throws KettleStepException {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    if (databaseMeta != null) {
        if (prev != null && prev.size() > 0) {
            // Copy the row
            RowMetaInterface tableFields;
            if (autoSchema) {
                tableFields = updateFields(prev, data);
            } else {
                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);
                    }
                }
            }
            if (!Utils.isEmpty(tableName)) {
                Database db = new Database(loggingObject, databaseMeta);
                db.shareVariablesWith(transMeta);
                try {
                    db.connect();
                    String schemaTable = databaseMeta.getQuotedSchemaTableCombination(transMeta.environmentSubstitute(schemaName), transMeta.environmentSubstitute(tableName));
                    MonetDBDatabaseMeta.safeModeLocal.set(safeMode);
                    String cr_table = db.getDDL(schemaTable, tableFields, null, false, null, true);
                    String sql = cr_table;
                    if (sql.length() == 0) {
                        retval.setSQL(null);
                    } else {
                        retval.setSQL(sql);
                    }
                } catch (KettleException e) {
                    retval.setError(BaseMessages.getString(PKG, "MonetDBBulkLoaderMeta.GetSQL.ErrorOccurred") + e.getMessage());
                } finally {
                    db.disconnect();
                    MonetDBDatabaseMeta.safeModeLocal.remove();
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "MonetDBBulkLoaderMeta.GetSQL.NoTableDefinedOnConnection"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "MonetDBBulkLoaderMeta.GetSQL.NotReceivingAnyFields"));
        }
    } else {
        retval.setError(BaseMessages.getString(PKG, "MonetDBBulkLoaderMeta.GetSQL.NoConnectionDefined"));
    }
    return retval;
}

Example 30 with SQLStatement

use of org.pentaho.di.core.SQLStatement in project pentaho-kettle by pentaho.

the class MonetDBBulkLoaderMeta method getTableDdl.

public SQLStatement getTableDdl(TransMeta transMeta, String stepname, boolean autoSchema, MonetDBBulkLoaderData data, boolean safeMode) throws KettleException {
    // new name might not yet be linked to other steps!
    String name = stepname;
    StepMeta stepMeta = new StepMeta(BaseMessages.getString(PKG, "MonetDBBulkLoaderDialog.StepMeta.Title"), name, this);
    RowMetaInterface prev = transMeta.getPrevStepFields(stepname);
    SQLStatement sql = getSQLStatements(transMeta, stepMeta, prev, autoSchema, data, safeMode);
    return sql;
}