DbFace script is powered by Smarty template engine.

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

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

<<<<<<< HEAD

Tips#

Use tagged SQL queries in Application script Editor#

DbFace provides snippet tag to refer SQL query snippet created in SQL workshop.

{snippet name="your-sql-query-tag-name"}
=======
## Script Plugins
DbFace provides many script plugins that help you to build complex application source.

### config
You can use config function to assign variables to current application script.
```smarty
{config key1=value1 key2=value2}

Now, you can use {$key1}, {$key2} in the application script.

For loading multiple fixed variables, you can also create ini file

{config file="your-ini-file-which-located-in-dbface-system-folder"}

DbFace will try to load the ini file and assigned all variables in the ini file. To create ini file, please open Settings menu and click Cloud Code link, then click "Start Development Workspace", then click "New File" in system folder.

create_variable#

As you already known, you can create Global Variables in Parameters section. All available variables will be assigned in every appliction script.

You can also create local variable that only available in current application script.

{create_variables name="local-variable-name" value="value" sql=""  func="" url=""}

value, sql, func and url should be (and only one) assigned.

  • value: assigned fixed value to {$local-variable-name}

  • sql: DbFace wil try to use the sql to query application database, and assigne the result set to {$local-variable-name}

  • func: DbFace will execute the function and assign the function result to {$local-variable-name}

  • url: DbFace will try to call the URL and assign the http response body to {$local-variable-name}

snippet#

snippet plugin helps you refer tagged application script into current application script.

{snippet name='your-tagged-sql-query'}
>>>>>>> d803b613d38bde6d3e2fe61118e994c6621c740e