...
Cloud customers should verify that the Database Connector is accessible from the frevvo servers. Cloud customers should replace <localhost:8082> with <app.frevvo.com> in the Business Rule and Document Action URL examples below.
Define a Queryset
The Typically, the first step in connecting your form/workflow to your database is to define a queryset and give it a name - BIRT in the configuration.xml file. Since we are using the built-in configuration.xml file, you do not have to do this. Here is an example of the BIRT queryset and the allCustomers query that is described below.
...
. The database connector can simultaneously connect to multiple databases; each must be defined in its own queryset. The queryset consists of a resource definition and any number of named queries. The resource definition is configured in the dbconnector.properties file. You will need to modify it as required: change localhost to the name of the machine running MySQL, specify the MySQL driver, change the database user name and password.
Next you would define a simple query named 'allCustomers' in the configuration.xml file. Queries can contain four SQL statements (create, retrieve, update, delete). In this example, our query contains just one SQL statement to get the list of customers. Here is an example of the BIRT queryset and the allCustomers query that is described above. Learn more about this process in the Defining SQL Queries documentation.
| Code Block |
|---|
<dbconnector> <queryset name="BIRT"> <statement><query name="allCustomers" autocreate="true"> <retrieve> SELECT "customerNumber","customerName" from "Customers" order by "customerName" <statement> </statement> </retrieve> </query> </queryset> </dbconnector> |
The database connector can simultaneously connect to multiple databases; each must be defined in its own queryset. The queryset consists of a resource definition and any number of named queries. The resource definition is shown above for MySQL. It is configured in the dbconnector.properties file. You will need to modify it as required: change localhost to the name of the machine running MySQL, specify the MySQL driver, change the database user name and password.
...
SELECT "customerNumber","customerName" from "Customers" order by "customerName"
</statement>
</retrieve>
</query>
</queryset>
</dbconnector> |
Mapping HTTP requests to SQL queries
The database connector maps between HTTP requests issued from and SQL queries. It is important to understand how HTTP URLs map to SQL queries in the database connector. The above query returns a resultset resultSet that contains data as shown below:
...
| Note |
|---|
It is very helpful to test your queries as shown above by entering the query URL directly into your web browser and verify that the data returned to your browser as a web page is as you expect. Note however that browsers often cache web pages. If you edit your configuration.xml SQL query and reload/refresh the URL in your browser you may NOT get the updated results due to your browser's caching. Avoid this caching issue by always opening a new browser tab to retest an updated query. |
Generating xml schema
If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your project one time and then you can add the relevant schema elements to your forms/workflows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.
| Code Block |
|---|
http://<server>:<port>/database/BIRT/schema |
...
query and reload/refresh the URL in your browser you may NOT get the updated results due to your browser's caching. Avoid this caching issue by always opening a new browser tab to retest an updated query. |
Generating xml schema
If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your project one time and then you can add the relevant schema elements to your forms/workflows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.
| Code Block |
|---|
http://<server>:<port>/database/BIRT/schema |
Example 1: View customer list
| Tip |
|---|
The example below shows you how to add dynamic dropdown options using a business rule to connect to your database and define the options. However, the Dynamic Options - No Coding Needed feature allows you to do this without a business rule, but simply by selecting Web Service from the Options Src property of your dropdown control and defining your query url and options there. Please see the documentation or watch this 5 minute video to use this recommended method. |
Our first example is to create a form with a dropdown control that is dynamically populated with the above list of customers from the database. When the user selects a customer by name, the value of the dropdown control should be set to the customer number.
...
Create a form
- Create a new form. Accept the defaults by clicking the Finish button. The Form Designer will appear.
- Drag a Dropdown control from the Palette and drop it into the form.
- In the properties pane on the left, change the Label as desired.
- In the properties pane, change the Name to sc.
...