Examples with DatabaseMeta org.pentaho.di.core.database.DatabaseMeta used on opensource projects

Example 56 with DatabaseMeta

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

the class InsertUpdate method prepareUpdate.

// Lookup certain fields in a table
public void prepareUpdate(RowMetaInterface rowMeta) throws KettleDatabaseException {
    DatabaseMeta databaseMeta = meta.getDatabaseMeta();
    data.updateParameterRowMeta = new RowMeta();
    String sql = "UPDATE " + data.schemaTable + Const.CR;
    sql += "SET ";
    boolean comma = false;
    for (int i = 0; i < meta.getUpdateLookup().length; i++) {
        if (meta.getUpdate()[i].booleanValue()) {
            if (comma) {
                sql += ",   ";
            } else {
                comma = true;
            }
            sql += databaseMeta.quoteField(meta.getUpdateLookup()[i]);
            sql += " = ?" + Const.CR;
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getUpdateStream()[i]).clone());
        }
    }
    sql += "WHERE ";
    for (int i = 0; i < meta.getKeyLookup().length; i++) {
        if (i != 0) {
            sql += "AND   ";
        }
        sql += " ( ( ";
        sql += databaseMeta.quoteField(meta.getKeyLookup()[i]);
        if ("BETWEEN".equalsIgnoreCase(meta.getKeyCondition()[i])) {
            sql += " BETWEEN ? AND ? ";
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]));
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream2()[i]));
        } else if ("IS NULL".equalsIgnoreCase(meta.getKeyCondition()[i]) || "IS NOT NULL".equalsIgnoreCase(meta.getKeyCondition()[i])) {
            sql += " " + meta.getKeyCondition()[i] + " ";
        } else if ("= ~NULL".equalsIgnoreCase(meta.getKeyCondition()[i])) {
            sql += " IS NULL AND ";
            if (databaseMeta.requiresCastToVariousForIsNull()) {
                sql += "CAST(? AS VARCHAR(256)) IS NULL";
            } else {
                sql += "? IS NULL";
            }
            // null check
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]));
            sql += " ) OR ( " + databaseMeta.quoteField(meta.getKeyLookup()[i]) + " = ?";
            // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]).clone());
        } else {
            sql += " " + meta.getKeyCondition()[i] + " ? ";
            data.updateParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]).clone());
        }
        sql += " ) ) ";
    }
    try {
        if (log.isDetailed()) {
            logDetailed("Setting update preparedStatement to [" + sql + "]");
        }
        data.prepStatementUpdate = data.db.getConnection().prepareStatement(databaseMeta.stripCR(sql));
    } catch (SQLException ex) {
        throw new KettleDatabaseException("Unable to prepare statement for SQL statement [" + sql + "]", ex);
    }
}

Example 57 with DatabaseMeta

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

the class InsertUpdate method setLookup.

