Examples with IPentahoResultSet org.pentaho.commons.connection.IPentahoResultSet used on opensource projects

Example 96 with IPentahoResultSet

use of org.pentaho.commons.connection.IPentahoResultSet in project pentaho-platform by pentaho.

the class SQLBaseComponent method runQuery.

/**
 * executes the specified query template. The query template is first formatted and then executed. If live, the
 * original result set is made available as an output. If not live, the result set is converted into memory and the
 * connection and live result set are closed.
 *
 * @param rawQuery
 *          query template
 * @param live
 *          returns original result set if true, memory result set if false
 * @return true if successful
 */
protected boolean runQuery(final String rawQuery, boolean live) {
    try {
        if ((connection == null) || !connection.initialized()) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            return false;
        }
        String query = applyInputsToFormat(rawQuery);
        SQLConnection sqlConnection = null;
        if ((connection instanceof SQLConnection)) {
            sqlConnection = (SQLConnection) connection;
        }
        // Some of the following Added by Arijit Chatterjee passing the timeout value to SQLConnection class
        if (sqlConnection != null) {
            if (this.getQueryTimeout() >= 0) {
                sqlConnection.setQueryTimeout(this.getQueryTimeout());
            }
            if (this.getMaxRows() >= 0) {
                sqlConnection.setMaxRows(this.getMaxRows());
            }
            if (this.getReadOnly()) {
                sqlConnection.setReadOnly(true);
            }
        }
        AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) getActionDefinition();
        IPentahoResultSet resultSet = null;
        boolean isForwardOnly = relationalDbAction.getUseForwardOnlyResultSet().getBooleanValue(false);
        resultSet = doQuery(sqlConnection, query, isForwardOnly);
        if (sqlConnection.isForcedForwardOnly()) {
            isForwardOnly = true;
            live = false;
            // $NON-NLS-1$
            warn(Messages.getInstance().getString("SQLBaseComponent.WARN_FALL_BACK_TO_NONSCROLLABLE"));
        }
        if (live) {
            // set the result set as the output
            rSet = resultSet;
            // After preparation and execution, we need to clear out the
            // prepared parameters.
            preparedParameters.clear();
            if (resultSet != null) {
                getMetadata(resultSet, true);
                IActionOutput actionOutput = relationalDbAction.getOutputResultSet();
                if (actionOutput != null) {
                    actionOutput.setValue(resultSet);
                }
                return true;
            } else {
                // close the connection if owner
                error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", // $NON-NLS-1$
                getActionName()));
                if (connectionOwner) {
                    connection.close();
                }
                return false;
            }
        } else {
            // execute the query, read the results and cache them
            try {
                // After preparation and execution, we need to clear out the
                // prepared parameters.
                preparedParameters.clear();
                IPentahoResultSet cachedResultSet = resultSet.memoryCopy();
                rSet = cachedResultSet;
                IActionOutput actionOutput = relationalDbAction.getOutputResultSet();
                if (actionOutput != null) {
                    actionOutput.setValue(cachedResultSet);
                }
            } finally {
                // close the connection if owner
                if (connectionOwner) {
                    connection.close();
                    connection = null;
                }
            }
        }
        return true;
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return false;
}

Example 97 with IPentahoResultSet

use of org.pentaho.commons.connection.IPentahoResultSet in project pentaho-platform by pentaho.

the class SQLBaseComponent method resolveParameter.

/**
 * This method is called when TemplateUtil.applyTemplate() encounters a parameter. TemplateUtil.applyTemplate is
 * called when someone makes a call to applyInputsToFormat() In this class it is called in the above "runQuery()"
 * method.
 *
 * @param template
 *          the source string
 * @param parameter
 *          the parameter value
 * @param parameterMatcher
 *          the regex parameter matcher
 * @param copyStart
 *          the start of the copy
 * @param results
 *          the output result
 * @return the next copystart
 */
