Archibus Parameterized Queries - Front End
Archibus Parameterized Queries - Front End
The usePreparedStatement attribute on data sources now has little effect. All data sources now use bind variables for parameters, but verbatim SQL in parameters or restrictions are still supported as of v2024.02. Verbatim SQL will go through existing scans, but support for them should be discontinued in a future release. At that time, verbatim parameters may be limited to database identifiers that must fit a strict format.
Replace verbatim SQL with SQL defined in AXVW or Java
-
SQL restriction on requests for data from a data source / JSON restriction with sql attribute
-
Use an Optional SQL Restriction on a data source in the AXVW.
-
-
Parameters to custom workflow rules
-
Each rule should validate inputs against a finite list (e.g. table names, field names, comparators) or treat them as bind variables, constructing SQL using the com.archibus.db.Sql class. No rule should accept SQL from the client.
-
-
Verbatim Parameters (except tables and fields)
-
Use non-verbatim parameters if possible. Use an Optional SQL Restriction on a data source in the AXVW for each possible SQL string, using non-verbatim parameters as needed.
-
-
Table and Field names as parameters
-
In workflow rules, validate these using Sql.valueOfTable(String) or Sql.valueOfField(String).
-
In data sources, use an Optional SQL Restriction in the AXVW.
-
The support for use of field names in verbatim parameters may continue, so long as they are valid DBMS identifiers and accessible to the user.
-
-
Select Value commands with restrictions
-
SQL restrictions for selectValue commands will be maintained on the server and will not be passed to the client. The browser may still evaluate some expressions that are parsed on the client and will be treated as bind variable values on the server.
-
SQL should not be provided as a restriction to a selectValue command from JavaScript. Either specify clauses, or for an alternative approach see below: SelectValue commands with restrictions.
-
-
Clause values with OGNL that have SQL expressions as inputs
-
A single OGNL expression that evaluates to a literal is permitted. A literal is permitted. Any SQL outside of an OGNL expression is not permitted.
-
If a parameter is used as a clause value, it should no longer be verbatim, but should be the type of the bind variable.
-
The server will now try to convert literal clause values specified in JSON to the the appropriate type for the field.
-
Upgrade Caveats
-
OGNL expressions such as ${user.name} should no longer be in quotes as they will evaluate to a bind variable. If you see '?' in a query, this is likely the issue. Check VPA restrictions and AXVWs.
-
Take care when converting verbatim parameters to text parameters that the parameter value is not surrounded by single quotes. This may result in a warning being logged.
-
Parameters used in clause values should not be verbatim, as they were before, but of the type required for the clause.
-
A type=”grouping” data source with a calculated field that takes a parameter within a function, or a custom query of this pattern won't work with bind variables:
SELECT somefunction(?) FROM table GROUP BY somefunction(?)
The DBMS doesn’t correlate the two bind variables, so this is a syntax error. It can be fixed by restructuring the query:
SELECT field FROM (SELECT somefunction(?) field FROM table) table GROUP BY field
Custom queries need to be modified directly, but data sources of type “grouping” can change to “groupingSubquery” in v2024.02 where this is an issue. This isn't a problem for sorting or other patterns, only grouping.
Optional SQL Restrictions
The general approach is to, in nearly all cases is to replace these with named restrictions on a data source enabled by a boolean parameter.
<dataSource id="ds_ab-sp-hl-comn-gp_tree_bl" usePreparedStatement="true">
<table name="bl" role="main"/>
<field table="bl" name="bl_id"/>
<parameter name="blIdParam" dataType="text" value=""/>
<parameter name="isFilterBlId" dataType="boolean" value="false"/>
<restriction enabled="isFilterBlId" type="sql" sql="bl.bl_id = ${sql.getBindVariable('blIdParam')}"/>
</dataSource>
See Optional SQL Restrictions help page for more details.
Verbatim Parameters
Except perhaps for field names, verbatim parameters will no longer be supported in a future release and will be replaced with Optional SQL Restrictions on the dataSource.
DataSource with Verbatim Parameter:
in axvw,
<dataSource id="ds_ab-sp-hl-comn-gp_tree_bl">
<table name="bl" role="main"/>
<field table="bl" name="bl_id"/>
<parameter name="blId" dataType="verbatim" value="IS NOT NULL"/>
<restriction type="sql" sql="bl.bl_id ${parameters['blId']} AND EXISTS (SELECT 1 FROM gp WHERE gp.prorate != 'NONE' AND gp.bl_id = bl.bl_id AND gp.dwgname IS NOT NULL and gp.portfolio_scenario_id is null)"/>
<sortField table="bl" name="bl_id" ascending="true"/>
</dataSource>
in Js,
this.abSpHlComnGp_BlTree.addParameter('blId', " = " + "'" + filterBlId + "'");
Alternative, DataSource without Verbatim:
in axvw,
<dataSource id="ds_ab-sp-hl-comn-gp_tree_bl" usePreparedStatement="true">
<table name="bl" role="main"/>
<field table="bl" name="bl_id"/>
<parameter name="blIdParam" dataType="text" value=""/>
<parameter name="isFilterBlId" dataType="boolean" value="false"/>
<restriction type="sql" sql="bl.bl_id IS NOT NULL AND EXISTS (SELECT 1 FROM gp WHERE gp.prorate != 'NONE' AND gp.bl_id = bl.bl_id AND gp.dwgname IS NOT NULL and gp.portfolio_scenario_id is null)"/>
<restriction enabled="isFilterBlId" type="sql" sql="bl.bl_id = ${sql.getBindVariable('blIdParam')}"/>
<sortField table="bl" name="bl_id" ascending="true"/>
</dataSource>
in Js,
this.abSpHlComnGp_BlTree.addParameter("blIdParam", filterBlId);
this.abSpHlComnGp_BlTree.addParameter("isFilterBlId", true);
Refreshes the panel data with restriction:
Do not use literal Sql restriction and avoid literal sql from client side. Alternatively, you can use Ab.view.Restriction object. Or, you can use an optional SQL restriction (with bind variables).
Example:
panel.refresh(" wr.wr_id = " + wrId");
Alternatively,
var restriction = new Ab.view.Restriction();
restriction.addClause("wr.wr_id", wrId);
panel.refresh(restriction);
See refresh_restrictionGrid help page for more details to refreshes the panel data.
SelectValue commands with restrictions:
see selectValue
Do not assign literal Sql restrictions in JavaScript to selectValue commands. Instead, define a dataSource with corresponding restrictions and specify its ID in the dataSource property and its parameters using dataSourceParameters to View.selectValue() function.
Example:
in axvw,
<dataSource id="projRestrictionConsoleDs2" usePreparedStatement="true" distinct="true">
<table name="project" role="main"/>
<table name="projecttype" role="standard"/>
<field table="project" name="project_id"/>…..
...
<restriction type="sql" sql="1=1 AND ${sql.getRestriction('projectIdRes')}"/>
<restriction id="projectIdRes" type="sql" sql="${sql.getRestriction('projectTypeRes','1=1')} /> <parameter name=" projectTypeExists" dataType="boolean" value="false"/>
<parameter name="projectTypeVal" dataType="text" value="%"/>
<restriction id="projectTypeRes" enabled="projectTypeExists" type="sql" sql="project.project_type LIKE ${sql.getBindVariable('projectTypeVal')}"/>
</dataSource>
in Js,
let ds = View.dataSources.get('projRestrictionConsoleDs2');
….
ds.addParameter('projectTypeExists', true);
ds.addParameter('projectTypeVal', projectTypeVal);
....
View.selectValue ('consolePanel', title,['project.project_id'],'project',['project.project_id'],['project.project_id','project.project_name','projecttype.project_type','project.status','project.requestor','project.summary'],
null, null, null, null, null, 800, 500, null, null, null, false, false, false, false, ds, ds.parameters );
Alternatively, you can specify the literal restriction using a JSON object.
Example:
<command type="selectValue" selectValueType="grid"
fieldNames="bl.site_id,rm.bl_id"
selectFieldNames="bl.site_id,bl.bl_id"
visibleFieldNames="bl.site_id,bl.name,bl.use1"
restriction="{clauses:[{name:'bl.bl_id', op:'=', value:'HQ'}]}">
<title>Building Code</title>
</command>