Database queries

Last modified by superadmin on 24.01.2017

Data base requests restrain similar to data sources (XML, CSV, JSON), but are not recorded statically but elected dynmaically from a data base.

For creating a request, an already compiled connection to a data base is necessary.

Creating an SQL request

creating an SQL request in Xima® Formcycle
  • Allocate an explicit name.
  • Choose a connection to the data base.
  • Enter the SQL request in the field request.

The entered statement is executed as Prepared-Statement, so that no so-called SQL incetion is possible. Therefore dom't use inverted commas. Basically transfer parameter can be used. In the request you substitute transfer parameter with an interrogation mark ?.

Using the request

The data base request is available inside and outside of a form via the designated servlet applying the corresponding parameters. The URL to the data base request reads as follows:

http://<server>/formcycle/datenabfragedb

The following URL parameters are possible here:

Name of the paramterDescriptionNecessary
nameMust match the name of the data base-data source.Yes
clientNameMust match the name of the client, under which this data source was created.Yes, if projektId is not given
projektIdMust match the ID of the form. This information can be called up via XFC_METADATA.currentProject.id.Yes, if mandantName is not given
sqlParameterOnly necessary, if the data base-data source placeholder is in shape of an interrogation mark. If this is the case, then this complies to the comma-seperated list of the used parameters.No
varNameIndicates the name of the JSON-response-object. If it is not set, than the JSON-object doesn't own an explicit name.No

At an access from a form, always use the global variables-object XFC_METADATA, see also global designer variables. In this object all relevant URLs of are available Xima® Formcycle, so that this can exemplary be ascertained as follows: XFC_METADATA.urls.datasource_db.

Further we recommand to use the script function getDataQuery of the form, which saves the manual setup of the servlet request.

The return of the request result takes place in the JSON format and can be used directly within the form.

Special case selection element

If the result of a data base request is to load in a selection element directly, it takes place via the properties of the selection elements in the Xima® Formcycle Designer.

The content of the SQL data base request is used in a selection element in the following order.

Visible value, assigned value, optional value 1, optional value 2, ...

All assigned values are added as value of the HTML attribute col0 (visible value), col1 (assigned value), col2 (optional value), col3 (optional value) and so on to the corresponding option element. 

The visible value is displayed in the selection list in the form. The assigned value is assigned as a value of the HTML attribute value when sending the form.

If additionally one or more optional values of the SQL data base request are returned, they can access them via Javascript via the following jQuery selector.

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

Examples

Statement: select name, vorname from tabelle where vorname like(?)

Abfrage per Servlet: http://meinserver/formcycle/datenquelledb?mandantName=ich&name=demo&sqlParameter=Micha

Statement: select name, vorname from tabelle where id=?

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

Statement: select name, vorname from tabelle where ort like(?) AND plz = ?

Abfrage per Servlet: http://meinserver/formcycle/datenquelledb?mandantName=ich&name=demo&sqlParameter=Dresden,01099

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