Search in sources :

Example 26 with MemoryResultSet

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

the class XMLABaseComponent method executeQuery.

/**
 * Execute query
 *
 * @param query   - MDX to be executed
 * @param catalog
 * @param handler Callback handler
 * @throws XMLAException
 */
public boolean executeQuery(final String query, final String catalog) throws XMLAException {
    Object[][] columnHeaders = null;
    Object[][] rowHeaders = null;
    Object[][] data = null;
    int columnCount = 0;
    int rowCount = 0;
    SOAPConnection connection = null;
    SOAPMessage reply = null;
    try {
        connection = scf.createConnection();
        SOAPMessage msg = mf.createMessage();
        MimeHeaders mh = msg.getMimeHeaders();
        // $NON-NLS-1$
        mh.setHeader("SOAPAction", XMLABaseComponent.EXECUTE_ACTION);
        SOAPPart soapPart = msg.getSOAPPart();
        SOAPEnvelope envelope = soapPart.getEnvelope();
        envelope.setEncodingStyle(XMLABaseComponent.ENCODING_STYLE);
        SOAPBody body = envelope.getBody();
        // $NON-NLS-1$//$NON-NLS-2$
        Name nEx = envelope.createName("Execute", "", XMLABaseComponent.XMLA_URI);
        SOAPElement eEx = body.addChildElement(nEx);
        eEx.setEncodingStyle(XMLABaseComponent.ENCODING_STYLE);
        // add the parameters
        // COMMAND parameter
        // <Command>
        // <Statement>select [Measures].members on Columns from
        // Sales</Statement>
        // </Command>
        // $NON-NLS-1$ //$NON-NLS-2$
        Name nCom = envelope.createName("Command", "", XMLABaseComponent.XMLA_URI);
        SOAPElement eCommand = eEx.addChildElement(nCom);
        // $NON-NLS-1$ //$NON-NLS-2$
        Name nSta = envelope.createName("Statement", "", XMLABaseComponent.XMLA_URI);
        SOAPElement eStatement = eCommand.addChildElement(nSta);
        eStatement.addTextNode(query);
        // <Properties>
        // <PropertyList>
        // <DataSourceInfo>Provider=MSOLAP;Data
        // Source=local</DataSourceInfo>
        // <Catalog>Foodmart 2000</Catalog>
        // <Format>Multidimensional</Format>
        // <AxisFormat>TupleFormat</AxisFormat> oder "ClusterFormat"
        // </PropertyList>
        // </Properties>
        Map paraList = new HashMap();
        // $NON-NLS-1$
        paraList.put("DataSourceInfo", dataSource);
        // $NON-NLS-1$
        paraList.put("Catalog", catalog);
        // $NON-NLS-1$ //$NON-NLS-2$
        paraList.put("Format", "Multidimensional");
        // $NON-NLS-1$ //$NON-NLS-2$
        paraList.put("AxisFormat", "TupleFormat");
        // $NON-NLS-1$ //$NON-NLS-2$
        addParameterList(envelope, eEx, "Properties", "PropertyList", paraList);
        msg.saveChanges();
        // $NON-NLS-1$
        debug("Request for Execute");
        logSoapMsg(msg);
        // run the call
        reply = connection.call(msg, url);
        // $NON-NLS-1$
        debug("Reply from Execute");
        logSoapMsg(reply);
        // error check
        errorCheck(reply);
        // process the reply
        SOAPElement eRoot = findExecRoot(reply);
        // for each axis, get the positions (tuples)
        // $NON-NLS-1$ //$NON-NLS-2$
        Name name = envelope.createName("Axes", "", XMLABaseComponent.MDD_URI);
        SOAPElement eAxes = selectSingleNode(eRoot, name);
        if (eAxes == null) {
            // $NON-NLS-1$
            throw new XMLAException("Excecute result has no Axes element");
        }
        // $NON-NLS-1$ //$NON-NLS-2$
        name = envelope.createName("Axis", "", XMLABaseComponent.MDD_URI);
        Iterator itAxis = eAxes.getChildElements(name);
        AxisLoop: for (int iOrdinal = 0; itAxis.hasNext(); ) {
            SOAPElement eAxis = (SOAPElement) itAxis.next();
            // $NON-NLS-1$
            name = envelope.createName("name");
            String axisName = eAxis.getAttributeValue(name);
            int axisOrdinal;
            if (axisName.equals("SlicerAxis")) {
                // $NON-NLS-1$
                continue;
            } else {
                axisOrdinal = iOrdinal++;
            }
            // $NON-NLS-1$//$NON-NLS-2$
            name = envelope.createName("Tuples", "", XMLABaseComponent.MDD_URI);
            SOAPElement eTuples = selectSingleNode(eAxis, name);
            if (eTuples == null) {
                // what else?
                continue AxisLoop;
            }
            // $NON-NLS-1$//$NON-NLS-2$
            name = envelope.createName("Tuple", "", XMLABaseComponent.MDD_URI);
            Iterator itTuple = eTuples.getChildElements(name);
            // loop over tuples
            int positionOrdinal = 0;
            while (itTuple.hasNext()) {
                // TupleLoop
                SOAPElement eTuple = (SOAPElement) itTuple.next();
                if ((axisOrdinal == XMLABaseComponent.AXIS_COLUMNS) && (columnHeaders == null)) {
                    // $NON-NLS-1$
                    columnCount = getChildCount(envelope, eTuples, "Tuple");
                    // $NON-NLS-1$
                    columnHeaders = new Object[getChildCount(envelope, eTuple, "Member")][columnCount];
                } else if ((axisOrdinal == XMLABaseComponent.AXIS_ROWS) && (rowHeaders == null)) {
                    // $NON-NLS-1$
                    rowCount = getChildCount(envelope, eTuples, "Tuple");
                    // $NON-NLS-1$
                    rowHeaders = new Object[rowCount][getChildCount(envelope, eTuple, "Member")];
                }
                int index = 0;
                // $NON-NLS-1$//$NON-NLS-2$
                name = envelope.createName("Member", "", XMLABaseComponent.MDD_URI);
                Iterator itMember = eTuple.getChildElements(name);
                while (itMember.hasNext()) {
                    // MemberLoop
                    SOAPElement eMem = (SOAPElement) itMember.next();
                    // loop over children nodes
                    String caption = null;
                    Iterator it = eMem.getChildElements();
                    InnerLoop: while (it.hasNext()) {
                        Node n = (Node) it.next();
                        if (!(n instanceof SOAPElement)) {
                            continue InnerLoop;
                        }
                        SOAPElement el = (SOAPElement) n;
                        String enam = el.getElementName().getLocalName();
                        if (enam.equals("Caption")) {
                            // $NON-NLS-1$
                            caption = el.getValue();
                        }
                    }
                    if (axisOrdinal == XMLABaseComponent.AXIS_COLUMNS) {
                        columnHeaders[index][positionOrdinal] = caption;
                    } else if (axisOrdinal == XMLABaseComponent.AXIS_ROWS) {
                        rowHeaders[positionOrdinal][index] = caption;
                    }
                    ++index;
                }
                // MemberLoop
                ++positionOrdinal;
            }
        // TupleLoop
        }
        // AxisLoop
        data = new Object[rowCount][columnCount];
        // loop over cells in result set
        // $NON-NLS-1$//$NON-NLS-2$
        name = envelope.createName("CellData", "", XMLABaseComponent.MDD_URI);
        SOAPElement eCellData = selectSingleNode(eRoot, name);
        // $NON-NLS-1$//$NON-NLS-2$
        name = envelope.createName("Cell", "", XMLABaseComponent.MDD_URI);
        Iterator itSoapCell = eCellData.getChildElements(name);
        while (itSoapCell.hasNext()) {
            // CellLoop
            SOAPElement eCell = (SOAPElement) itSoapCell.next();
            // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
            name = envelope.createName("CellOrdinal", "", "");
            String cellOrdinal = eCell.getAttributeValue(name);
            int ordinal = Integer.parseInt(cellOrdinal);
            // $NON-NLS-1$//$NON-NLS-2$
            name = envelope.createName("Value", "", XMLABaseComponent.MDD_URI);
            Object value = selectSingleNode(eCell, name).getValue();
            int rowLoc = ordinal / columnCount;
            int columnLoc = ordinal % columnCount;
            data[rowLoc][columnLoc] = value;
        }
        // CellLoop
        MemoryResultSet resultSet = new MemoryResultSet();
        MemoryMetaData metaData = new MemoryMetaData(columnHeaders, rowHeaders);
        resultSet.setMetaData(metaData);
        for (Object[] element : data) {
            resultSet.addRow(element);
        }
        rSet = resultSet;
        if (resultSet != null) {
            if (getResultOutputName() != null) {
                setOutputValue(getResultOutputName(), resultSet);
            }
            return true;
        }
        return false;
    } catch (SOAPException se) {
        throw new XMLAException(se);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SOAPException e) {
                // log and ignore
                // $NON-NLS-1$
                error("?", e);
            }
        }
    }
}
Also used : HashMap(java.util.HashMap) Node(javax.xml.soap.Node) SOAPConnection(javax.xml.soap.SOAPConnection) MemoryMetaData(org.pentaho.commons.connection.memory.MemoryMetaData) SOAPEnvelope(javax.xml.soap.SOAPEnvelope) SOAPMessage(javax.xml.soap.SOAPMessage) Name(javax.xml.soap.Name) MimeHeaders(javax.xml.soap.MimeHeaders) SOAPBody(javax.xml.soap.SOAPBody) SOAPException(javax.xml.soap.SOAPException) SOAPPart(javax.xml.soap.SOAPPart) SOAPElement(javax.xml.soap.SOAPElement) Iterator(java.util.Iterator) HashMap(java.util.HashMap) Map(java.util.Map) MemoryResultSet(org.pentaho.commons.connection.memory.MemoryResultSet)