@Override
public int resolveParameter(final String template, final String parameter, final Matcher parameterMatcher, int copyStart, final StringBuffer results) {
    // $NON-NLS-1$
    StringTokenizer tokenizer = new StringTokenizer(parameter, ":");
    if (tokenizer.countTokens() == 2) {
        // Currently, the component only handles one bit of metadata
        String parameterPrefix = tokenizer.nextToken();
        String inputName = tokenizer.nextToken();
        // mark a spot in the preparedParameters list and move on.
        if (parameterPrefix.equals(IPreparedComponent.PREPARE_LATER_PREFIX)) {
            if (!isDefinedOutput(IPreparedComponent.PREPARED_COMPONENT_NAME)) {
                // $NON-NLS-1$
                error(Messages.getInstance().getErrorString("IPreparedComponent.ERROR_0003_INVALID_PARAMETER_STATE"));
                return -1;
            }
            preparedParameters.add(IPreparedComponent.PREPARE_LATER_PLACEHOLDER);
            int start = parameterMatcher.start();
            int end = parameterMatcher.end();
            results.append(template.substring(copyStart, start));
            // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
            results.append("{" + IPreparedComponent.PREPARE_LATER_INTER_PREFIX + ":" + inputName + "}");
            return end;
        }
        if (parameterPrefix.equals(SQLBaseComponent.PREPARE_PARAMETER_PREFIX)) {
            // We know this parameter is for us.
            // First, is this a special input
            Object parameterValue = TemplateUtil.getSystemInput(inputName, getRuntimeContext());
            if ((parameterValue == null) && isDefinedInput(inputName)) {
                parameterValue = this.getInputValue(inputName);
            }
            if (parameterValue != null) {
                // We have a parameter value - now, it's time to create a parameter and build up the
                // parameter string
                int start = parameterMatcher.start();
                int end = parameterMatcher.end();
                // First, find out if the parameter was quoted...
                if ((start > 0) && (end < template.length())) {
                    if ((template.charAt(start - 1) == '\'') && (template.charAt(end) == '\'')) {
                        // Ok, the parameter was quoted as near as we can tell. So, we need
                        // to increase the size of the amount we overwrite by one in each
                        // direction. This is for backward compatibility.
                        start--;
                        end++;
                    }
                }
                // We now have a valid start and end. It's time to see whether we're dealing
                // with an array, a result set, or a scalar.
                StringBuffer parameterBuffer = new StringBuffer();
                if (parameterValue instanceof String) {
                    preparedParameters.add(parameterValue);
                    parameterBuffer.append('?');
                } else if (parameterValue instanceof Object[]) {
                    Object[] pObj = (Object[]) parameterValue;
                    for (Object element : pObj) {
                        preparedParameters.add(element);
                        // $NON-NLS-1$ //$NON-NLS-2$
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "?" : ",?");
                    }
                } else if (parameterValue instanceof IPentahoResultSet) {
                    IPentahoResultSet rs = (IPentahoResultSet) parameterValue;
                    // See if we can find a column in the metadata with the same
                    // name as the input
                    IPentahoMetaData md = rs.getMetaData();
                    int columnIdx = -1;
                    if (md.getColumnCount() == 1) {
                        columnIdx = 0;
                    } else {
                        columnIdx = md.getColumnIndex(new String[] { parameter });
                    }
                    if (columnIdx < 0) {
                        // $NON-NLS-1$
                        error(Messages.getInstance().getErrorString("Template.ERROR_0005_COULD_NOT_DETERMINE_COLUMN"));
                        return -1;
                    }
                    int rowCount = rs.getRowCount();
                    Object valueCell = null;
                    // TODO support non-string columns
                    for (int i = 0; i < rowCount; i++) {
                        valueCell = rs.getValueAt(i, columnIdx);
                        preparedParameters.add(valueCell);
                        // $NON-NLS-1$ //$NON-NLS-2$
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "?" : ",?");
                    }
                } else if (parameterValue instanceof List) {
                    List pObj = (List) parameterValue;
                    for (int i = 0; i < pObj.size(); i++) {
                        preparedParameters.add(pObj.get(i));
                        // $NON-NLS-1$ //$NON-NLS-2$
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "?" : ",?");
                    }
                } else {
                    // If we're here, we know parameterValue is not null and not a string
                    this.preparedParameters.add(parameterValue);
                    parameterBuffer.append('?');
                }
                // OK - We have a parameterBuffer and have filled out the preparedParameters
                // list. It's time to change the SQL to insert our parameter marker and tell
                // the caller we've done our job.
                results.append(template.substring(copyStart, start));
                copyStart = end;
                results.append(parameterBuffer);
                return copyStart;
            }
        }
    }
    // Nothing here for us - let default behavior through
    return -1;
}

