Examples with Database org.apache.hop.core.database.Database used on opensource projects

Example 1 with Database

use of org.apache.hop.core.database.Database in project hop by apache.

the class ActionEvalTableContentDialog method getSql.

private void getSql() {
    DatabaseMeta inf = getWorkflowMeta().findDatabase(wConnection.getText());
    if (inf != null) {
        DatabaseExplorerDialog std = new DatabaseExplorerDialog(shell, SWT.NONE, variables, inf, getWorkflowMeta().getDatabases());
        if (std.open()) {
            String sql = "SELECT *" + Const.CR + "FROM " + inf.getQuotedSchemaTableCombination(variables, std.getSchemaName(), std.getTableName()) + Const.CR;
            wSql.setText(sql);
            MessageBox yn = new MessageBox(shell, SWT.YES | SWT.NO | SWT.CANCEL | SWT.ICON_QUESTION);
            yn.setMessage(BaseMessages.getString(PKG, "ActionEvalTableContent.IncludeFieldNamesInSQL"));
            yn.setText(BaseMessages.getString(PKG, "ActionEvalTableContent.DialogCaptionQuestion"));
            int id = yn.open();
            switch(id) {
                case SWT.CANCEL:
                    break;
                case SWT.NO:
                    wSql.setText(sql);
                    break;
                case SWT.YES:
                    Database db = new Database(loggingObject, variables, inf);
                    try {
                        db.connect();
                        IRowMeta fields = db.getQueryFields(sql, false);
                        if (fields != null) {
                            sql = "SELECT" + Const.CR;
                            for (int i = 0; i < fields.size(); i++) {
                                IValueMeta field = fields.getValueMeta(i);
                                if (i == 0) {
                                    sql += "  ";
                                } else {
                                    sql += ", ";
                                }
                                sql += inf.quoteField(field.getName()) + Const.CR;
                            }
                            sql += "FROM " + inf.getQuotedSchemaTableCombination(variables, std.getSchemaName(), std.getTableName()) + Const.CR;
                            wSql.setText(sql);
                        } else {
                            MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_ERROR);
                            mb.setMessage(BaseMessages.getString(PKG, "ActionEvalTableContent.ERROR_CouldNotRetrieveFields") + Const.CR + BaseMessages.getString(PKG, "ActionEvalTableContent.PerhapsNoPermissions"));
                            mb.setText(BaseMessages.getString(PKG, "ActionEvalTableContent.DialogCaptionError2"));
                            mb.open();
                        }
                    } catch (HopException e) {
                        MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_ERROR);
                        mb.setText(BaseMessages.getString(PKG, "ActionEvalTableContent.DialogCaptionError3"));
                        mb.setMessage(BaseMessages.getString(PKG, "ActionEvalTableContent.AnErrorOccurred") + Const.CR + e.getMessage());
                        mb.open();
                    } finally {
                        db.disconnect();
                    }
                    break;
                default:
                    break;
            }
        }
    } else {
        MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_ERROR);
        mb.setMessage(BaseMessages.getString(PKG, "ActionEvalTableContent.ConnectionNoLongerAvailable"));
        mb.setText(BaseMessages.getString(PKG, "ActionEvalTableContent.DialogCaptionError4"));
        mb.open();
    }
}

Example 2 with Database

use of org.apache.hop.core.database.Database in project hop by apache.

the class InsertUpdateMeta method getSqlStatements.