Example 27 with MemoryResultSet

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

the class JavaScriptResultSetIT method testIterators.

public void testIterators() {
    MemoryMetaData metadata = new MemoryMetaData(new String[][] { { "col1", "col2" } }, null);
    MemoryResultSet dataSet = new MemoryResultSet(metadata);
    JavaScriptResultSet data = new JavaScriptResultSet();
    data.setResultSet(dataSet);
    data.addRow(new Object[] { "a", new Integer(1) });
    data.addRow(new Object[] { "b", new Integer(2) });
    data.addRow(new Object[] { "c", new Integer(3) });
    assertEquals(3, data.getRowCount());
    assertEquals("a", data.getValueAt(0, 0));
    assertEquals(1, data.getValueAt(0, 1));
    assertEquals("b", data.getValueAt(1, 0));
    assertEquals(2, data.getValueAt(1, 1));
    assertEquals("c", data.getValueAt(2, 0));
    assertEquals(3, data.getValueAt(2, 1));
    assertEquals("a", data.next()[0]);
    assertEquals("b", data.next()[0]);
    assertEquals("c", data.next()[0]);
    assertNull(data.next());
    data.beforeFirst();
    assertEquals("a", data.next()[0]);
    assertEquals("b", data.next()[0]);
    assertEquals("c", data.next()[0]);
    assertNull(data.next());
    data.close();
    assertEquals("a", data.next()[0]);
    assertEquals("b", data.next()[0]);
    assertEquals("c", data.next()[0]);
    assertNull(data.next());
    data.closeConnection();
    assertEquals("a", data.next()[0]);
    assertEquals("b", data.next()[0]);
    assertEquals("c", data.next()[0]);
    assertNull(data.next());
    data.dispose();
    assertEquals("a", data.next()[0]);
    assertEquals("b", data.next()[0]);
    assertEquals("c", data.next()[0]);
    assertNull(data.next());
}
Also used : JavaScriptResultSet(org.pentaho.platform.plugin.services.connections.javascript.JavaScriptResultSet) MemoryMetaData(org.pentaho.commons.connection.memory.MemoryMetaData) MemoryResultSet(org.pentaho.commons.connection.memory.MemoryResultSet)