public void setLookup(RowMetaInterface rowMeta) throws KettleDatabaseException {
    data.lookupParameterRowMeta = new RowMeta();
    data.lookupReturnRowMeta = new RowMeta();
    DatabaseMeta databaseMeta = meta.getDatabaseMeta();
    String sql = "SELECT ";
    for (int i = 0; i < meta.getUpdateLookup().length; i++) {
        if (i != 0) {
            sql += ", ";
        }
        sql += databaseMeta.quoteField(meta.getUpdateLookup()[i]);
        data.lookupReturnRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getUpdateStream()[i]).clone());
    }
    sql += " FROM " + data.schemaTable + " WHERE ";
    for (int i = 0; i < meta.getKeyLookup().length; i++) {
        if (i != 0) {
            sql += " AND ";
        }
        sql += " ( ( ";
        sql += databaseMeta.quoteField(meta.getKeyLookup()[i]);
        if ("BETWEEN".equalsIgnoreCase(meta.getKeyCondition()[i])) {
            sql += " BETWEEN ? AND ? ";
            data.lookupParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]));
            data.lookupParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream2()[i]));
        } else {
            if ("IS NULL".equalsIgnoreCase(meta.getKeyCondition()[i]) || "IS NOT NULL".equalsIgnoreCase(meta.getKeyCondition()[i])) {
                sql += " " + meta.getKeyCondition()[i] + " ";
            } else if ("= ~NULL".equalsIgnoreCase(meta.getKeyCondition()[i])) {
                sql += " IS NULL AND ";
                if (databaseMeta.requiresCastToVariousForIsNull()) {
                    sql += " CAST(? AS VARCHAR(256)) IS NULL ";
                } else {
                    sql += " ? IS NULL ";
                }
                // null check
                data.lookupParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]));
                sql += " ) OR ( " + databaseMeta.quoteField(meta.getKeyLookup()[i]) + " = ? ";
                // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
                data.lookupParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]).clone());
            } else {
                sql += " " + meta.getKeyCondition()[i] + " ? ";
                data.lookupParameterRowMeta.addValueMeta(rowMeta.searchValueMeta(meta.getKeyStream()[i]));
            }
        }
        sql += " ) ) ";
    }
    try {
        if (log.isDetailed()) {
            logDetailed("Setting preparedStatement to [" + sql + "]");
        }
        data.prepStatementLookup = data.db.getConnection().prepareStatement(databaseMeta.stripCR(sql));
    } catch (SQLException ex) {
        throw new KettleDatabaseException("Unable to prepare statement for SQL statement [" + sql + "]", ex);
    }
}

Example 58 with DatabaseMeta

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

the class DatabaseMetaLoadSaveValidator method getTestObject.

@Override
public DatabaseMeta getTestObject() {
    DatabaseMeta db = new DatabaseMeta();
    db.setObjectId(new LongObjectId(rand.nextInt(Integer.MAX_VALUE)));
    db.setName(UUID.randomUUID().toString());
    db.setHostname(UUID.randomUUID().toString());
    db.setUsername(UUID.randomUUID().toString());
    db.setPassword(UUID.randomUUID().toString());
    return db;
}

Example 59 with DatabaseMeta

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

the class XulDatabaseExplorerControllerIT method testPostActionStatusForReflectCalls.

/**
 * PDI-11394 - Empty database browser dialog appears after unsuccessful connect to DB
 */
@Test
public void testPostActionStatusForReflectCalls() {
    Shell shell = new Shell();
    DatabaseMeta meta = new DatabaseMeta();
    List<DatabaseMeta> list = Collections.emptyList();
    XulDatabaseExplorerController dialog = new XulDatabaseExplorerController(shell, meta, list, false);
    UiPostActionStatus actual = dialog.getActionStatus();
    Assert.assertEquals("By default action status is none", UiPostActionStatus.NONE, actual);
    try {
        dialog.createDatabaseNodes();
    } catch (Exception e) {
    // do nothing as it usually used for ui functionality
    }
    actual = dialog.getActionStatus();
    // this error is caused runtime exception on error dialog show
    Assert.assertEquals("For reflective calls we have ability to ask for status directly", UiPostActionStatus.ERROR, actual);
}

Example 60 with DatabaseMeta

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

the class JobEntryCheckDbConnections method getResourceDependencies.

public List<ResourceReference> getResourceDependencies(JobMeta jobMeta) {
    List<ResourceReference> references = super.getResourceDependencies(jobMeta);
    if (connections != null) {
        for (int i = 0; i < connections.length; i++) {
            DatabaseMeta connection = connections[i];
            ResourceReference reference = new ResourceReference(this);
            reference.getEntries().add(new ResourceEntry(connection.getHostname(), ResourceType.SERVER));
            reference.getEntries().add(new ResourceEntry(connection.getDatabaseName(), ResourceType.DATABASENAME));
            references.add(reference);
        }
    }
    return references;
}