Prepared Components - Enabling Subreporting and Connection Sharing

Unknown macro: {scrollbar}

Most of the data source components now support a new feature called "prepared_component". This functionality allows data source components to be executed by other components and also share their connections.

Data Source Components that implement the prepared component feature include:

SQLLookupRule
MDXLookupRule
HQLLookupRule
XQueryLookupRule

Also note that SQLExecute can use a prepared_component as an action input for sharing connections.

Enabling JFreeReport Subreporting

By defining a component output with the name "prepared_component", a datasource component goes into a prepared state vs. the standard execution state. The component initializes its connection and sets up its query, but waits for another component to execute the prepared statement. An example of this is a subreport in JFreeReport. JFreeReport will execute the prepared statement for each item in its primary result set. Note that currently only the JFreeReport Component uses the prepared_component for later execution. Here is an example action sequence that uses the prepared_component functionality in a subreport.

<action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>SQL Query For Report Data</action-type>
      <action-inputs>
         <max_rows type="string"/>
      </action-inputs>
      <action-outputs>
        <prepared_component type="prepared-component" mapping="main_query"/>
      </action-outputs>
      <component-definition>
        <jndi>SampleData</jndi>
        <query><![CDATA[select DISTINCT POSITIONTITLE from QUADRANT_ACTUALS order by POSITIONTITLE]]></query>
         <max_rows>3</max_rows>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>SQL Query For Report Data</action-type>
      <action-inputs>
         <prepared_component type="prepared-component" mapping="main_query"/>
      </action-inputs>
      <action-outputs>
        <prepared_component type="prepared-component" mapping="subreport_query"/>
      </action-outputs>
      <component-definition>
        <jndi>SampleData</jndi>
        <query><![CDATA[select DISTINCT DEPARTMENT from QUADRANT_ACTUALS WHERE POSITIONTITLE = {PREPARELATER:POSITIONTITLE} order by DEPARTMENT]]></query>
        <dept>Finance</dept>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>JFreeReportComponent</component-name>
      <action-type>Create Report Using Query Results</action-type>
      <action-inputs>
        <default mapping="main_query"/>
        <subreport_query mapping="subreport_query"/>
        <output-type type="string"/>
      </action-inputs>
      <action-resources>
        <report-definition type="resource"/>
      </action-resources>
      <component-definition/>
    </action-definition>

See the pentaho-solutions/test/reporting/jfreereport-subreport-ipreparedcomponent-test.xaction for the complete example above.

New syntax introduced includes the "PREPARELATER" template item in the sub query above. The PREPARELATER field "POSITIONTITLE" is resolved when the query is executed within the JFreeReportComponent. In this example, The subquery is executed for each row in the main query. This relationship is defined within the JFreeReport xml file. Also note that the second SQLLookupRule takes in the first SQLLookupRule as an action input. This is another benefit of using Prepared Component, components can now share connections.

Within the JFreeReportComponent itself, the only changes that need to be made to support subreports are to include the additional lookup rules as action inputs as seen above. The action input name needs to match the subreport query name provided in the JFreeReport XML.

Connection Sharing

An additional benefit of the prepared_component feature is the ability to share connections across components. For instance, if two SQLLookupRules need to share a connection, they are able to do so using the prepared_component functionality. In this example, notice that the first SQLLookupRule makes a connection available by using the action output prepared_component, and then the two following SQL components use the prepared_component as input for sharing a connection:

<!-- first create a connection -->

    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Get Connection</action-type>
      <action-inputs/>
      <action-outputs>
        <prepared_component mapping="connObj"/>
      </action-outputs>
      <component-definition>
        <jndi>SampleDataAdmin</jndi>
      </component-definition>
    </action-definition>

      <!-- second, create temporary table w/ connection and add data -->

      <action-definition>
        <action-inputs>
          <prepared_component mapping="connObj"/>
        </action-inputs>
        <action-outputs/>
        <component-name>SQLExecute</component-name>
        <action-type>Create a temp table</action-type>
        <component-definition>
          <continue_on_exception>true</continue_on_exception>
          <query><![CDATA[
            drop table tmptbl;
            create temp table tmptbl(val int) ON COMMIT PRESERVE ROWS;
            insert into tmptbl values (1)
          ]]></query>
        </component-definition>
      </action-definition>

      <!-- third, extract inserted data from temporary table -->

    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Check for Data</action-type>
      <action-inputs>
         <prepared_component mapping="connObj"/>
      </action-inputs>
      <action-outputs>
        <query-result mapping="a_result"/>
      </action-outputs>
      <component-definition>
        <query>SELECT * FROM tmptbl</query>
      </component-definition>
    </action-definition>

See the pentaho-solutions/test/ipreparedcomponents/ipreparedcomponent_sql_temptable.xaction for the complete example above