Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Frevvoproduct
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:

...


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

  1. Create a new form. Accept the defaults by clicking the Finish button. The Form Designer will appear.
  2. Drag a Dropdown control from the Palette and drop it into the form.
  3. In the properties pane on the left, change the Label as desired.
  4. In the properties pane, change the Name to sc.

...