Example 98 with IPentahoResultSet

use of org.pentaho.commons.connection.IPentahoResultSet in project pentaho-platform by pentaho.

the class MDXBaseComponent method executePrepared.

/**
 * executes a prepared method that returns a result set executePrepared looks up any "PREPARELATER" params in the
 * preparedParams map.
 *
 * @param preparedParams
 *          a map of possible parameters.
 * @return result set
 */
public IPentahoResultSet executePrepared(final Map preparedParams) {
    try {
        if (connection == null) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("MDXBaseComponent.ERROR_0008_NO_CONNECTION"));
            return null;
        }
        if (!connection.initialized()) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("MDXBaseComponent.ERROR_0008_NO_CONNECTION"));
            return null;
        }
        if (preparedQuery == null) {
            error(Messages.getInstance().getErrorString("MDXBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", // $NON-NLS-1$
            getActionName()));
            return null;
        }
        // parse preparedQuery, replacing any {PREPARELATER:NAME} with appropriate values
        String query = TemplateUtil.applyTemplate(preparedQuery, getRuntimeContext(), new MapParameterResolver(preparedParams, IPreparedComponent.PREPARE_LATER_PREFIX, getRuntimeContext()));
        if (ComponentBase.debug) {
            // $NON-NLS-1$
            debug(Messages.getInstance().getString("MDXBaseComponent.DEBUG_RUNNING_QUERY", query));
        }
        // evaluate
        IPentahoResultSet resultSet = connection.executeQuery(query);
        rSet = resultSet;
        return resultSet;
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("MDXBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return null;
}

Example 99 with IPentahoResultSet

use of org.pentaho.commons.connection.IPentahoResultSet in project pentaho-platform by pentaho.

the class MessageFormatter method formatSuccessMessage.

