Summary

• Page properties (2 modified, 0 added, 0 removed)

Details

Page properties

Author

...	...	@@ -1,1 +1,1 @@
1		-XWiki.mko
	1	+XWiki.gru

Content

...	...	@@ -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&sqlParameter=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&sqlParameter=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&sqlParameter=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&sqlParameter=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&sqlParameter=aRi{{/code}}
	145	+URL for running the query: {{code language="none"}}http://myserver/formcycle/datenquelledb?mandantName=myself&name=demo&queryParameter=aRi{{/code}}