Database queries

Last modified by Sabrina Schulze on 08.10.2018

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

Creating a database query in Xima® Formcycle
  • Enter a unique name.
  • Select a database connection
  • Enter the SQL statement to be executed for the query

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.

http://<server>/formcycle/datenabfragedb

The following URL parameters are supported.

Name of the paramterDescriptionRequired
nameMust match the name of the database query.Yes
clientNameMust match the name of the client used for creating this data source.Yes, if projektId is not given
projektIdMust 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
sqlParameterAlias for queryParameter. This is deprecated from version 6 and should not be used anymore. It may be removed in future releases.
queryParameterYes, 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
varNameAllows you to change name of the JSON response object. If not given, a plain JSON object with the result data is returned.No
delimiterThe 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 global designer variables. 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.

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.

Setting up a selection element with data from a database query. Just select the database query as the data source.

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

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

Abfrage per Servlet: http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&sqlParameter=Robinson

select name, first_name from table where id = ?

Abfrage per Servlet: http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&sqlParameter=100

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

Abfrage per Servlet: http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&sqlParameter=Paris,75001

Tags:
Created by superadmin on 22.06.2015
Translated into en by superadmin on 08.07.2016
  
Copyright 2000-2018