Database queries

In QuickHMI you have the possibility to display whole table contents from databases on the user interface.

To create a database query (SQL statement), you need a database connection, of course.


First, klick on “Database queries” in the Project explorer.


In the window that opens, click on the plus icon to create a new database query.




Then assign a "Name", select the "Connection" and choose the execution mode.  Then enter the desired SQL and save your entries.

There are 2 execution modes available for database queries. The modes differ on the one hand in when and by whom they are triggered

and on the other hand in which users get which data displayed.



Interval


The database query is automatically executed by the server at a specified interval (fastest interval 500ms).

All users always get the same current data automatically.



Manual


The database query is executed once when the server is started to obtain an initial data status.

After that, this query is only executed during a user interaction (by means of a created action, e.g. when a button is pressed).

The table is now filled with the current data of the database and is not updated until the action is executed again.

If other users perform an action that affects this database query during this time, this has no effect on other users.


In SQL, you can use placeholders that are replaced by the contents of variables at the time of query execution.

In this example we want to use the number of currently connected clients in an email.


We can find this value in the system variable "current_session_count".The data source name of the system data source is "System".




Placeholders for variables consist of curly brackets into which an "@" + data source name + variable name is written.

Data source name and variable name are separated by a dot. So, in this case {@System.current_session_count}.






Under "Controls" on the left side, the new element "DatabaseTable" appears, which can be dragged onto the surface like all the other elements.

In the “Properties” for the control under "DatabaseQuery", select the previously created database query.




When all entries have been made and the runtime has been executed, the table from the database is displayed.

The data's can be sorted by the columns as desired. (The representation can vary in the detail in the future)




Explanation of the element-specific properties



  • Info

When this option is enabled, informations about the table are displayed, including information about filtered data.

This option can be used to enable or disable this function.


  • Ordering

Enables or disables the ability to sort the data in the table by desired column.


  • Filtering

Enables or disables the search function within the table.


  • Paging

Controls whether the user can change the number of records displayed on a page at runtime.

If this option is disabled, all records are always displayed.


  • PageSize

A sequence of numbers (separated by commas) can be specified here to indicate which selection options the user has when paging is enabled.

In the example below, paging is enabled, and the page size is set to 50. So the user is not given the option to adjust the page size at runtime.


By entering "50, 100, -1", a selection could be made between 50, 100, and all entries.

With the exception of -1 (This represents all records on a page), numbers must be positive.