Using Query Parameters

If the DataSource defined in the AXVW uses a custom SQL restriction or a custom SQL query with parameters, you can set these parameters from your JavaScript code. The parameters will be used in all consequent calls to getRecord() or getRecords() methods.

In the AXVW you need to define <parameter> elements for all parameters used inside the SQL text.

Parameter declarations must specify the parameter data type using the dataType attribute. The supported parameter data types are:

  • text (or string ): the parameter value is a text string which will be converted into a string literal, i.e. all single quotes inside the text will be escaped, and the text itself will be put inside single quotes.
  • number : the parameter value is a numeric value which will be parsed to verify that it is a valid number.
  • date : the parameter value is a date which will be parsed to verify that it is a valid date and converted into database-dependent date string representation.
  • time : the parameter value is a time which will be parsed to verify that it is a valid date and converted into database-dependent time string representation.
  • literal : the parameter value is a text string which will be inserted into SQL as is.

Parameter declarations can include the default parameter value using the value attribute. The default parameter value will be used if the client JavaScript code does not send a value for this parameter.

Parameter with text or verbatim data type can use ${} binding expressions in the value. In this example, the user_name parameter uses an expression that is evaluated to the current user name.

<dataSource id="processDataSource">
<table name="afm_processes"/>
<field name="process_id"/>
<parameter name="user_name" dataType="text" value="${user.name}"/>
<restriction type="sql"
sql="NOT EXISTS (SELECT activity_id, process_id FROM afm_userprocs
WHERE user_name = ${parameters[\'user_name\']}
AND activity_id = afm_processes.activity_id
AND process_id = afm_processes.process_id)"/>
</dataSource>


JavaScript:

var userName = someRecord.getValue('afm_users.user_name');
this.processDataSource.addParameter('user_name', userName);
var processes = this.processDataSource.getRecords();

Nesting the $ expression

You cannot nest one $ expression inside another.

For example, if the custom SQL query in an AXVW contains the ${parameters} expression:

<sql dialect="generic">
SELECT ls_id,bl_id,pr_id,ac_id,cost_tran_recur_id,cost_cat_id,description,amount_expense,amount_income,yearly_factor,date_start,date_end,period,period_custom,date_seasonal_start,date_seasonal_end,date_trans_created,status_active
FROM cost_tran_recur WHERE 1=1 ${parameters['restrict']}
</sql>

and the parameter value applied from the client contains another expression:

cost_tran_recur.date_start >=${sql.date('2009-01-01')}

then, the second expression will not be evaluated because the expressions are nested.

The recommended solution is to remove the restriction from the custom SQL query, and apply it as a JS restriction:

<sql dialect="generic">
SELECT ls_id,bl_id,pr_id,ac_id,cost_tran_recur_id,cost_cat_id,description,amount_expense,amount_income,yearly_factor,date_start,date_end,period,period_custom,date_seasonal_start,date_seasonal_end,date_trans_created,status_active
FROM cost_tran_recur
</sql>
panel.refresh(‘cost_tran_recur.date_start >=${sql.date('2009-01-01')}’);