Changes for page DB-Abfragen

From version < 18.1 >
edited by nlo
on 30.04.2020, 13:24
To version < 19.1
edited by XIMA Admin
on 30.11.2020, 13:49
<
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nlo
1 +XWiki.ximaadmin
Content
... ... @@ -11,6 +11,18 @@
11 11  {{id name="data_db_query"/}}
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 +
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 +
14 14  * 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"]]).
15 15  * The following data is needed for a Database query:
16 16  ** **Name**: A unique name for the database query
... ... @@ -27,7 +27,7 @@
27 27  
28 28  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.
29 29  
30 -{{code}}
42 +{{code language="none"}}
31 31  http://<server>/formcycle/datenabfragedb
32 32  {{/code}}
33 33  
... ... @@ -34,23 +34,30 @@
34 34  The servlet URL is displayed beneath the settings (see [[figure>>||anchor="data_db_query"]]).
35 35  The following URL parameters are supported:
36 36  
49 +{{table dataTypeAlpha="0" preSort="0-asc"}}
37 37  |=Name of the paramter|=Description|=Required
38 38  |name|Must match the name of the database query.|Yes
39 39  |clientName|Must match the name of the client used for creating this data source.|Yes, if //projektId// is not given
40 40  |projektId|Must match the ID of the form. This information can be seen by accessing the {{code language="javascript"}}XFC_METADATA.currentProject.id{{/code}} object from JavaScript.|Yes, if //mandantName// is not given
41 -|sqlParameter|Alias for //queryParameter//. This is deprecated from version 6 and should not be used anymore. It may be removed in future releases.|
42 -|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
54 +|--sqlParameter--|Alias for //queryParameter//. Should no longer be used in {{formcycle/}} version 6 and will most likely be removed in the next major release of {{formcycle/}}.|No
55 +|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//.
56 +
57 +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
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
43 43  |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
44 -|delimiter|The delimiter for the placeholder values, see //queryParameter//. Defaults to a comma {{code}},{{/code}}|No
60 +|delimiter|The delimiter for the placeholder values, see //queryParameter//. Defaults to a comma {{code language="none"}},{{/code}}
61 +
62 +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
63 +{{/table}}
45 45  
46 -When accessing a database servlet from a form, always use the database URL contained in the global object //XFC_METADATA//, see also the [[metadata>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.FormMetadata]]. For example: {{code language="javascript"}}XFC_METADATA.urls.datasource_db{{/code}}.
65 +When accessing a database servlet from a form, always use the database URL contained in the global object //XFC_METADATA//, see also the [[metadata>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.FormMetadata]]. For example: {{code language="javascript"}}XFC_METADATA.urls.datasource_db{{/code}}.
47 47  
48 -Further we recommend you use the script function [[getDataQuery>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.AdditionalScriptFunctions.GetDataQuery]], so you do not have to setup the servlet request manually. The result of the database query is returned as //JSON//.
67 +Further we recommend you use the script function {{jsdoc page="xutil" name="getdataquery"/}}, so you do not have to setup the servlet request manually. The result of the database query is returned as //JSON//.
49 49  
50 50  == Testing the query ==
51 51  
52 52  {{info}}
53 -For quick testing of the query the shortcut {{code}}Ctrl + Enter{{/code}} is provided.
72 +For quick testing of the query the shortcut {{code language="none"}}Ctrl + Enter{{/code}} is provided.
54 54  {{/info}}
55 55  
56 56  Database queries can be tested directly from the configuration UI. For this purpose a test console is provided below the SQL editor (see [[figure>>||anchor="fig_data_db_query"]]).
... ... @@ -57,27 +57,26 @@
57 57  In the header of the console there is a row of buttons for controling the query:
58 58  
59 59  * {{ficon name="database-search"/}}**Perform query**
60 -Runs the database query. If //query parameters// ({{code}}?{{/code}}) are provided the user will be prompted to input values vor those parameters. Otherwise the result of the query will be displayed directly in the //table view//.
61 -{{lightbox image="data_db_query_test_en.png" title="Run the given query"/}}
79 +Runs the database query. If //query parameters// ({{code language="none"}}?{{/code}}) are provided the user will be prompted to input values vor those parameters. Otherwise the result of the query will be displayed directly in the //table view//.{{lightbox image="data_db_query_test_en.png" title="Run the given query"/}}
62 62  * {{icon name="question"/}}**Query parameters**
63 -Mask for inputting values for query parameters. This option is only available if query parameters ({{code}}?{{/code}}) are used in the SQL query. The individual parameters will be enumerated in the SQL query. Clicking "User parameters for query" {{ficon name="arrow-right-bold-circle-outline2"/}} executes the query with the given parameters. The result will be displayed in the //table view//.
64 -{{lightbox image="data_db_query_test_param_en.png" title="Query parameters are enumerated"/}}
65 -Actual Query in the SQL editor:
66 -{{lightbox image="data_db_query_example_de.png" title="Example query"/}}
81 +Mask for inputting values for query parameters. This option is only available if query parameters ({{code language="none"}}?{{/code}}) are used in the SQL query. The individual parameters will be enumerated in the SQL query. Clicking "User parameters for query" {{ficon name="arrow-right-bold-circle-outline2"/}} executes the query with the given parameters. The result will be displayed in the //table view//.
82 + {{lightbox image="data_db_query_test_param_en.png" title="Query parameters are enumerated"/}}
83 +Actual Query in the SQL editor:{{lightbox image="data_db_query_example_de.png" title="Example query"/}}
67 67  * {{ficon name="table-large"/}}**Table view**
68 -Query result in table view
69 -{{lightbox image="data_db_query_test_table_en.png" title="Query result in table view"/}}
85 +Query result in table view{{lightbox image="data_db_query_test_table_en.png" title="Query result in table view"/}}
70 70  * {{icon name="code"/}}**Source code view**
71 -Query result in JSON format
72 -{{lightbox image="data_db_query_test_code_en.png" title="Query result in JSON format"/}}
87 +Query result in JSON format{{lightbox image="data_db_query_test_code_en.png" title="Query result in JSON format"/}}
73 73  * {{icon name="Terminal"/}}**Generated SQL**
74 -Displays the generated SQL statement with input parameter values
75 -{{lightbox image="data_db_query_test_sql_en.png" title="Displays the generated SQL statement with input parameter values"/}}
89 +Displays the generated SQL statement with input parameter values{{lightbox image="data_db_query_test_sql_en.png" title="Displays the generated SQL statement with input parameter values"/}}
76 76  
77 77  == Selection form elements ==
78 78  
79 79  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.
80 80  
95 +{{info}}
96 +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.
97 +{{/info}}
98 +
81 81  {{figure image="data_db_query_designer_en.png"}}
82 82  Using the result of a database query as the data source a select element in the {{designer case="dat"/}}.
83 83  {{/figure}}
... ... @@ -100,6 +100,11 @@
100 100  
101 101  == Examples ==
102 102  
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 +
103 103  {{code language="sql"}}
104 104  select name, first_name from table where first_name like (?)
105 105  {{/code}}
... ... @@ -106,8 +106,12 @@
106 106  
107 107  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.
108 108  
109 -URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}}
132 +URL for running the query:
133 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}}
110 110  
135 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
136 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Robinson{{/code}}
137 +\\
111 111  
112 112  {{code language="sql"}}
113 113  select name, first_name from table where id = ?
... ... @@ -115,8 +115,12 @@
115 115  
116 116  Retrieves the name of a person with a certain ID. The ID is given as an URL parameter.
117 117  
118 -URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}}
145 +URL for running the query:
146 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}}
119 119  
148 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
149 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=100{{/code}}
150 +\\
120 120  
121 121  {{code language="sql"}}
122 122  select name, first_name from table where city like(?) AND zip = ?
... ... @@ -124,8 +124,12 @@
124 124  
125 125  Retrieves the names of all persons that live in certain city. The city and the ZIP code are given as URL parameters.
126 126  
127 -URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}}
158 +URL for running the query:
159 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}}
128 128  
161 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
162 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Paris&queryParameterValues=75001{{/code}}
163 +\\
129 129  
130 130  {{code language="sql"}}
131 131  select name, vorname from tabelle where ort like concat(?, '%')
... ... @@ -133,8 +133,12 @@
133 133  
134 134  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.
135 135  
136 -URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}}
171 +URL for running the query:
172 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}}
137 137  
174 +{{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query:
175 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Par{{/code}}
176 +\\
138 138  
139 139  {{code language="sql"}}
140 140  select name, vorname from tabelle where lower(ort) like concat('%', lower(?), '%')
... ... @@ -142,4 +142,8 @@
142 142  
143 143  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.
144 144  
145 -URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}}
184 +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:
188 +{{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=aRi{{/code}}

Navigation

Copyright 2000-2024