Example 28 with MemoryResultSet

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

the class JavaScriptResultSetIT method testCopy.

public void testCopy() {
    MemoryMetaData metadata = new MemoryMetaData(new String[][] { { "col1", "col2" } }, null);
    MemoryResultSet data1 = new MemoryResultSet(metadata);
    data1.addRow(new Object[] { "a", new Integer(1) });
    data1.addRow(new Object[] { "b", new Integer(2) });
    data1.addRow(new Object[] { "c", new Integer(3) });
    MemoryResultSet data = (MemoryResultSet) data1.memoryCopy();
    assertNotNull(data.getMetaData());
    assertNotNull(data.getMetaData().getColumnHeaders());
    assertNull(data.getMetaData().getRowHeaders());
    assertEquals(2, data.getMetaData().getColumnCount());
    assertEquals(1, data.getMetaData().getColumnHeaders().length);
    assertEquals(2, data.getMetaData().getColumnHeaders()[0].length);
    assertEquals("col1", data.getMetaData().getColumnHeaders()[0][0]);
    assertEquals("col2", data.getMetaData().getColumnHeaders()[0][1]);
    assertEquals(3, data.getRowCount());
    assertEquals("a", data.getValueAt(0, 0));
    assertEquals(1, data.getValueAt(0, 1));
    assertEquals("b", data.getValueAt(1, 0));
    assertEquals(2, data.getValueAt(1, 1));
    assertEquals("c", data.getValueAt(2, 0));
    assertEquals(3, data.getValueAt(2, 1));
}
Also used : MemoryMetaData(org.pentaho.commons.connection.memory.MemoryMetaData) MemoryResultSet(org.pentaho.commons.connection.memory.MemoryResultSet)

