In the QueryDB User Guide we discussed the possibilities offered by this web service. This document is a tutorial where we illustrate the use of Query Builder in conjunction with QueryDB to create two types of queries: static and dynamic.
Generate queryxml for use with QueryDB
1. Open InfoPath, select Qdabra Query Builder and click on Fill out this form.
2. In the Data Sources table, enter the root URL to your installation of DBXL in the Web Service URL Prefix field.
3. The Web Service Name field is automatically populated to the default value, which is QdabraWebService. If you have chosen a different value, modify the content of the field.
4. For the InfoPath Form Template field, you only need to attach an InfoPath form if you are going to be using a field from the form to build the query. We will do this in a future example, but for now this can be left blank.
5. If you know it, enter the Database Connection String directly into the field. Alternatively, click on the Construct Now button to display a new section.
a. When using Windows Authentication, you only need to enter two fields:
i. Enter a Data Source (Server Name). This is the location of your SQL Server.
ii. Enter an Initial Catalog (Database Name). Note that this value will automatically be copied to the next table, Query Parameters.
iii. Click the Done button.
b. If you select SQL Server Authentication, a new section will open.
i. Enter a login and a password.
6. In the Query Parameters table:
a. Provide a Database Name if one was not provided in the connection string in step 5.
b. Enter the Table Name you wish to query.
c. Enter a value for the maxrows parameter in the Max Rows Count field. This will limit the number of rows that are returned by the connection.
d. Check the Distinct checkbox if desired.
7. In the Query Columns table, click on Insert return column.
a. Type in the name of the column. Alternatively, click on the Select Database Column icon. The database structure will open in the taskpane, where you can select the column desired.
b. Repeat as desired, adding the columns you wish to retrieve from the table. For this example, we will only return the ProjectName column.
8. In the Query Filter table, click on Insert comparison to create a new query filter.
a. Click on the Select Database Column icon and select a column from the data structure shown in the taskpane. For our example, we will select Client.
b. Select a Comparison Operator from the dropdown. For our example, we will select Equal.
c. In the Value or a column name field, enter Client1.
9. Click on Insert Logical Operator.
a. Select the logical operator OR from the dropdown.
b. Now repeat step 8, replacing Client1 with Client2.
10. In the Query Sort table, click on Insert sort column.
a. Click on the Select Database Column icon and select a column from the data structure shown in the taskpane. For our example, we will select Actual_length_months.
b. Select a Sort Order from the dropdown, in our case, we opt for Ascending.
11. Click on the Build Now button to generate the Query Strings. Copy the first two strings into Notepad.
Implement the static query
12. In InfoPath, click on File > Design a Form Template. Select a blank form and click OK.
13. Click on Tools > Data Connections, and in the dialog box, click on Add.
14. The Data Connection Wizard will now be displayed. Select Create a New Connection to
15. and Receive data. Click Next.
16. From the following screen, select Web Service and click Next.
17. Type in the URL for the QueryDB service, http://<server-name>/QdabraWebService/QueryDB.asmx and click Next.
18. In the following screen, select GetColumnsXMLStrQuery and click Next.
19. Double-click on tns:queryxml and paste the first query that was generated by Query Builder. This is the sample value, used by InfoPath to derive a schema. Click Next.
20. In the following screen, double-click on tns:queryxml and paste the second query that was generated by Query Builder. Click Next twice.
21. Ensure the checkbox for Automatically retrieve data when this form is opened is checked. Ensure the name for this data connection is GetColumnsXMLStrQuery. Click Finish. Click Close.
22. In the right hand pane called Design Tasks, click on Data Source and select GetColumnsXMLStrQuery from the dropdown.
23. Drag the node /dfs:myFields/dfs:dataFields/tns:GetColumnsXMLStrQueryResponse/tns:GetColumnsXMLStrQueryResult/Rows/row and drop it into the form as a Repeating table.
24. Click on File, then Save As, and save the form.
25. Click on Preview. If prompted by a Microsoft InfoPath Security warning, click Yes. This will display the results, listing the projects for Client1 OR Client2, sorted by the Actual_length_month column in ascending order.
Switch to a dynamic query
With a few additional steps we can implement a dynamic query that will call QueryDB after a user provides some missing piece of data. First we need to modify the form, then modify the query generated by Query Builder.
26. Delete the repeating table added to the form in step 14, above.
27. In the Design Tasks taskpane, click on Controls and then click on Text Box. A textbox will appear in the view.
28. Double click on the textbox and change the field name to client. Click OK to close the textbox properties window. Save the form.
29. Switch back to Query Builder and attach the form saved in step 17 to the InfoPath Form Template file attachment control.
30. Clear the Query Filter table by clicking on the down arrow and selecting Remove Logical Operator.
31. Click on Insert comparison and select the Client column for the Column Name. Leave the Comparison operator set to Equal.
32. For Value or Column Name, click on the Select Schema Node icon and select the client node.
33. Click on Build Now and copy the first two queries into Notepad.
34. Switch back to your form and select Tools > Data Connections. Click Modify for the GetColumnsXMLStrQuery.
35. Click Next twice and you will reach the Set Sample Value window for the Data Connection Wizard. Double-click on tns:queryxml and paste the first query that was generated by Query Builder. Click Next.
36. In the following screen, double-click on tns:queryxml and paste the same (first) query that was generated by Query Builder. Click Next twice.
37. Uncheck the checkbox for Automatically retrieve data when this form is opened and click Finish.
38. In the right hand pane called Design Tasks, click on Data Source and select GetColumnsXMLStrQuery from the dropdown.
39. Drag the node /dfs:myFields/dfs:dataFields/tns:GetColumnsXMLStrQueryResponse/tns:GetColumnsXMLStrQueryResult/Rows/row and drop it into the form as a Repeating table.
40. In the Design Tasks taskpane, click on Controls and then click on Button. A button will appear in the view.
41. Double click on the button and change the label to Query.
42. Click on Rules, then click on Add, and then click on Add Action.
43. Select Set a field’s value from the dropdown.
44. Click on the icon to the right of the Field textbox and select the queryxml node from the GetColumnsXMLStrQuery secondary data connection. Then click OK.
45. Click on the icon to the right of the Value textbox and paste the second query generated by Query Builder. Then click OK twice.
46. Click on Add Action.
47. Select Query using a data connection from the dropdown, making sure that the data connection to GetColumnsXMLStrQuery is selected from the Data connection dropdown, then click OK.
48. Click OK until all dialogs are closed. Save the form and click Preview.
49. Enter a client into the textbox and click the Query button. The results table will show the projects associated with that specific client.
Populate a dropdown
With a few extra steps we can use the data received from QueryDB populate a dropdown list:
50. Click on Close Preview to return to design mode for the form.
51. Delete the results repeating table.
52. In the right hand pane called Design Tasks, click on Controls and click on the Drop-Down List Box to get it in to the view.
53. Double click on the drop down to view the properties of the dropdown list.
54. In the properties dialog box, select Look up values from an external data source radio button.
55. Click the button next to the Field or Group text box. The Select a Field or Group dialog will appear.
56. Expand the data source, select the row repeating group and click OK twice.
57. Preview the form. Enter a client into the textbox and click the Query button. The dropdown will load the values pulled down via the QueryDB web service.
