Data Source: Prepared Statements and Bind Variables

Data sources and parameterized data sources can execute using bind variables. This improves performance by allowing the server to cache the previously-parsed version of the query.

Sample view: ab-ex-report-grid-bind-variables.axvw

Use bind variables for parsed restriction values in classic data sources

Console views and reports typically use parsed restrictions to query data based on values entered by users in filter forms or selected by users in grids or trees. Data sources convert each clause of the parsed restriction into an SQL fragment and add it to the WHERE clause of the generated SQL query.

Because users enter or select different values, data sources typically generate different SQL queries each time. But while the values are always different, the clauses often follow the same pattern, For example:

SELECT ... FROM ... WHERE problem_type = <dynamicValue> AND requestor = <dynamicValue>

Archibus provides an option to globally use prepared statements and bind variables in these data sources. To enable this option, set the following in \WEB-INF\config\core.properties :

core.usePreparedStatement=true

When the option is enabled, data sources automatically apply bind variables for:

  • Parsed restriction values applied from the client using AXVW commands showPanel , selectTab , openDialog , or using JS API panel.refresh(Ab.view.Restriction) .

  • Parameter values applied from the client using JS API panel.addParameter( ) and dataSource.addParameter() when parameters are used inside parsed restrictions defined in AXVW.

AXVW

<dataSource id="blDs"> <table name="bl"/> <field name="bl_id"/> <field name="name"/> <parameter name="building" dataType="text" value=""/> <restriction type="parsed"> <clause op="=" value="${parameters['building']}" name="bl_id" table="bl"/> </restriction> </dataSource>

JS

afterInitialDataFetch: function(){ this.blGrid.addParameter("building", "HQ"); this.blGrid.refresh(); }

Limitations

Bind variables are not used in SQL restrictions passed from the view or defined in AXVW.

Use bind variables in parameterized SQL data sources

Archibus allows add-in manager to use prepared statements and bind variables in parameterized SQL data sources. This option requires each data source to be modified, and cannot be globally enabled.

To modify a parameterized SQL data source to use bind variables:

Add usePreparedStatement=”true ” to the <dataSource> tag in the view.

Replace ${parameters['name']} with ${sql.getBindVariable('name')} in the SQL query.

Keep <parameter> tags as is.

Example

<dataSource id="reportGridSql_ds" usePreparedStatement="true"> <sql dialect="generic"> SELECT bl_id, fl_id, count(rm_id) ${sql.as} rooms, sum(area) ${sql.as} total_area FROM rm WHERE bl_id = ${sql.getBindVariable('building')} GROUP BY bl_id, fl_id </sql> <table name="rm" role="main"/> <field table="rm" name="rm_id"/> <field table="rm" name="fl_id"/> <field table="rm" name="bl_id"/> <field table="rm" name="area"/> <parameter name="building" dataType="text" value="HQ"/> </dataSource>

This option cannot be enabled or disabled using the global option core.usePreparedStatement 3535 in core.propertie s.

Limitations

  • The sql.getBindVariable expression cannot be used in calculated SQL fields (classic data sources can have <field> tags with nested <sql> expressions).

  • The sql.getBindVariable expression must be used only for values. It cannot be used as a replacement for table or field names, or for restriction clauses, because bind variables cannot change the structure of the SQL query. The following examples are invalid:

<parameter name="whereClause" dataType="verbatim" value="1=1"/> <sql dialect="generic">SELECT rm.rm_id FROM rm WHERE ${sql.getBindVariable('whereClause')}</sql>

<parameter name="fieldName" dataType="verbatim" value="1=1"/> <sql dialect="generic">SELECT rm.rm_id, ${sql.getBindVariable('fieldName')} FROm rm</sql>