Changes for page DB-Abfragen

From version 4.1
edited by jdr
on 19.07.2021, 14:03
Change comment: There is no comment for this version
To version 7.3
edited by gru
on 18.09.2023, 17:10
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.jdr
1 +XWiki.gru
Content
... ... @@ -1,6 +1,6 @@
1 1  {{content/}}
2 2  
3 -Database queries are similar to [[data sources>>doc:Sources]] (XML, CSV, JSON). The important difference is that they are not static, but read dynamically from a database.
3 +Database queries are similar to [[data sources>>doc:Sources]] (XML, CSV, JSON). The important difference is that they are not static, but read dynamically from a database. In principle, only queries or SELECT statements should be stored at this point. Other statements (INSERT, etc.) may work - but this varies from database type to database type and is not recommended. Instead, such statements should be made with the corresponding workflow action [[Database query>>doc:Formcycle.Designer.Workflow.Actions.SQLStatement]].
4 4  
5 5  {{info}}
6 6  Before you can create a new database query, you must create a [[database connection >>doc:DBConnections]] if you have not done so already.
... ... @@ -44,10 +44,11 @@
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|{{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
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
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 -
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 +
51 51  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
52 52  {{/table}}
53 53  
... ... @@ -107,11 +107,6 @@
107 107  
108 108  == Examples ==
109 109  
110 -{{info}}
111 -{{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.
112 -{{/info}}
113 -
114 -
115 115  {{code language="sql"}}
116 116  select name, first_name from table where first_name like (?)
117 117  {{/code}}
... ... @@ -119,9 +119,6 @@
119 119  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.
120 120  
121 121  URL for running the query:
122 -{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}}
123 -
124 -{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
125 125  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Robinson{{/code}}
126 126  \\
127 127  
... ... @@ -132,9 +132,6 @@
132 132  Retrieves the name of a person with a certain ID. The ID is given as an URL parameter.
133 133  
134 134  URL for running the query:
135 -{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}}
136 -
137 -{{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=100{{/code}}
139 139  \\
140 140  
... ... @@ -145,9 +145,6 @@
145 145  Retrieves the names of all persons that live in certain city. The city and the ZIP code are given as URL parameters.
146 146  
147 147  URL for running the query:
148 -{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}}
149 -
150 -{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
151 151  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Paris&queryParameterValues=75001{{/code}}
152 152  \\
153 153  
... ... @@ -158,9 +158,6 @@
158 158  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.
159 159  
160 160  URL for running the query:
161 -{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}}
162 -
163 -{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
164 164  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Par{{/code}}
165 165  \\
166 166  
... ... @@ -171,7 +171,4 @@
171 171  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.
172 172  
173 173  URL for running the query:
174 -{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}}
175 -
176 -{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
177 177  {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=aRi{{/code}}