@Override
public SqlStatement getSqlStatements(IVariables variables, PipelineMeta pipelineMeta, TransformMeta transformMeta, IRowMeta prev, IHopMetadataProvider metadataProvider) throws HopTransformException {
    SqlStatement sqlStatement = // default: nothing to do!
    new SqlStatement(transformMeta.getName(), null, null);
    String connectionName = variables.resolve(connection);
    if (StringUtils.isEmpty(connectionName)) {
        sqlStatement.setError(BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NoConnectionDefined"));
        return sqlStatement;
    }
    DatabaseMeta databaseMeta;
    try {
        databaseMeta = metadataProvider.getSerializer(DatabaseMeta.class).load(connectionName);
        if (databaseMeta == null) {
            sqlStatement.setError("Error finding database connection " + connectionName + " in the metadata");
            return sqlStatement;
        }
    } catch (Exception e) {
        sqlStatement.setError("Error loading database connection " + connectionName + " from Hop metadata: " + Const.getSimpleStackTrace(e));
        return sqlStatement;
    }
    if (prev != null && prev.size() > 0) {
        String[] keyLookup = null;
        String[] keyStream = null;
        String[] updateLookup = null;
        String[] updateStream = null;
        if (insertUpdateLookupField.getLookupKeys().size() > 0) {
            keyLookup = new String[insertUpdateLookupField.getLookupKeys().size()];
            for (int i = 0; i < insertUpdateLookupField.getLookupKeys().size(); i++) {
                keyLookup[i] = insertUpdateLookupField.getLookupKeys().get(i).getKeyLookup();
            }
        }
        if (insertUpdateLookupField.getLookupKeys().size() > 0) {
            keyStream = new String[insertUpdateLookupField.getLookupKeys().size()];
            for (int i = 0; i < insertUpdateLookupField.getLookupKeys().size(); i++) {
                keyStream[i] = insertUpdateLookupField.getLookupKeys().get(i).getKeyStream();
            }
        }
        if (insertUpdateLookupField.getValueFields().size() > 0) {
            updateLookup = new String[insertUpdateLookupField.getValueFields().size()];
            for (int i = 0; i < insertUpdateLookupField.getValueFields().size(); i++) {
                updateLookup[i] = insertUpdateLookupField.getValueFields().get(i).getUpdateLookup();
            }
        }
        if (insertUpdateLookupField.getValueFields().size() > 0) {
            updateStream = new String[insertUpdateLookupField.getValueFields().size()];
            for (int i = 0; i < insertUpdateLookupField.getValueFields().size(); i++) {
                updateStream[i] = insertUpdateLookupField.getValueFields().get(i).getUpdateStream();
            }
        }
        IRowMeta tableFields = RowMetaUtils.getRowMetaForUpdate(prev, keyLookup, keyStream, updateLookup, updateStream);
        if (!Utils.isEmpty(insertUpdateLookupField.getTableName())) {
            Database db = new Database(loggingObject, variables, databaseMeta);
            try {
                db.connect();
                String schemaTable = databaseMeta.getQuotedSchemaTableCombination(variables, variables.resolve(insertUpdateLookupField.getSchemaName()), variables.resolve(insertUpdateLookupField.getTableName()));
                String crTable = db.getDDL(schemaTable, tableFields, null, false, null, true);
                String crIndex = "";
                String[] idxFields = null;
                if (keyLookup != null && keyLookup.length > 0) {
                    idxFields = new String[keyLookup.length];
                    System.arraycopy(keyLookup, 0, idxFields, 0, keyLookup.length);
                } else {
                    sqlStatement.setError(BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.MissingKeyFields"));
                }
                // Key lookup dimensions...
                if (idxFields != null && !db.checkIndexExists(variables.resolve(insertUpdateLookupField.getSchemaName()), variables.resolve(insertUpdateLookupField.getTableName()), idxFields)) {
                    String indexName = "idx_" + variables.resolve(insertUpdateLookupField.getTableName()) + "_lookup";
                    crIndex = db.getCreateIndexStatement(schemaTable, indexName, idxFields, false, false, false, true);
                }
                String sql = crTable + Const.CR + crIndex;
                if (sql.length() == 0) {
                    sqlStatement.setSql(null);
                } else {
                    sqlStatement.setSql(sql);
                }
            } catch (HopException e) {
                sqlStatement.setError(BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.ErrorOccurred") + e.getMessage());
            }
        } else {
            sqlStatement.setError(BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NoTableDefinedOnConnection"));
        }
    } else {
        sqlStatement.setError(BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NotReceivingAnyFields"));
    }
    return sqlStatement;
}

Example 3 with Database

use of org.apache.hop.core.database.Database in project hop by apache.

the class InsertUpdateTestLazyConversion method testDateLazyConversion.

@Test
public void testDateLazyConversion() throws HopException {
    Database db = mock(Database.class);
    RowMeta returnRowMeta = new RowMeta();
    doReturn(new Object[] { new Timestamp(System.currentTimeMillis()) }).when(db).getLookup(any(PreparedStatement.class));
    returnRowMeta.addValueMeta(new ValueMetaDate("TimeStamp"));
    doReturn(returnRowMeta).when(db).getReturnRowMeta();
    ValueMetaString storageMetadata = new ValueMetaString("Date");
    storageMetadata.setConversionMask("yyyy-MM-dd");
    ValueMetaDate valueMeta = new ValueMetaDate("Date");
    valueMeta.setStorageType(IValueMeta.STORAGE_TYPE_BINARY_STRING);
    valueMeta.setStorageMetadata(storageMetadata);
    RowMeta inputRowMeta = new RowMeta();
    inputRowMeta.addValueMeta(valueMeta);
    InsertUpdateMeta transformMeta = smh.iTransformMeta;
    InsertUpdateLookupField mockedIulf = mock(InsertUpdateLookupField.class);
    List<InsertUpdateValue> items = mock(ArrayList.class);
    when(transformMeta.getInsertUpdateLookupField()).thenReturn(mockedIulf);
    when(transformMeta.getInsertUpdateLookupField().getValueFields()).thenReturn(items);
    when(items.get(0)).thenReturn(mock(InsertUpdateValue.class));
    when(items.get(0).isUpdate()).thenReturn(true);
    InsertUpdateData transformData = smh.iTransformData;
    transformData.lookupParameterRowMeta = inputRowMeta;
    transformData.db = db;
    transformData.keynrs = transformData.valuenrs = new int[] { 0 };
    transformData.keynrs2 = new int[] { -1 };
    transformData.updateParameterRowMeta = when(mock(RowMeta.class).size()).thenReturn(2).getMock();
    InsertUpdate transform = new InsertUpdate(smh.transformMeta, smh.iTransformMeta, smh.iTransformData, 0, smh.pipelineMeta, smh.pipeline);
    transform.setInputRowMeta(inputRowMeta);
    transform.addRowSetToInputRowSets(smh.getMockInputRowSet(new Object[] { "2013-12-20".getBytes() }));
    transform.init();
    transform.first = false;
    transform.processRow();
}

Example 4 with Database

use of org.apache.hop.core.database.Database in project hop by apache.

the class ScriptValuesAddedFunctions method fireToDB.

@SuppressWarnings("unused")
public static Object fireToDB(Context actualContext, Scriptable actualObject, Object[] argList, Function functionContext) {
    Object oRC = new Object();
    if (argList.length == 2) {
        try {
            Object scmO = actualObject.get("_transform_", actualObject);
            ScriptValues scm = (ScriptValues) Context.jsToJava(scmO, ScriptValues.class);
            String strDBName = Context.toString(argList[0]);
            String strSql = Context.toString(argList[1]);
            DatabaseMeta databaseMeta = DatabaseMeta.findDatabase(scm.getPipelineMeta().getDatabases(), strDBName);
            if (databaseMeta == null) {
                throw Context.reportRuntimeError("Database connection not found: " + strDBName);
            }
            // TODO: figure out how to set variables on the connection?
            // 
            Database db = new Database(scm, Variables.getADefaultVariableSpace(), databaseMeta);
            db.setQueryLimit(0);
            try {
                db.connect();
                ResultSet rs = db.openQuery(strSql);
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                if (rs != null) {
                    List<Object[]> list = new ArrayList<>();
                    while (rs.next()) {
                        Object[] objRow = new Object[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            objRow[i] = rs.getObject(i + 1);
                        }
                        list.add(objRow);
                    }
                    Object[][] resultArr = new Object[list.size()][];
                    list.toArray(resultArr);
                    db.disconnect();
                    return resultArr;
                }
            } catch (Exception er) {
                throw Context.reportRuntimeError(er.toString());
            }
        } catch (Exception e) {
            throw Context.reportRuntimeError(e.toString());
        }
    } else {
        throw Context.reportRuntimeError("The function call fireToDB requires 2 arguments.");
    }
    return oRC;
}

Example 5 with Database

use of org.apache.hop.core.database.Database in project hop by apache.

the class PGBulkLoaderDialog method setTableFieldCombo.

private void setTableFieldCombo() {
    Runnable fieldLoader = () -> {
        if (!wTable.isDisposed() && !wConnection.isDisposed() && !wSchema.isDisposed()) {
            final String tableName = wTable.getText();
            final String connectionName = wConnection.getText();
            final String schemaName = wSchema.getText();
            // clear
            for (ColumnInfo colInfo : tableFieldColumns) {
                colInfo.setComboValues(new String[] {});
            }
            if (!Utils.isEmpty(tableName)) {
                DatabaseMeta databaseMeta = pipelineMeta.findDatabase(connectionName);
                if (databaseMeta != null) {
                    Database db = new Database(loggingObject, variables, databaseMeta);
                    try {
                        db.connect();
                        String schemaTable = databaseMeta.getQuotedSchemaTableCombination(variables, schemaName, tableName);
                        IRowMeta r = db.getTableFields(schemaTable);
                        if (null != r) {
                            String[] fieldNames = r.getFieldNames();
                            if (null != fieldNames) {
                                for (ColumnInfo colInfo : tableFieldColumns) {
                                    colInfo.setComboValues(fieldNames);
                                }
                            }
                        }
                    } catch (Exception e) {
                        for (ColumnInfo colInfo : tableFieldColumns) {
                            colInfo.setComboValues(new String[] {});
                        }
                    // ignore any errors here. drop downs will not be
                    // filled, but no problem for the user
                    } finally {
                        try {
                            if (db != null) {
                                db.disconnect();
                            }
                        } catch (Exception ignored) {
                            // ignore any errors here.
                            db = null;
                        }
                    }
                }
            }
        }
    };
    shell.getDisplay().asyncExec(fieldLoader);
}