... |
... |
@@ -21,11
+21,13 @@ |
21 |
21 |
|
22 |
22 |
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. |
23 |
23 |
|
|
24 |
+{{version major="6" minor="4" patch="0"/}} [[Variables>>doc:Formcycle.UserInterface.Variables.WebHome]] can be used in the SQL statements. |
|
25 |
+ |
24 |
24 |
== Using the database query == |
25 |
25 |
|
26 |
26 |
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. |
27 |
27 |
|
28 |
|
-{{code}} |
|
30 |
+{{code language="none"}} |
29 |
29 |
http://<server>/formcycle/datenabfragedb |
30 |
30 |
{{/code}} |
31 |
31 |
|
... |
... |
@@ -39,9
+39,9 @@ |
39 |
39 |
|sqlParameter|Alias for //queryParameter//. This is deprecated from version 6 and should not be used anymore. It may be removed in future releases.| |
40 |
40 |
|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 |
41 |
41 |
|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 |
42 |
|
-|delimiter|The delimiter for the placeholder values, see //queryParameter//. Defaults to a comma {{code}},{{/code}}|No |
|
44 |
+|delimiter|The delimiter for the placeholder values, see //queryParameter//. Defaults to a comma {{code language="none"}},{{/code}}|No |
43 |
43 |
|
44 |
|
-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}}. |
|
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}}. |
45 |
45 |
|
46 |
46 |
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//. |
47 |
47 |
|
... |
... |
@@ -48,7
+48,7 @@ |
48 |
48 |
== Testing the query == |
49 |
49 |
|
50 |
50 |
{{info}} |
51 |
|
-For quick testing of the query the shortcut {{code}}Ctrl + Enter{{/code}} is provided. |
|
53 |
+For quick testing of the query the shortcut {{code language="none"}}Ctrl + Enter{{/code}} is provided. |
52 |
52 |
{{/info}} |
53 |
53 |
|
54 |
54 |
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"]]). |
... |
... |
@@ -55,10
+55,10 @@ |
55 |
55 |
In the header of the console there is a row of buttons for controling the query: |
56 |
56 |
|
57 |
57 |
* {{ficon name="database-search"/}}**Perform query** |
58 |
|
-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//. |
|
60 |
+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//. |
59 |
59 |
{{lightbox image="data_db_query_test_en.png" title="Run the given query"/}} |
60 |
60 |
* {{icon name="question"/}}**Query parameters** |
61 |
|
-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//. |
|
63 |
+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//. |
62 |
62 |
{{lightbox image="data_db_query_test_param_en.png" title="Query parameters are enumerated"/}} |
63 |
63 |
Actual Query in the SQL editor: |
64 |
64 |
{{lightbox image="data_db_query_example_de.png" title="Example query"/}} |
... |
... |
@@ -104,7
+104,7 @@ |
104 |
104 |
|
105 |
105 |
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. |
106 |
106 |
|
107 |
|
-URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}} |
|
109 |
+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Robinson{{/code}} |
108 |
108 |
|
109 |
109 |
|
110 |
110 |
{{code language="sql"}} |
... |
... |
@@ -113,7
+113,7 @@ |
113 |
113 |
|
114 |
114 |
Retrieves the name of a person with a certain ID. The ID is given as an URL parameter. |
115 |
115 |
|
116 |
|
-URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}} |
|
118 |
+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=100{{/code}} |
117 |
117 |
|
118 |
118 |
|
119 |
119 |
{{code language="sql"}} |
... |
... |
@@ -122,7
+122,7 @@ |
122 |
122 |
|
123 |
123 |
Retrieves the names of all persons that live in certain city. The city and the ZIP code are given as URL parameters. |
124 |
124 |
|
125 |
|
-URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}} |
|
127 |
+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Paris,75001{{/code}} |
126 |
126 |
|
127 |
127 |
|
128 |
128 |
{{code language="sql"}} |
... |
... |
@@ -131,7
+131,7 @@ |
131 |
131 |
|
132 |
132 |
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. |
133 |
133 |
|
134 |
|
-URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}} |
|
136 |
+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=Par{{/code}} |
135 |
135 |
|
136 |
136 |
|
137 |
137 |
{{code language="sql"}} |
... |
... |
@@ -140,4
+140,4 @@ |
140 |
140 |
|
141 |
141 |
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. |
142 |
142 |
|
143 |
|
-URL for running the query: {{code}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}} |
|
145 |
+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}} |