DB queries
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.
Creating a database 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.
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.
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. This is deprecated from version 6 and should not be used anymore. It may be removed in future releases. | |
| queryParameter | Yes, if placeholders ( ? ) are used in the query. Must be a comma separated list of parameters and match the number of parameters used in the SQL query (from version 6). | 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 , | 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 getDataQuery, so you do not have to setup the servlet request manually. The result of the database query is returned as JSON.
Testen der Abfrage
Datenbank-Abfragen können direkt in der Konfigurationsoberfläche getestet werden. Hierzu steht eine Testkonsole unterhalb des SQL-Editor zur Verfügung (siehe Abbildung).
Im Header der Testkonsole befindet sich eine Reihe von Buttons zur Steuerung der Abfrage:
- Abfrage ausführen
Führt die Datenbank-Abfrage aus. Werden Abfrageparameter (?) verwendet, wird der Nutzer aufgefordert Werte für diese Parameter einzugeben. Ist dies nicht der Fall wird das Ergebnis der Abfrage direkt in der Tabellenansicht dargestellt.
- Abfrageparameter
Maske zum Eingeben von Werten für Abfrageparameter. Diese Option steht nur zur Verfügung, wenn Abfrageparameter (?) in der SQL-Abfrage verwendet werden. Die einzelnen Abfrageparameter werden nummeriert im SQL-Query dargestellt. Ein Klick auf "Parameter für Abfrage verwenden" führt die Abfrage mit den eingebenen Parametern aus. Das Ergebnis wird in der Tabellenansicht dargestellt.
Eigentliche Abfrage im SQL-Editor:
- Tabellenansicht
Abfrageergebnis in Tabellenform
- Quellcodeansicht
Abfrageergebnis im JSON-Format
- Generiertes SQL
Zeigt das generierte SQL-Statement mit eingefügten Parameterwerten
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.
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.
Examples
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&sqlParameter=Robinson
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&sqlParameter=100
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&sqlParameter=Paris,75001