@SuppressWarnings({ "deprecation", "null" })
public void formatSuccessMessage(final String mimeType, final IRuntimeContext context, final StringBuffer messageBuffer, final boolean doMessages, final boolean doWrapper) {
    if (context == null) {
        // something went badly wrong
        formatFailureMessage(mimeType, context, messageBuffer);
    } else if (mimeType.equalsIgnoreCase(MessageFormatter.HTML_MIME_TYPE)) {
        // TODO make this template or XSL based
        if (doWrapper) {
            messageBuffer.append(// $NON-NLS-1$
            "<html><head><title>").append(// $NON-NLS-1$
            Messages.getInstance().getString("MessageFormatter.USER_START_ACTION")).append(// $NON-NLS-1$
            "</title><link rel=\"stylesheet\" type=\"text/css\" href=\"/pentaho-style/active/default.css\"></head>").append("<body dir=\"").append(LocaleHelper.getTextDirection()).append(// $NON-NLS-1$ //$NON-NLS-2$
            "\"><table cellspacing=\"10\"><tr><td class=\"portlet-section\" colspan=\"3\">").append(// $NON-NLS-1$
            Messages.getInstance().getString("MessageFormatter.USER_ACTION_SUCCESSFUL")).append(// $NON-NLS-1$
            "<hr size=\"1\"/></td></tr><tr><td class=\"portlet-font\" valign=\"top\">");
        }
        // hmm do we need this to be ordered?
        Set outputNames = context.getOutputNames();
        Iterator outputNameIterator = outputNames.iterator();
        while (outputNameIterator.hasNext()) {
            String outputName = (String) outputNameIterator.next();
            Object value = context.getOutputParameter(outputName).getValue();
            if (value == null) {
                // $NON-NLS-1$
                value = "";
            } else if (value instanceof IPentahoResultSet) {
                formatResultSetAsHTMLRows((IPentahoResultSet) value, messageBuffer);
            } else {
                // Temporary fix for BISERVER-3348
                ReturnParameter rpm = (ReturnParameter) context.getParameterManager().getReturnParameters().get(outputName);
                // CHECKSTYLE IGNORE EmptyBlock FOR NEXT 3 LINES
                if (// $NON-NLS-1$
                (rpm != null) && ("response".equalsIgnoreCase(rpm.destinationName)) && ("header".equalsIgnoreCase(rpm.destinationParameter))) {
                // $NON-NLS-1$
                // we don't want to output response header parameters to the browser...
                } else {
                    if (doWrapper) {
                        // $NON-NLS-1$
                        messageBuffer.append(outputName).append("=");
                    }
                    messageBuffer.append(value.toString());
                    if (doWrapper) {
                        // $NON-NLS-1$
                        messageBuffer.append("<br/>");
                    }
                }
            }
        }
        if (doMessages) {
            if (doWrapper) {
                // $NON-NLS-1$
                messageBuffer.append("<p><br size=\"1\">");
            }
            List messages = context.getMessages();
            Iterator messageIterator = messages.iterator();
            while (messageIterator.hasNext()) {
                messageBuffer.append((String) messageIterator.next());
                if (doWrapper) {
                    // $NON-NLS-1$
                    messageBuffer.append("<br/>");
                }
            }
        }
        if (doWrapper) {
            // $NON-NLS-1$
            messageBuffer.append("</td></tr></table></body></html>");
        }
    } else if (mimeType.equalsIgnoreCase(MessageFormatter.TEXT_MIME_TYPE)) {
        // $NON-NLS-1$ //$NON-NLS-2$
        messageBuffer.append(Messages.getInstance().getString("MessageFormatter.USER_START_ACTION" + "\n")).append(// $NON-NLS-1$ //$NON-NLS-2$
        Messages.getInstance().getString("MessageFormatter.USER_ACTION_SUCCESSFUL" + "\n"));
        // hmm do we need this to be ordered?
        Set outputNames = context.getOutputNames();
        Iterator outputNameIterator = outputNames.iterator();
        while (outputNameIterator.hasNext()) {
            String outputName = (String) outputNameIterator.next();
            Object value = context.getOutputParameter(outputName).getValue();
            if (value == null) {
                // $NON-NLS-1$
                value = "";
            } else if (value instanceof IPentahoResultSet) {
                IPentahoResultSet resultSet = (IPentahoResultSet) value;
                Object[][] columnHeaders = resultSet.getMetaData().getColumnHeaders();
                Object[][] rowHeaders = resultSet.getMetaData().getRowHeaders();
                boolean hasColumnHeaders = columnHeaders != null;
                boolean hasRowHeaders = rowHeaders != null;
                if (hasColumnHeaders) {
                    for (Object[] element : columnHeaders) {
                        for (int column = 0; column < element.length; column++) {
                            if (hasRowHeaders) {
                                for (int indent = 0; indent < rowHeaders[0].length; indent++) {
                                    // $NON-NLS-1$
                                    messageBuffer.append("\t");
                                }
                            }
                            // $NON-NLS-1$
                            messageBuffer.append(element[column]).append("\t");
                        }
                        // $NON-NLS-1$
                        messageBuffer.append("\n");
                    }
                }
                int headerRow = 0;
                Object[] dataRow = resultSet.next();
                int currentRow = 0;
                while ((dataRow != null) && (currentRow < MessageFormatter.MAX_RESULT_THRESHOLD)) {
                    if (hasRowHeaders) {
                        for (int rowHeaderCol = 0; rowHeaderCol < rowHeaders[headerRow].length; rowHeaderCol++) {
                            // $NON-NLS-1$
                            messageBuffer.append(rowHeaders[headerRow][rowHeaderCol].toString()).append("\t");
                        }
                    }
                    for (Object element : dataRow) {
                        // $NON-NLS-1$
                        messageBuffer.append(element.toString()).append("\t");
                    }
                    dataRow = resultSet.next();
                    currentRow++;
                }
            } else {
                // Temporary fix for BISERVER-3348
                ReturnParameter rpm = (ReturnParameter) context.getParameterManager().getReturnParameters().get(outputName);
                // CHECKSTYLE IGNORE EmptyBlock FOR NEXT 3 LINES
                if (// $NON-NLS-1$
                (rpm != null) && ("response".equalsIgnoreCase(rpm.destinationName)) && ("header".equalsIgnoreCase(rpm.destinationParameter))) {
                // $NON-NLS-1$
                // we don't want to output response header parameters to the browser...
                } else {
                    // $NON-NLS-1$ //$NON-NLS-2$
                    messageBuffer.append(outputName).append("=").append(value.toString()).append("\n");
                }
            }
        }
        if (doMessages) {
            List messages = context.getMessages();
            Iterator messageIterator = messages.iterator();
            while (messageIterator.hasNext()) {
                // $NON-NLS-1$
                messageBuffer.append((String) messageIterator.next()).append("\n");
            }
        }
    }
}

