Changes for page DB-Abfragen
edited by XIMA Admin
on 30.11.2020, 13:49
on 30.11.2020, 13:49
Change comment:
There is no comment for this version
Summary
-
Page properties (2 modified, 0 added, 0 removed)
Details
- Page properties
-
- Author
-
... ... @@ -1,1 +1,1 @@ 1 -XWiki. gru1 +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 ... ... @@ -64,27 +64,26 @@ 64 64 In the header of the console there is a row of buttons for controling the query: 65 65 66 66 * {{ficon name="database-search"/}}**Perform query** 67 -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//. 68 -{{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"/}} 69 69 * {{icon name="question"/}}**Query parameters** 70 70 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//. 71 -{{lightbox image="data_db_query_test_param_en.png" title="Query parameters are enumerated"/}} 72 -Actual Query in the SQL editor: 73 -{{lightbox image="data_db_query_example_de.png" title="Example query"/}} 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"/}} 74 74 * {{ficon name="table-large"/}}**Table view** 75 -Query result in table view 76 -{{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"/}} 77 77 * {{icon name="code"/}}**Source code view** 78 -Query result in JSON format 79 -{{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"/}} 80 80 * {{icon name="Terminal"/}}**Generated SQL** 81 -Displays the generated SQL statement with input parameter values 82 -{{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"/}} 83 83 84 84 == Selection form elements == 85 85 86 86 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. 87 87 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 + 88 88 {{figure image="data_db_query_designer_en.png"}} 89 89 Using the result of a database query as the data source a select element in the {{designer case="dat"/}}. 90 90 {{/figure}} ... ... @@ -108,7 +108,8 @@ 108 108 == Examples == 109 109 110 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.{{/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}} 112 112 113 113 114 114 {{code language="sql"}} ... ... @@ -122,8 +122,7 @@ 122 122 123 123 {{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query: 124 124 {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Robinson{{/code}} 125 - 126 - 137 +\\ 127 127 128 128 {{code language="sql"}} 129 129 select name, first_name from table where id = ? ... ... @@ -136,8 +136,7 @@ 136 136 137 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 - 140 - 150 +\\ 141 141 142 142 {{code language="sql"}} 143 143 select name, first_name from table where city like(?) AND zip = ? ... ... @@ -150,8 +150,7 @@ 150 150 151 151 {{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query: 152 152 {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Paris&queryParameterValues=75001{{/code}} 153 - 154 - 163 +\\ 155 155 156 156 {{code language="sql"}} 157 157 select name, vorname from tabelle where ort like concat(?, '%') ... ... @@ -164,8 +164,7 @@ 164 164 165 165 {{version major="6" minor="6" patch="3"/}}URL that can be used starting with {{formcycle/}} version 6.6.3 for running the query: 166 166 {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameterValues=Par{{/code}} 167 - 168 - 176 +\\ 169 169 170 170 {{code language="sql"}} 171 171 select name, vorname from tabelle where lower(ort) like concat('%', lower(?), '%')