DB queries

Content [ Hide ]

Creating a database query
Using the database query
Testing the query
Selection form elements
Examples

Database queries are similar to data sources (XML, CSV, JSON). The important difference is that they are not static, but read dynamically from a database.

Before you can create a new database query, you must create a database connection if you have not done so already.

Creating a database query

Configuration screen for creating a database query in: (1) list of existing DB queries, (2) editor for defining the SQL query, (3) settings of the DB query, (4) console for testing the DB query.

Open the module "Database queries" and click "New" in the header of the list (see figure).
The following data is needed for a Database query:
- Name: A unique name for the database query
- Description: An optional description for the database query
- Connection: The Database connection that should be used

Enter the SQL statement to be executed for the query in the editor (see figure).

The entered SQL statement is executed as a prepared statement, which prevents SQL injection attacks. You should not and need not use inverted commas (` or '). Also, you can use question marks ( ? ) as placeholders to build queries dynamically.

6.4.0+ Variables can be used in the SQL statements.

Using the database query

You can access the database query by making a HTTP request to the corresponding database servlet and providing the required parameters as GET parameters. The URL to the database query servlet is as follows.

http://<server>/formcycle/datenabfragedb

The servlet URL is displayed beneath the settings (see figure).
The following URL parameters are supported:

Name of the paramter	Description	Required
name	Must match the name of the database query.	Yes
clientName	Must match the name of the client used for creating this data source.	Yes, if projektId is not given
projektId	Must match the ID of the form. This information can be seen by accessing the XFC_METADATA.currentProject.id object from JavaScript.	Yes, if mandantName is not given
~~sqlParameter~~	Alias for queryParameter. Should no longer be used in Xima® Formcycle version 6 and will most likely be removed in the next major release of Xima® Formcycle.	No
queryParameter	When the query contains placeholders (question marks, ?), a list of parameters must be supplied for each placeholder. The number of items must match the number of parameters used in the SQL query. The items are separated with the delimiter as defined by the URL parameter delimiter. If possible the parameter queryParameterValues should be used for new projects instead of queryParameter because queryParameter will not be supported in a future version of Xima® Formcycle.	No
queryParameterValues	6.6.3+ Starting with Xima® Formcycle Version 6.6.3 this parameter can be used as an alternative to the parameters queryParameter and delimiter. Like these parameters, queryParameterValues is only required if placeholders in the form of a question mark ? are used within the SQL query. If this is the case, the individual query parameters are passed one after the other as a separate parameter queryParameterValues, which also eliminates the use of the parameter delimiter.	No
varName	Allows you to change name of the JSON response object. If not given, a plain JSON object with the result data is returned.	No
delimiter	The delimiter for the placeholder values, see queryParameter. Defaults to a comma , If possible the parameter queryParameterValues should be used for new projects instead of delimiter and queryParameter because delimiter will not be supported in a future version of Xima® Formcycle.	No

When accessing a database servlet from a form, always use the database URL contained in the global object XFC_METADATA, see also the metadata. For example: XFC_METADATA.urls.datasource_db.

Further we recommend you use the script function xutil.getdataquery, so you do not have to setup the servlet request manually. The result of the database query is returned as JSON.

Testing the query

For quick testing of the query the shortcut Ctrl + Enter is provided.

Database queries can be tested directly from the configuration UI. For this purpose a test console is provided below the SQL editor (see figure).
In the header of the console there is a row of buttons for controling the query:

Perform query
Runs the database query. If query parameters (?) are provided the user will be prompted to input values vor those parameters. Otherwise the result of the query will be displayed directly in the table view.
Query parameters
Mask for inputting values for query parameters. This option is only available if query parameters (?) are used in the SQL query. The individual parameters will be enumerated in the SQL query. Clicking "User parameters for query" executes the query with the given parameters. The result will be displayed in the table view.

Actual Query in the SQL editor:
Table view
Query result in table view
Source code view
Query result in JSON format
Generated SQL
Displays the generated SQL statement with input parameter values

Selection form elements

If you want to display the returned data as options of a selection element, you can do so easily by opening the Xima® Formcycle Designer and selecting the database query as the data source of the selection element.

Queries that are used in selection elements must not have a question mark "?". Queries with a "?" are not offered as a data source for selection elements.

Using the result of a database query as the data source a select element in the Xima® Formcycle Designer.

The result of the database query is used to create the options of the selection element in the following order:

Displayed value, submitted value, optional value 1, optional value 2, ...

All returned columns are added as the value of the HTML attribute col0 (displayed value), col1 (submitted value), col2 (optional value 1), col3 (optional value 2) etc. to the corresponding option element.

The displayed value is visible to the user directly when selecting an option. The submitted value is the value of the HTML attribute value and it is sent when the form is submitted.

Optional values returned by the database query may be access as follows with JavaScript.

$('[name=sel2]').find('option:selected').attr('col2') // Selects the active option of the selection element named 'sel2' and returns the first optional value.

Examples

6.6.3+ Starting with Xima® Formcycle Version 6.6.3, the parameter queryParameterValues can be used instead of the parameter queryParameter. The former is recommended for new projects because the parameter queryParameter will not be supported in a future version of Xima® Formcycle. The following examples therefore show one servlet query with queryParameter and one with queryParameterValues for each SQL query.

select name, first_name from table where first_name like (?)

This SQL statement returns the names of all persons with a certain first name. The first name to search for is specified via an URL parameter.

URL for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson

6.6.3+ URL that can be used starting with Xima® Formcycle version 6.6.3 for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Robinson

select name, first_name from table where id = ?

Retrieves the name of a person with a certain ID. The ID is given as an URL parameter.

URL for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100

6.6.3+ URL that can be used starting with Xima® Formcycle version 6.6.3 for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=100

select name, first_name from table where city like(?) AND zip = ?

Retrieves the names of all persons that live in certain city. The city and the ZIP code are given as URL parameters.

URL for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001

6.6.3+ URL that can be used starting with Xima® Formcycle version 6.6.3 for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Paris&queryParameterValues=75001

select name, vorname from tabelle where ort like concat(?, '%')

This SQL statement returns the names of all persons who live in a place that starts with the given characters. The '%' character serves as a wildcard for any number of characters. Depending on the DBMS used, the syntax may differ slightly (here: MySQL). The requested value is passed via URL parameters.

URL for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par

6.6.3+ URL that can be used starting with Xima® Formcycle version 6.6.3 for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Par

select name, vorname from tabelle where lower(ort) like concat('%', lower(?), '%')

This SQL statement returns the names of all persons who live in a place that contains the given characters. Upper/lower case spelling is irrelevant becauce the statement converts both, the value column and the actual filter value to lower case (lower(...)). The requested value is passed via URL parameters.

URL for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi

6.6.3+ URL that can be used starting with Xima® Formcycle version 6.6.3 for running the query:
http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=aRi

Tags: