Changes for page DB-Abfragen

From version 7.1
edited by Release Notes
on 19.01.2023, 17:03
Change comment: There is no comment for this version
To version 2.1
edited by gru
on 29.06.2021, 14:19
Change comment: Renamed back-links.

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.ReleaseNotes
1 +XWiki.gru
Content
... ... @@ -12,6 +12,17 @@
12 12  {{figure image="data_db_query_de.png"}}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.{{/figure}}
13 13  
14 14  
15 +{{html wiki="true"}}
16 +<div class='xm-figure xm-float-right xm-clear-h2' data-alt='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.'><div class='xm-figure-inner' style='width:310px'>{{lightbox image='data_db_query_de.png' width='300' group='$height' group='$group' title='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.'/}}<div class='xm-figure-caption'>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.</div></div></div>
17 +{{/html}}
18 +
19 +
20 +
21 +{{html wiki="true"}}
22 +<div class='xm-figure xm-float-right xm-clear-h2' data-alt='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.'><div class='xm-figure-inner' style='width:310px'>{{lightbox image='data_db_query_de.png' width='300' group='$height' group='$group' title='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.'/}}<div class='xm-figure-caption'>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.</div></div></div>
23 +{{/html}}
24 +
25 +
15 15  * Open the module "Database queries" and click "New" {{ficon name="plus-circle-outline"/}} in the header of the list (see [[figure>>||anchor="fig_data_db_query"]]).
16 16  * The following data is needed for a Database query:
17 17  ** **Name**: A unique name for the database query
... ... @@ -22,7 +22,7 @@
22 22  
23 23  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.
24 24  
25 -[[Variables>>doc:Formcycle.UserInterface.Variables.WebHome]] can be used in the SQL statements.
36 +{{version major="6" minor="4" patch="0"/}} [[Variables>>doc:Formcycle.UserInterface.Variables.WebHome]] can be used in the SQL statements.
26 26  
27 27  == Using the database query ==
28 28  
... ... @@ -44,11 +44,10 @@
44 44  |queryParameter|When the query contains placeholders (question marks, {{code language="none"}}?{{/code}}), 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//.
45 45  
46 46  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 {{formcycle/}}.|No
47 -|queryParameterValues|Starting with {{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 {{code language="none"}}?{{/code}} 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
58 +|queryParameterValues|{{version major="6" minor="6" patch="3"/}}Starting with {{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 {{code language="none"}}?{{/code}} 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
48 48  |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
49 49  |delimiter|The delimiter for the placeholder values, see //queryParameter//. Defaults to a comma {{code language="none"}},{{/code}}
50 -|jsonPath {{version major="7" minor="3"/}}|This parameter allows specifying a JSON path expression which will be applied to the returned result of the query on the server side. The example //$.returnValue[0]// returns the values of the first result of the query.|No
51 -
61 +
52 52  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 {{formcycle/}}.|No
53 53  {{/table}}
54 54  
... ... @@ -80,7 +80,7 @@
80 80  
81 81  == Selection form elements ==
82 82  
83 -If you want to display the returned data as options of a [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]], you can do so easily by opening the {{designer/}} and selecting the database query as the data source of the selection element.
93 +If you want to display the returned data as options of a [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]], you can do so easily by opening the {{designer/}} and selecting the database query as the data source of the selection element.
84 84  
85 85  {{info}}
86 86  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.
... ... @@ -90,7 +90,7 @@
90 90  Using the result of a database query as the data source a select element in the {{designer case="dat"/}}.
91 91  {{/figure}}
92 92  
93 -The result of the database query is used to create the options of the [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]] in the following order:
103 +The result of the database query is used to create the options of the [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]] in the following order:
94 94  
95 95  {{box}}
96 96  Displayed value, submitted value, optional value 1, optional value 2, ...
... ... @@ -108,6 +108,11 @@
108 108  
109 109  == Examples ==
110 110  
121 +{{info}}
122 +{{version major="6" minor="6" patch="3"/}}Starting with {{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 {{formcycle/}}. The following examples therefore show one servlet query with //queryParameter// and one with //queryParameterValues// for each SQL query.
123 +{{/info}}
124 +
125 +
111 111  {{code language="sql"}}
112 112  select name, first_name from table where first_name like (?)
113 113  {{/code}}
... ... @@ -115,6 +115,9 @@
115 115  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.
116 116  
117 117  URL for running the query:
133 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}}
134 +
135 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
118 118  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Robinson{{/code}}
119 119  \\
120 120  
... ... @@ -125,6 +125,9 @@
125 125  Retrieves the name of a person with a certain ID. The ID is given as an URL parameter.
126 126  
127 127  URL for running the query:
146 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}}
147 +
148 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
128 128  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=100{{/code}}
129 129  \\
130 130  
... ... @@ -135,6 +135,9 @@
135 135  Retrieves the names of all persons that live in certain city. The city and the ZIP code are given as URL parameters.
136 136  
137 137  URL for running the query:
159 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}}
160 +
161 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
138 138  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Paris&queryParameterValues=75001{{/code}}
139 139  \\
140 140  
... ... @@ -145,6 +145,9 @@
145 145  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.
146 146  
147 147  URL for running the query:
172 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}}
173 +
174 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
148 148  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Par{{/code}}
149 149  \\
150 150  
... ... @@ -155,4 +155,7 @@
155 155  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.
156 156  
157 157  URL for running the query:
185 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}}
186 +
187 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
158 158  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=aRi{{/code}}