Example 100 with IPentahoResultSet

use of org.pentaho.commons.connection.IPentahoResultSet in project pentaho-platform by pentaho.

the class JavascriptRule method executeScript.

protected Object executeScript(final ScriptableObject scriptable, final Scriptable scope, final String script, final Context cx) throws Exception {
    ScriptableObject.defineClass(scope, JavaScriptResultSet.class);
    @SuppressWarnings("unchecked") Set<String> inputNames = getInputNames();
    Iterator<String> inputNamesIterator = inputNames.iterator();
    String inputName;
    Object inputValue;
    while (inputNamesIterator.hasNext()) {
        inputName = (String) inputNamesIterator.next();
        if (inputName.indexOf('-') >= 0) {
            throw new IllegalArgumentException(Messages.getInstance().getErrorString("JSRULE.ERROR_0006_INVALID_JS_VARIABLE", // $NON-NLS-1$
            inputName));
        }
        inputValue = getInputValue(inputName);
        if (inputValue instanceof String) {
            inputValue = StringEscapeUtils.escapeHtml((String) inputValue);
        }
        Object wrapper;
        if (inputValue instanceof IPentahoResultSet) {
            JavaScriptResultSet results = new JavaScriptResultSet();
            // Required as of Rhino 1.7R1 to resolve caching, base object
            // inheritance and property tree
            results.setPrototype(scriptable);
            results.setResultSet((IPentahoResultSet) inputValue);
            wrapper = Context.javaToJS(inputValue, results);
        } else {
            wrapper = Context.javaToJS(inputValue, scope);
        }
        ScriptableObject.putProperty(scope, inputName, wrapper);
    }
    // Add system out and this object to the scope
    Object wrappedOut = Context.javaToJS(System.out, scope);
    Object wrappedThis = Context.javaToJS(this, scope);
    // $NON-NLS-1$
    ScriptableObject.putProperty(scope, "out", wrappedOut);
    // $NON-NLS-1$
    ScriptableObject.putProperty(scope, "rule", wrappedThis);
    // $NON-NLS-1$
    return cx.evaluateString(scope, script, "<cmd>", 1, null);
}