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

Example 51 with IPentahoResultSet

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

the class DatasourceServiceHelper method getSerializeableResultSet.

public static SerializedResultSet getSerializeableResultSet(String connectionName, String query, int rowLimit, IPentahoSession session) throws DatasourceServiceException {
    SerializedResultSet serializedResultSet = null;
    SQLConnection sqlConnection = null;
    try {
        sqlConnection = (SQLConnection) PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, connectionName, PentahoSessionHolder.getSession(), null);
        sqlConnection.setMaxRows(rowLimit);
        sqlConnection.setReadOnly(true);
        IPentahoResultSet resultSet = sqlConnection.executeQuery(query);
        // $NON-NLS-1$
        logger.debug("ResultSet is not scrollable. Copying into memory");
        if (!resultSet.isScrollable()) {
            resultSet = convertToMemoryResultSet(resultSet);
        }
        MarshallableResultSet marshallableResultSet = new MarshallableResultSet();
        marshallableResultSet.setResultSet(resultSet);
        IPentahoMetaData ipmd = resultSet.getMetaData();
        int[] columnTypes = null;
        if (ipmd instanceof SQLMetaData) {
            SQLMetaData smd = (SQLMetaData) ipmd;
            columnTypes = smd.getJDBCColumnTypes();
        } else if (ipmd instanceof MemoryMetaData) {
            MemoryMetaData mmd = (MemoryMetaData) ipmd;
            String[] columnTypesAsString = mmd.getColumnTypes();
            columnTypes = new int[columnTypesAsString.length];
            for (int i = 0; i < columnTypesAsString.length; i++) {
                columnTypes[i] = Integer.parseInt(columnTypesAsString[i]);
            }
        }
        if (columnTypes != null) {
            // Hack warning - get JDBC column types
            // TODO: Need to generalize this amongst all IPentahoResultSets
            List<List<String>> data = new ArrayList<List<String>>();
            for (MarshallableRow row : marshallableResultSet.getRows()) {
                String[] rowData = row.getCell();
                List<String> rowDataList = new ArrayList<String>(rowData.length);
                for (int j = 0; j < rowData.length; j++) {
                    rowDataList.add(rowData[j]);
                }
                data.add(rowDataList);
            }
            serializedResultSet = new SerializedResultSet(columnTypes, marshallableResultSet.getColumnNames().getColumnName(), data);
        }
    } catch (Exception e) {
        logger.error(Messages.getErrorString("DatasourceServiceHelper.ERROR_0001_QUERY_VALIDATION_FAILED", e.getLocalizedMessage()), // $NON-NLS-1$
        e);
        throw new DatasourceServiceException(Messages.getErrorString("DatasourceServiceHelper.ERROR_0001_QUERY_VALIDATION_FAILED", e.getLocalizedMessage()), // $NON-NLS-1$
        e);
    } finally {
        if (sqlConnection != null) {
            sqlConnection.close();
        }
    }
    return serializedResultSet;
}

Example 52 with IPentahoResultSet

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

the class MetadataService method doXmlQuery.

/**
 * Executes a XML query and returns a serializable result set
 *
 * @param rowLimit An optional row limit, -1 or null means all rows
 * @return
 */
public MarshallableResultSet doXmlQuery(String xml, Integer rowLimit) {
    IPentahoResultSet resultSet = executeQuery(xml, rowLimit);
    if (resultSet == null) {
        return null;
    }
    MarshallableResultSet result = getMarshallableResultSet();
    result.setResultSet(resultSet);
    return result;
}

Example 53 with IPentahoResultSet

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

the class TemplateUtil method applyTableTemplate.

public static void applyTableTemplate(final String template, final Properties inputs, final Pattern parameterPattern, final StringBuffer results) {
    Matcher parameterMatcher = parameterPattern.matcher(template);
    ArrayList<String> partsList = new ArrayList<String>();
    ArrayList<Integer> columnsList = new ArrayList<Integer>();
    int idx = 0;
    int lastEnd = 0;
    IPentahoResultSet data = null;
    while (parameterMatcher.find()) {
        int start = parameterMatcher.start();
        String parameter = parameterMatcher.group(1);
        // pull out the repeating part
        // $NON-NLS-1$
        int pos1 = parameter.indexOf(":col:");
        if (pos1 > -1) {
            String part = template.substring(lastEnd, start);
            if (PentahoSystem.debug) {
                // $NON-NLS-1$
                TemplateUtil.logger.debug("parameter=" + parameter);
                // $NON-NLS-1$
                TemplateUtil.logger.debug("part=" + part);
            }
            String inputName = parameter.substring(0, pos1);
            String columnNoStr = parameter.substring(pos1 + 5);
            int columnNo = Integer.parseInt(columnNoStr);
            if (PentahoSystem.debug) {
                // $NON-NLS-1$
                TemplateUtil.logger.debug("inputName=" + inputName);
                // $NON-NLS-1$
                TemplateUtil.logger.debug("columnNoStr=" + columnNoStr);
                // $NON-NLS-1$
                TemplateUtil.logger.debug("columnNo=" + columnNo);
            }
            Object obj = null;
            if (inputs instanceof InputProperties) {
                obj = ((InputProperties) inputs).getInput(inputName);
            }
            if (obj == null) {
                if (TemplateUtil.logger.isDebugEnabled()) {
                    // $NON-NLS-1$
                    TemplateUtil.logger.debug(Messages.getInstance().getString("TemplateUtil.NOT_FOUND", inputName));
                }
            } else {
                if (obj instanceof IPentahoResultSet) {
                    data = (IPentahoResultSet) obj;
                    if (columnNo < data.getColumnCount()) {
                        columnsList.add(new Integer(columnNo));
                    } else {
                        TemplateUtil.logger.warn(Messages.getInstance().getString("TemplateUtil.INVALID_COLUMN", // $NON-NLS-1$
                        String.valueOf(columnNo)));
                    }
                }
            }
            partsList.add(part);
            lastEnd = parameterMatcher.end();
        }
    }
    if (PentahoSystem.debug) {
        // $NON-NLS-1$
        TemplateUtil.logger.debug("partsList.size()=" + partsList.size());
    }
    if (PentahoSystem.debug) {
        // $NON-NLS-1$
        TemplateUtil.logger.debug("columnsList.size()=" + columnsList.size());
    }
    if (PentahoSystem.debug) {
        // $NON-NLS-1$
        TemplateUtil.logger.debug("data=" + data);
    }
    if (partsList.size() > 0) {
        partsList.add(template.substring(lastEnd));
    } else {
        // $NON-NLS-1$
        TemplateUtil.logger.warn(Messages.getInstance().getString("TemplateUtil.NO_TOKEN"));
    }
    if ((data != null) && (partsList.size() == columnsList.size() + 1)) {
        // here we go
        String[] parts = new String[partsList.size()];
        partsList.toArray(parts);
        Integer[] cols = new Integer[columnsList.size()];
        columnsList.toArray(cols);
        int rowNo = 0;
        Object[] row = data.getDataRow(rowNo);
        while (row != null) {
            for (idx = 0; idx < cols.length; idx++) {
                results.append(parts[idx]);
                results.append(row[cols[idx].intValue()]);
            }
            results.append(parts[parts.length - 1]);
            rowNo++;
            row = data.getDataRow(rowNo);
        }
    }
    if (PentahoSystem.debug) {
        // $NON-NLS-1$
        TemplateUtil.logger.debug("results=" + results.toString());
    }
}

