Using Smarty template engine to extend SQL script achieves the function that user form variables can be dynamically inserted in SQL scripts and the scripts can be dynamically rewritten according to the input. Before all the scripts submitted to the database, all the variables will be replaced with the value of the current form.

Form variable value ​​can be applied to the script.

For example, a tabular report query application script:

select OrderData, Status, ColumnName from Orders where Status = {$status}

Considering a form that has a form field named "status", if user input "Shipped", the final query should be

select OrderData, Status, ColumnName from Orders where Status = `Shipped`

The final statement may vary depending on the the choice of different data sources.

Tip

Do not worry about SQL injection here, all the item value will be escaped.

Tip

Script can also be used in drag & drop mode.Regarding about the filter condition value, you can also use the variable like {$status} .

For example:

use form variables in filter condition

About advanced usage of the script, please refer to Smarty template.

Form script#

Form variables used in the script#

Depending on the control, form controls may output two types of data: Single value and a value list.

  • Single value

Single Line, Multi Line, Drop down, and Radio Group submit only one value.

If it's single value, please insert it into the scripts in the form {$Field Name} . Considering a Single Line named Status, you can insert it into the filter condition or the scripts in the form {$status}. Such as:

select orderDate, status where status = '{$status}'

Please note: when this variable is included in the script, you need to add quotes. When parsing script, Dbface will not automatically determine the type of the field.

  • Multiple value

Checkbox and Multi Select will submit multiple value.

Multi-value control used in the filter condition is the same with Single value control, meaning that: a match to meet the conditons.

Multi-value control used in script mode is different from Single value control, such as the search condition.

... Where status = '{$status}'

Dbface will not retrieve the data, because {$status} is an array so that it will be forced to be converted to a string 'Array'. The correct usage should be:

... Where status in ({$status|join})

{$status | join} Syntax will automatically expand and split this value.

Data Source script#

Considering some form controls having limited value, such as DropDown, Multi Select, Checkbox, RadioGroup, you can use data source scripts to specify their limited value. When executing, DbFace will dynamically execute data source script to get the value of form controls.

Data source script can output one or two fields, the remaining fields will be ignored.

If there are two fields: the contents of the first field as submitted value ​​(data is applied in the script), the second field as the displayed value ​​(data is only for display).

As

select value from table where ...

Or

select key, value from table where ...

To use the data source scripts, click the "Edit" button in Forms controls having limited value. Click the "Script" button in the edit box of pop-up window field, the input textbox "Data source script" will be displayed, where you can enter the data source script and save it.

screenshot

Do not forget to use the data source script generator, click on the generator icon behind the "script" button, all tables of current links and their fields will pop up. Check the required fields to generate queries.

Form field onchange script#

After editing the form field, you can set a query script. When the input is completed and the focus is lost, the script will automatically execute and fetch data form the final database. The data obtained will be mapped by name to the current form control one by one.

To change the script, click on the "Edit" button, the field property edit box pops up, click on the "Onchange Scripts" button, enter the script in the expanded textbox, then save it.

You can use the current form variable to change form field script.

screenshot

Form loading script#

Users can specify a loading script (query) for user form to initialize the form data value.

After the application opened, DbFace will detect whether the current form has the loading script, if so, Dbface will automatically execute this loading script and map the query result to the current form one by one. If the query returns multiple data, Dbface will provide the "Browse" button, then you can select the required data for the form control value.

To create a form loading script, click the Form Editor "Properties" button, then the property editbox pops up, where you can enter the loading script and save it.

screenshot