Example 29 with MemoryResultSet

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

the class JavaScriptResultSetIT method testGetDataRow.

public void testGetDataRow() {
    MemoryMetaData metadata = new MemoryMetaData(new String[][] { { "col1", "col2" } }, null);
    MemoryResultSet dataSet = new MemoryResultSet(metadata);
    JavaScriptResultSet data = new JavaScriptResultSet();
    data.setResultSet(dataSet);
    data.addRow(new Object[] { "a", new Integer(1) });
    data.addRow(new Object[] { "b", new Integer(2) });
    data.addRow(new Object[] { "c", new Integer(3) });
    Object[] row = data.getDataRow(0);
    assertEquals(2, row.length);
    assertEquals("a", row[0]);
    assertEquals(1, row[1]);
    row = data.getDataRow(1);
    assertEquals("b", row[0]);
    assertEquals(2, row[1]);
    row = data.getDataRow(2);
    assertEquals("c", row[0]);
    assertEquals(3, row[1]);
    assertNull(data.getDataRow(99));
}
Also used : JavaScriptResultSet(org.pentaho.platform.plugin.services.connections.javascript.JavaScriptResultSet) MemoryMetaData(org.pentaho.commons.connection.memory.MemoryMetaData) MemoryResultSet(org.pentaho.commons.connection.memory.MemoryResultSet)

Example 30 with MemoryResultSet

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

the class JavaScriptResultSetIT method testGetDataColumn.

public void testGetDataColumn() {
    MemoryMetaData metadata = new MemoryMetaData(new String[][] { { "col1", "col2" } }, null);
    MemoryResultSet dataSet = new MemoryResultSet(metadata);
    JavaScriptResultSet data = new JavaScriptResultSet();
    data.setResultSet(dataSet);
    data.addRow(new Object[] { "a", new Integer(1) });
    data.addRow(new Object[] { "b", new Integer(2) });
    data.addRow(new Object[] { "c", new Integer(3) });
    Object[] col = data.getDataColumn(0);
    assertEquals(3, col.length);
    assertEquals("a", col[0]);
    assertEquals("b", col[1]);
    assertEquals("c", col[2]);
    col = data.getDataColumn(1);
    assertEquals(3, col.length);
    assertEquals(1, col[0]);
    assertEquals(2, col[1]);
    assertEquals(3, col[2]);
}
Also used : JavaScriptResultSet(org.pentaho.platform.plugin.services.connections.javascript.JavaScriptResultSet) MemoryMetaData(org.pentaho.commons.connection.memory.MemoryMetaData) MemoryResultSet(org.pentaho.commons.connection.memory.MemoryResultSet)

Aggregations

MemoryResultSet (org.pentaho.commons.connection.memory.MemoryResultSet)31 MemoryMetaData (org.pentaho.commons.connection.memory.MemoryMetaData)24 IPentahoMetaData (org.pentaho.commons.connection.IPentahoMetaData)7 JavaScriptResultSet (org.pentaho.platform.plugin.services.connections.javascript.JavaScriptResultSet)7 Date (java.util.Date)3 ArrayList (java.util.ArrayList)2 List (java.util.List)2 RowMetaInterface (org.pentaho.di.core.row.RowMetaInterface)2 StepMetaDataCombi (org.pentaho.di.trans.step.StepMetaDataCombi)2 SQLException (java.sql.SQLException)1 HashMap (java.util.HashMap)1 Iterator (java.util.Iterator)1 Map (java.util.Map)1 StringTokenizer (java.util.StringTokenizer)1 MimeHeaders (javax.xml.soap.MimeHeaders)1 Name (javax.xml.soap.Name)1 Node (javax.xml.soap.Node)1 SOAPBody (javax.xml.soap.SOAPBody)1 SOAPConnection (javax.xml.soap.SOAPConnection)1 SOAPElement (javax.xml.soap.SOAPElement)1