Hitachi Vantara Pentaho Community Wiki
Child pages
  • Updates for BISERVER-2735 (Row Limit, Timeout, Read Only)

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin


The components above now accept up to three new "known" optional inputs.


Data Type: Integer
Limit: Must be greater or equal to zero

All of the above components look for, and respect the new input max_rows. For SQLBaseComponent/SQLLookupRule, MQLRelationalDataComponent, and HQLBaseComponent/HQLLookupRule, when max_rows comes in as an input through the normal , the value is ultimately passed into the JDBC statement object. For XQueryBaseComponent/XQueryLookupRule, the data limiting is performing during document parse. If the underlying JDBC driver does not support this setting, the action sequence will be terminated.

Care should be used when limiting the number of rows returned by a query because the user will never know that the results are being truncated.


Data Type: Integer
Limit: Must be greater than zero
Scale: seconds

Of the above components, only the JDBC-based components (SQLBaseComponent/SQLLookupRule, MQLRelationalComponent, HQLBaseComponent/HQLLookupRule) provide support for this setting. The query timeout is passed into the underlying JDBC statement object. Queries that take longer than the specified time (in seconds) will fail and an exception will be thrown to the server.


Data Type: Boolean
Limit: Must be true or false

Of the above components, only the components (SQLBaseComponent/SQLLookupRule, MQLRelationalComponent) provide support for this setting. The read-only value is passed into the underlying JDBC connection object.

Creating global defaults for these settings

If you want to set a global default for max rows, query timeout, or read-only, you can modify the bean definition for the SQL Connection bean in the file pentaho-solutions/system/pentahoObjects.spring.xml and add the default properties there. Below is an example that shows setting the global defaults to 10,000 rows, 90 seconds for query timeout, and true for read-only.

Code Block

<bean id="connection-SQL" class="" scope="prototype">
  <property name="maxRows" value="10000" />
  <property name="queryTimeout" value="90" /> <!-- In Seconds -->
  <property name="readOnly" value="true" />

With the defaults above applied, every time a SQLConnection object is created by the Pentaho Object Factory, those properties will be injected into the connection by default. The defaults of course will be overridden by settings in the metadata model, and also at the individual component level in an action sequence.