Example 54 with IPentahoResultSet

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

the class MapParameterResolver method resolveParameter.

/**
 * This method is called when TemplateUtil.applyTemplate() encounters a parameter.
 *
 * @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
 */
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();
        if (parameterPrefix.equals(prefix)) {
            // We know this parameter is for us.
            // First, is this a special input
            Object parameterValue = TemplateUtil.getSystemInput(inputName, runtimecontext);
            if ((parameterValue == null) && lookupMap.containsKey(inputName)) {
                parameterValue = lookupMap.get(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();
                // 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) {
                    // $NON-NLS-1$ //$NON-NLS-2$
                    parameterBuffer.append(((String) parameterValue).replaceAll("'", "\\'"));
                } else if (parameterValue instanceof Object[]) {
                    Object[] pObj = (Object[]) parameterValue;
                    for (Object element : pObj) {
                        // TODO: escape quotes!
                        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "'" + element + "'" : ",'" + element + "'");
                    }
                } 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);
                        // TODO: escape quotes!
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "'" + valueCell + "'" : // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
                        ",'" + valueCell + "'");
                    }
                } else if (parameterValue instanceof List) {
                    List pObj = (List) parameterValue;
                    for (int i = 0; i < pObj.size(); i++) {
                        parameterBuffer.append((parameterBuffer.length() == 0) ? "'" + pObj.get(i) + "'" : // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
                        ",'" + pObj.get(i) + "'");
                    }
                } else {
                    // If we're here, we know parameterValue is not null and not a string
                    // $NON-NLS-1$ //$NON-NLS-2$
                    parameterBuffer.append(parameterValue.toString().replaceAll("'", "\\'"));
                }
                // 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 55 with IPentahoResultSet

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

the class CategoryDatasetChartComponent method createChart.

@Override
public Dataset createChart(final Document doc) {
    if (actionPath != null) {
        // if we have a solution then get the values
        values = getActionData();
    }
    if (values == null) {
        // we could not get any data
        return null;
    }
    // get the chart node from the document
    // $NON-NLS-1$
    Node chartAttributes = doc.selectSingleNode("//" + AbstractChartComponent.CHART_NODE_NAME);
    // create the definition
    // $NON-NLS-1$
    String chType = chartAttributes.selectSingleNode("chart-type").getText();
    CategoryDatasetChartDefinition chartDefinition = null;
    if (ChartDefinition.BAR_LINE_CHART_STR.equalsIgnoreCase(chType)) {
        chartDefinition = new BarLineChartDefinition((IPentahoResultSet) values, byRow, chartAttributes, getSession());
    } else {
        chartDefinition = new CategoryDatasetChartDefinition((IPentahoResultSet) values, byRow, chartAttributes, getSession());
    }
    // set the misc values from chartDefinition
    setChartType(chartDefinition.getChartType());
    setTitle(chartDefinition.getTitle());
    // get the URL template
    Node urlTemplateNode = chartAttributes.selectSingleNode(AbstractChartComponent.URLTEMPLATE_NODE_NAME);
    if (urlTemplateNode != null) {
        setUrlTemplate(urlTemplateNode.getText());
    }
    // get the additional parameter
    Node paramName2Node = chartAttributes.selectSingleNode(AbstractChartComponent.PARAM2_NODE_NAME);
    if (paramName2Node != null) {
        seriesName = paramName2Node.getText();
    }
    if ((chartDefinition.getWidth() != -1) && (width == -1)) {
        setWidth(chartDefinition.getWidth());
    }
    if ((chartDefinition.getHeight() != -1) && (height == -1)) {
        setHeight(chartDefinition.getHeight());
    }
    return chartDefinition;
}